Call Rest APIs from Power BI / Power Query

Overview:

Sometimes, we have a requirement where we need to call some sort of Rest APIs from Power BI Desktop. During this video, I will teach you step by step procedure for calling Rest APIs from Power BI.

Note

Real-Life Example:

• In this example, I’m going to pass the Username and Password to my API.
• It will generate Bearer Token to consume in my API
• Using Bearer Token, we need to call another API

 So, Let’s get started!

Step 1:

Open your Power Query Window.

Go to Get Data > select Blank Query.

Add the following line of code

() =>
let
    body = [username="Dhruvin.shah@powerplatformtrainings.com",password="999999"],
   Data=Json.Document(Web.Contents("https://api.symestic.com/api/login", [Headers=[#"Content-Type"="application/json"], Content=Json.FromValue(body)])),
    result = Record.Field(Data[result]{0},"token")
in

    result

Here,

1 = Your API URL

2= Username to generate Token

3 = Password

If your API do not support any body Parameters, you can skip this step.

This will add the one PowerQuery function for you!

Click on Invoke. This will show you Bearer Token generated by API.

Step 2:

1. Now, we want to call one of the APIs which use the above Bearer Token.
2. The API URL is = https://api.symestic.com/api/downtimeReasons/user/734
3. Open Power BI Desktop and then Click on Get Data > select Web
4. Select the Advanced option.
   

Add  API URL in #1

Add Authorization header

Authorization = Bearer <<space>> Token Generated in Step 1

5. Click on OK

6. That will load the following records.

7. It will also generate below PowerQuery code automatically for us.

Step 3: Configure Dynamic Bearer Token in Power BI for REST APIs

1. Open the Power Query code generated in step 2.7
2. Replace Bearer code with the function created in Step 1.
3. The code will look like below.

let
    Source = Json.Document(Web.Contents("https://api.symestic.com/api/downtimeReasons/user/734", [Headers=[Authorization="Bearer "&Authentication()]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"productionLineId", "downtimeReasonId", "description", "color", "isDeleted"}, {"productionLineId", "downtimeReasonId", "description", "color", "isDeleted"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"productionLineId", Int64.Type}, {"downtimeReasonId", Int64.Type}, {"description", type text}, {"color", type text}, {"isDeleted", type logical}})
in
    #"Changed Type"

4. That will generate dynamic token on every refresh.

Step 4: Schedule Refresh for Power BI Rest APIs

• Not every API in Power BI Report supports schedule refresh.
• If I publish the report having Step 1, Step 2 and Step 3 it will not support refresh.
• If I publish the report with only Step 2 and pass static Bearer Token then it supports refresh. This thing is totally dependent on your API.

Go to Schedule Refresh and make sure, you select skip test connection from “Datasource credentials”.

Conclusion:

This is how you can call Rest APIs from Power BI. If you need any paid consultation, write me here!

Download File