Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!

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 =>
        token_url = "",
//The EXACT parameters vary from API to API
//Include EscapeDataString if the parameter value include special characters

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





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




GetPages = () =>
            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)




Finally, you will need to fetch each page:


FetchPage = (apikey,page) =>
        Source = 
                            Headers=[Authorization="Bearer "&apikey, ContentType="application/json;"], 




You can build a table with this code:




Table_Name = #table(
       {{"Table_Name Source"}}
Output = 
    //Initial table creation
            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"})
    #"Expanded Column3"





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: .  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:

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

Helpful resources


Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors