Scenario
In this example, we have an Excel table that contains the region names and we want to to get the current year-to-date sales data from our sales application. We won't be making a request to our actual sales application but we'll be using a service named pipedream. Pipedream is a wonderful service for connecting API's, databases, and all sorts of things. This post will use a simple service I've created that generates random values for the purpose of this demo but if you ever need to connect applications, Pipedream is a great choice. You can read more about it at https://pipedream.com.
Initial data
To start, our simple Excel file contains one worksheet named "Lists" which contains one table named "Regions":
Make the regions available in Power Query
To add the regions list to Power Query, in Excel, we can simply select one of the cells in the Regions table, choose the Data tab on the menubar, then choose From Table/Range on the Data toolbar:
The Power Query window will open and the Regions table will be addd:
Create a custom Power Query function
Don't let the idea of creating a custom function scare you. This is a very simple function and it makes sense in our case because we ultimately want to be able to provide our sales API the region name and get the region's YTD sales back.
Our simple Power Query function will use the built-in Web.Contents function to make our POST request - click here to read more about Web.Contents on Microsoft's PowerQuery M language reference.
To create a custom function in Power Query, on the Home tab on the menubar, New Source -> Other Sources -> Blank Query:
A new blank query will be added. To make it a little easier to add our function steps, let's open up the Advanced Editor from the Home menubar:
In the advanced editor, add this and make any changes that are needed for your particular application:
(region as text) =>
let
SalesUrl = "<ENTER YOUR URL HERE>",
ApiResponse = Web.Contents(SalesUrl, [
Content = Json.FromValue([SalesRegion = region])
]),
// Because my API return a JSON response, I am converting this to a record
Response = Json.Document(ApiResponse)
in
Response
After creating our custom function, we should test it. By selecting the function from the Query list, the user will have an option to invoke the funtion:
Enter "North East" (or a valid value for your API) and ensure you get the expected response before moving on. In my example, I am expecting a JSON response with "YTD Sales Amount" as the key and the amount as the value. (Please refer to the API you are using to understand the type of response you will receive.):
*Note: If during the invocation of your custom function, you receive a "Please specify how to connect" prompt, choose Edit Credentials and select the appropriate option.
Use our custom function to get our sales for each region
Now that we've created our custom function and tested it, we're ready to use it in our query.
In Power Query, select the Regions query we created earlier by importing data from our Excel range. We want to add our sales amount so, choose the Add Column tab on the menubar and then choose Invoke Custom Function:
In the Invoke Custom Function dialog, set New column name to "Sales Amount" and, in the Function query dropdown, select the custom function you created. Once you select the custom function from the dropdown, Power Query will recognize that the custom function expects a "region" parameter. From the region dropdown, make sure Column name is selected then select "Region" from the column name dropdown and click OK:
A request for each row will be made to the API and a response returned:
If your API simply sends back the sales amount in the form of a number, you will see that number in the cell. In my case, my API sends back a JSON response that I converted to a record in the custom function so I'll need to indicate exact what I want to see in the cell. I can click the button in the column header, select the record value I want in the cell, optionally choose to include/exclude the table name from the column name, and click OK:
After clicking OK, my sales amount is shown in the cell:
By choosing Close & Load on the Power Query Home menubar, a new worksheet will be added that includes the region and sales data. Congratulations!
Wrap up
By creating a custom function in Power Query, you can do all sorts of things including, as we saw in this blog post, making a call to an external API. Let us know if there is anything Judith Creek Consulting can help you with!