cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dan_Sprague
Advocate I
Advocate I

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
Anonymous
Not applicable

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"

 

 

 

 

Anonymous
Not applicable

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
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors