Dynamic API Query Run Unable to Refresh on PowerBI Service

[BH22One]

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

Context:

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"

= () =>
let
body = "grant_type=password

&scope=ABCDE

&client_id=ABCDE

&client_secret=ABCDE

&username=ABCDE

&password=ABCDE",
Data=Json.Document(Web.Contents("https://US.api.ABCDE.com/connect/token", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])),
access_token = Data[access_token]
in
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.

= () =>
let

Source =Json.Document(Web.Contents("https://US.api.ABCDE.com/api/queryruns",

[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]
in
id1

Step 3: Get Data to be used in Report

= Csv.Document(Web.Contents("https://US.api.ABCDE.com/api/completedqueryrun/"&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.

Thanks,

Brandon S Hyde

[collinq]

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

[BH22One]

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.

[ZeDe]

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

Kind regards