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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I
Helper I

Dynamic API Query Run Unable to Refresh on PowerBI Service

Good Day!


I am stuck in a situation where I have a dymanic condition within my API call that works fine within Power BI Desktop however is not allowed within Power BI Service. The reason for this (based on some researching) is that my API call has an element in which some or all of the information required to connect cannot be determined until Power Query runs its query, because the data is generated in code or returned from another data source.


The message I receive on Power BI Service is as follows:


You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

  • Data source for Query1


I am working on a reporting solution where the source data generates 8 reports between 8am - 5pm. Each report generates a unique Report ID which is needed in the API call. I was able to dynamically generate a new Bearer token with each refresh since the users want this report updated each hour. 

In Power BI, I created a web API call to get a list of all the reports generated and use Power Query to identify the ReportID that has the most recent runtime.


Get Data Steps (API):

Step 1: (Generate Bearer Token) - fx GetAccessToken() - Function (Note because the access credentials are sensitive I am replacing this information with "ABCDE"

= () =>
body = "grant_type=password





Data=Json.Document(Web.Contents("", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])),
access_token = Data[access_token]


Step 2: (Generate and Retrive ReportID) fx GetReportId() - Allows me to retrive the most recent ReportID, utilizes step one where the Bearer token is needed. In order to retrive the most recent ReportID I had to utilize some Power Query editing.

= () =>

Source =Json.Document(Web.Contents("",

[Timeout=#duration(0, 0, 30, 0),

Headers=[Authorization="Bearer "&GetAccessToken(), ContentType="application/json; charset=utf-8"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"creatorFirstAndLastName", "endTime", "id"}, {"creatorFirstAndLastName", "endTime", "id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"creatorFirstAndLastName", type text}, {"endTime", type datetime}, {"id", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", let latest = List.Max(#"Changed Type"[endTime]) in each [endTime] = latest),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"creatorFirstAndLastName", "endTime"}),
id1 = #"Removed Columns"{0}[id]


Step 3: Get Data to be used in Report

= Csv.Document(Web.Contents(""&GetReportId()&"/CSV", [Timeout=#duration(0, 0, 30, 0), Headers=[Authorization="Bearer "&GetAccessToken(), ContentType="application/octet-stream"]]),[Delimiter=",", Columns=12, Encoding=65001, QuoteStyle=QuoteStyle.None])


I'm not sure if a workaround exists but ultimately if I could make a dynamic function for "GetReportId()" that doesn't involve the steps from Power Query I believe that would resolve my issue. I'm not an expert with .json and api calls but I was wondering if there was a way to insert some sort of where clause where I could tell the web api call to only retrive the ReportId of the most recent endtime.


If anything in my example or description doesn't make sense I'd be happy to provide more context.



Brandon S Hyde


Super User
Super User

Hi @BH22One ,


I doubt there is a problem in your syntax.  I believe that the issue is that Power BI Service does not refresh dynamic API's.


See here:

Setting a scheduled refresh on a Dynamic Data Source in Power BI | by dataninja | DataDrivenInvestor 

and here:

Schedule Refresh in Service for Dynamic API Query - Microsoft Power BI Community

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.

Thanks for the reply @collinq but on the contrary, I've gotten this same syntax to work with the reportId step I mention above. The function that generates the Bearer token is dynamic as I'm using the result from this API call (Step 1) within Step 3 which authorizes my connection. If I simply hard code a reportId in the code (instead of generating it dynamically in step 2), it can be scheduled to run on Power BI Service. The problem with that is, the reportId changes every hour so I'm having ti dynamically generate that as well. I'm sure this can be done given all the "code hacks" and tips I've seen on youtube and in this forum. I'm going to continue looking for a solution but hopefully someone else has had a similar issue and views this post at some point.

Frequent Visitor

Hey, I'm running into the same issue.
Did you find a solution?

Kind regards

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors