cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calling PowerBI Rest API in Power Query

Hi Folks,

I'm trying to pull data from the Power BI REST API into Power Query and need some guidance.   I've seen numerous threads/blogs describing how to go about this, but none of them have a complete solution, only partial. 

 

1. To get data into Power Query from the Power BI REST API, should "Web" be used as the data source?  (Some posts have said to use "blank query") 

 

2.  Whether it's Web or BlankQuery, how to pass Authentication?  Signing into Power BI as usual doesn't seem to do the trick.

 

3. I've seen lots of posts about APIs, other than Power BI, talking about "tokens"... is a token necessary to get data from the PowerBI REST API?

 

4. Do Headers need to be anywhere when calling the REST API?

 

Thanks much for all feedback on this!

4 REPLIES 4
Helper IV
Helper IV

I'm just starting this journey and it seems to me that it varies greatly depending on the vendor's API.  I could not get the Web connection to work in my situation.  Therefore, I jumped over to a blank query.  Once there click on Advance Editor button in the middle of toolbar to get started.

 

As I understand it, all Rest APIs require a token by definition.  However, the token may or may not expire.  If a token does not expire you can retrieve it once (using a tool called PostMan) and then hardcode it in Power Query.  If it expires, the following code "should" be able to retrieve it.  Yes, you will need to pass header and body details.

 

Again, it varies, but the vendor we used, the API authentatication was within the query itself.  Personally, seems like a terrible design decision, but not my call.  Some APIs I believe would authentic by using the "Data Source Settings" icon within Power Query.  

 

All the code you need is included below...

 

 

 

RefreshAPIToken = () as text =>
    let
        token_url = "https://www.example.com/api/",
 
//The EXACT parameters vary from API to API
//Include EscapeDataString if the parameter value include special characters

UserID = "USERID_VALUE",
Password = Uri.EscapeDataString("PASSWORD_VALUE"),
Client_ID = Uri.EscapeDataString("CLIENT_ID_VALUE"),
Client_Secret = Uri.EscapeDataString("CLIENT_SECRET_VALUE"),
Database = "DATABASE_VALUE",
 
body="Username="&UserID&"&Password="&Password&"&grant_type=password&Integrated=N&database="&Database & "&Client_Id=" & Client_ID & "&Client_Secret=" & Client_Secret,
 
params =
        [ 
            Content = Text.ToBinary(body),
            RelativePath = "Token",                //use address that retrieves token from specific API
            Headers = [#"Content-Type"="application/x-www-form-urlencoded"]
        ],
    WebSource = Json.Document(Web.Contents(token_url,params)),
    apikey =  WebSource[access_token]   //use fieldname that stores token within specific API
          
in
apikey,

 

 

 

 

Rest API retrieve data by pages, so you will need to loop somehow to get each page:

  

 

 

GetPages = () =>
        let
            apikey = RefreshAPIToken(),

            //Cycle each page adding the results, one call at a time
            //This API does not give a total count of pages back, so you go until you hit null
            jobsJsonPaginated = List.Generate( () => 
                [counter = 1,pageResult=FetchPage(apikey,1),pageSize=0],
                    each [counter]<=1 or [pageSize]>0,
                    each [
                            counter = [counter] + 1 , 
                            pageResult = try FetchPage(apikey,counter) otherwise null,
                            pageSize = try List.Count(pageResult) otherwise 0
                        ],
                    each [pageResult]),
            jobsJsonPaginatedOut = List.RemoveNulls(jobsJsonPaginated)
        
        in 
            jobsJsonPaginatedOut,

 

 

 

Finally, you will need to fetch each page:

 

FetchPage = (apikey,page) =>
        let
        Source = 
                Json.Document(
                    Web.Contents(
                        "https://www.example.com/api/Table_Name", 
                        [
                            Headers=[Authorization="Bearer "&apikey, ContentType="application/json;"], 
                            Query=[#"page"=Text.From(page),#"pageSize"="50"]
                        ]
                    )
                )
        in
            Source,

 

 

 

You can build a table with this code:

 

 

let

Table_Name = #table(
       {"Table_Name"},
       {{"Table_Name Source"}}
    ),
 
Output = 
    //Initial table creation
        Table.AddColumn(
            Table_Name,  
            "Column1",
            each GetPages()
        ),

    //Build additional columns into the table
    #"Expanded Column1" = Table.ExpandListColumn(Output, "Column1"),
    #"Expanded Column2" = Table.ExpandListColumn(#"Expanded Column1", "Column1"),

    //Add all fields
    #"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1",  {"FIELD_NAME1", "FIELD_NAME2", "FIELD_NAME3"})
in
    #"Expanded Column3"

 

 

 

 

One other remark, be careful about copy and paste with the "".  For whatever reason, these will occasionally be slightly different quotation marks that won't be recognized as strings within the editor.  I have seen it happen with other programming languages, but just figured I would remind you as it got me once already...

Thanks much for the info bflury.   Yes, each API is called differently.  Microsoft's documentation here on using the PowerBI API seems so simple:  https://docs.microsoft.com/en-us/rest/api/power-bi/apps/getapps .  My problem is how to translate that to M code and use in Power Query.

Helo and thank you for your message, this is exactly the situation i am into. Any news from then?

 

I want to access de PowerBi REST API, into PowerQuery in order to load in a PowerBi Report the date of last update of the list of reports i'm giving access to... 

I was looking at Json.document(Web.Content()) a bit like in the following post:
https://www.mssqltips.com/sqlservertip/6318/read-api-data-with-power-bi-using-power-query/

But it requieres a Token, and without the Token it gives a message error or credencial issues

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors