Use parameters and custom functions to call APIs in Power BI – MSDynamicsWorld.com

December 22 2020
If you are familiar with general concepts around calling APIs using a programming language, you probably know how to handle the challenges of paging and adding dynamic inputs in the endpoints. While Power BI allows for connecting to data sources via API, it offers no direct solutions for these common needs. Instead, we can make use of the parameters and custom function feature to overcome the problem of paging and we can dynamically change the text (e.g. id) in the API’s endpoint.
I have already written a detailed blog post on how to get data into Power BI using an API which you can find here. In this article, I’m going to explain how you can dynamically call the API based on its location.
What are Parameters in Power BI?
In Power BI Desktop, you can add Query Parameters to a table/query and make elements of the table dependent on those parameters. You can have one parameter, or multiple parameters for any table. Let’s take a look at how to create parameters in Power BI Desktop.
Steps to create parameters:
Step 1: Open Power BI Desktop, from Home ribbon click on “Transform data”.
Step 2: In Power Query Editor, from Home ribbon click on Manage Parameters->New Parameter
Step 3: Fill in the details in the Manage Parameters Dialog box. (Give a name and then add a dummy value for now). Click on Ok.

In Power BI, custom functions can be used to tackle the repetitive tasks. Instead of writing the same code again and again, we can create a function for the code & execute it in multiple queries which will save our time & efforts.
In our case, we are going to use Custom Functions to dynamically change the location id in an API’s endpoint, so that we don’t have to manually call the API’s for each location.
Let’s take a look at how to create custom functions.
Steps to create Custom functions with Parameters:
In this example we are bringing an Event api from a popular platform which requires a Location ID present in its URL to access the data.
Step 1: Create a query by calling an api for a single location. (Enter the id manually) E.g. https://play.dhis2.org/2.34.0/api/events?paging=false&orgUnit=Il7prf3KXCf. Name the query “GetEvents”

Step 2: Navigate the API, extract the lists, records and finally columns. By the end of which the M code will look something like this:

Step 3: As you can see, we are entering the id after “orgunit=” in the query itself. So, the next step is to enter the same code via Parameters.
For this, change the text after “orgunit=” to point towards the parameter that we had created. The URL will now become: “https://play.dhis2.org/2.34.0/api/events?paging=false&orgUnit=”&LocationID
Meanwhile, also change the “LocationID” parameter’s value to the “id” i.e. Il7prf3KXCf.

The output will remain the same, but now instead of giving the values from the URL we are giving it from the new Parameter that we had created.
Step 4: Now that we have a table “GetEvents”. We are going to make a function out of it. To do so, right click on “GetEvents” and select “Create Function”. A “Create Function” dialog box will pop up. Give the function a name and click on OK.

Step 5: You will see that, Power BI will automatically group the parameter & function in a single Folder. Inside the folder is the function that has just been created called “EventFunction”.
Tip: Disable the load for “GetEvents”. Transformations done in the “GetEvents” query will reflect in the output of the function.

 Dynamically bring Events for multiple Locations using Custom Functions:
I have already brought a table “Location” in Power BI from an endpoint which gives me the list of all locations in my account.
I’m going to use this table as the base table to call the events. Let’s take a look at how we can do that.
Step 1: Create a duplicate of “Location” query & rename it to “Events”.

Step 2: Click on the header of the column which has the id. In this case I have clicked on “id”. Then, go to “Add column” ribbon & click on “Invoke Custom Function”.

Step 3: A “Invoke Custom Function” dialog box will appear. From the Function query dropdown, select our function “EventFunction”. In LocationID dropdown, confirm that column “id” is selected. Click on Ok.

Step 4: Remove the errors from the newly created column and then expand the table to bring in the records and columns accordingly.

In this article, we have learnt how to use parameters and custom functions to call API’s dynamically.
You may also have noticed that this platform’s API had an option to tackle paging in one simple step. But in most cases, we have to handle paging by ourselves. Parameters and custom functions can come handy in such situations.

Karan Nair is working as an expert consultant and leads the Data Analytics department in Addend Analytics. Having a core interest and passion in applying Data for solving business problems, he is a voracious reader in the Data Analytics domain. Currently, Karan is working on multiple North American Power BI implementation Projects.  

Addend Analytics is a Microsoft Certified Partner which is a young and rapidly growing Data Analytics consultancy. It specializes in the application of Business Intelligence and Data Science tools to solve business challenges some of which includes extracting data from disparate data sources like Databases, SaaS applications, ERP, QuickBooks, IoT devices, spreadsheets, creating on-premises or Cloud Data Warehouses using ETL process, Analyzing Data using Microsoft Power BI, Creating Insightful Reports and embedding Power BI reports into Apps. Addend Analytics has been credited with the successful implementation of Power BI projects for 100+ clients across multiple sectors like Finance, Professional Services, Retail, Sales, Real estate, Inventory, and countries like the US, Europe, Australia, and India. You can explore their services at www.addendanalytics.com 


Karan Nair is working as an expert consultant and leads the Data Analytics department in Addend Analytics. Having a core interest and passion in applying Data for solving business problems, he is a voracious reader in the Data Analytics domain. Currently, Karan is working on multiple North American Power BI implementation Projects.  
Addend Analytics is a Microsoft Certified Partner which is a young and rapidly growing Data Analytics consultancy. It specializes in the application of Business Intelligence and Data Science tools to solve business challenges some of which includes extracting data from disparate data sources like Databases, SaaS applications, ERP, QuickBooks, IoT devices, spreadsheets, creating on-premises or Cloud Data Warehouses using ETL process, Analyzing Data using Microsoft Power BI, Creating Insightful Reports and embedding Power BI reports into Apps. Addend Analytics has been credited with the successful implementation of Power BI projects for 100+ clients across multiple sectors like Finance, Professional Services, Retail, Sales, Real estate, Inventory, and countries like the US, Europe, Australia, and India. You can explore their services at www.addendanalytics.com 
More about Karan Nair

source