Call Rest APIs from Power BI / Power Query


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.


Sometime, Rest APIs in Power BI does not support Refresh when you publish your report in Power BI Service.

Make sure, you check if your API supports refresh or not!

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

() =>
    body = [username="",password="999999"],
   Data=Json.Document(Web.Contents("", [Headers=[#"Content-Type"="application/json"], Content=Json.FromValue(body)])),
    result = Record.Field(Data[result]{0},"token")



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 =
  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.
    Source = Json.Document(Web.Contents("", [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}})
    #"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”.


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

Download File

The sample file uploaded to GITHUB.

Leave a Reply

Leave a Reply

%d bloggers like this: