Use Power Query to import Azure ML score data into Excel

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

You can use Power Query to import Azure ML score data into Excel. On an Azure ML API reference page, you can paste the auto-generate code into a blank query.

Power Query Requirement

Power Query December 2014 Update or later.

• Download Power Query for Excel

To import Azure ML score data into Excel

1. In the Excel ribbon, choose POWER QUERY > From Other Sources > Blank Query.

   

2. In the Query Editor, choose Advanced Editor.

3. In the Advanced Editor form, enter a formula that connects to an Azure ML OData feed and invokes a score function, and click Done.

   Example Power Query formula

   The example is based on Sample 1: Download dataset from UCI: Adult 2 class dataset part of the Azure ML sample collection.

   let      MultiplyBy4 = (number as number) =>  let      Source = OData.Feed("https://ussouthcentral.services.azureml.net/odata/workspaces/f602a9a0e6f240baa1ebd18d921b6a3f/services/5d805ba51fe3420091b33b060de1964a"),      ScoreSource = Source{[Name="Score"]}[Data],      #"Invoke FunctionScoreSource" = ScoreSource(number),      #"Convert to Table" = Record.ToTable(#"Invoke FunctionScoreSource")  in      #"Convert to Table"  in MultiplyBy4  

4. In Query Editor, click Invoke.

5. In the Enter Parameters form, enter parameters for the Azure ML score.

6. Click Edit Credentials.

7. In the Access an OData feed authentication form, choose Basic, and paste an Azure ML token into Password, and click Save.

8. In Query Editor, click Close and Load (or continue to shape the data).

To get an Azure ML authorization key

You get an Azure ML web service API key from an Azure ML web service. You can get it from Microsoft Azure Machine Learning studio or the Azure Management Portal.

Microsoft Azure Machine Learning studio

1. In Microsoft Azure Machine Learning studio, click WEB SERVICES on the left.

2. Click a web service. The "API key" is on the DASHBOARD tab.

Azure Management Portal

1. Click MACHINE LEARNING on the left.

2. Click a workspace.

3. Click WEB SERVICES.

4. Click a web service.

5. Click an endpoint. The "API KEY" is down at the lower-right.