Help with Odata Api call Refresh status returning Dynamic Datasources, which cannot be refreshed

[UNICODE]

Hello,  I am trying to build a query that returns the Refesh status of the Power Bi reports on the on prem Power Bi RS server.   
I am trying to do this by using three api calls and Odata feed connector.   It works on the desktop but returns the "Dynamic Datasource, wich cannot be refreshed" error on the server.   

API Calls that I am using:
/PowerBIReports?$select=Id,Path,CreatedBy
/PowerBIReports(ID)/CacheRefreshPlans?$select=Id
/CacheRefreshPlans(ID)/History

I have created functions for the CacheRefreshPlans and History Api call.  These are called using the Add Column/Invoke custom  Function command to bring the results to the main query table which is the Power Bi report Id, Path and Created By.  

Here is an example of the Function of the History call 

let
Source = (group as text) =>
let
Source = OData.Feed("servername/reports/api/V2.0/CacheRefreshPlans(" & group & ")/History", null [Implementation="2.0"]),
#"Filtered Rows" = Table.SelectRows(Source, let latest = List.Max(Source[StartTime]) in each [StartTime] = latest)
in 
#"Filtered Rows"
in
Source 

I have tried changing this over to a web connection but I still get the same dynamic error on the server. 

 
I have also tried calling the top level of the API  Server/reports/api/V2.0  with the Odata, then Navigating to the PowerBIReports  expanding CacheRefreshPlans  and futher expanding CacheRefreshPlans.History.   This gives me the wanted data but when refreshed in the server the report returns blank without any error messages.  

Thanks for any assistance provided. 

[v-prasare]

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare · Accepted solution]

Hi @UNICODE,

The main issue usually happens when Power BI Report Server doesn’t allow dynamic URLs during refresh. Instead of building URLs on the fly, the best approach is to connect once to the top-level OData endpoint and then navigate through the tables to get what you need. For example, you can go into the PowerBIReports table, expand the  CacheRefreshPlans, and then expand the History column to view recent refresh details.

If you do need to use  Web.Contents, make sure to use the RelativePath option and wrap it with  Json.Document() so that the data returns as a proper table. Also, make sure to check for any permission issues conflicts between AD groups and Row-Level Security can also cause unexpected refresh problems.

Thanks,

Prashanth

[UNICODE]

Thanks for the link.   

My main issue ended up being conficts between Ad groups and Row Level security. 

I did try to reformat the link with RelativePath and web contents.   Instead of getting back a table of values it was returning a file at that point.   I saw other examples that placed a Json document command around the web contents, but I was having issues getting it formatted correctly.   

For those playing along at home here is my code that drilled down to the History column of the CacheRefreshPlan on the Power Bi report:

let
Source = OData.Feed("Servername/reports/api/v2.0/PowerBiReports?$selectId,Path,CreatedBy,CacheRefreshPlans", null, [Implementation = "2.0"]),
#'Expanded CacheRefreshPlans" = Table.ExpandTableColumn(Source, "CacheRefreshPlans", {"Id","LastRunTime","LastStatus","History"},{"CacheRefreshPlans.Id","CacheRefreshPlans.LastRunTime","CacheRefreshPlans.LastStatus","CacheRefreshPlans.History"}),

Then you can expand the History column to see the status of the last few refreshes.  

[v-prasare]

Hi @UNICODE,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@lbendlin,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[lbendlin]

I have tried changing this over to a web connection but I still get the same dynamic error on the server. 

Try again, using the RelativePath option.    https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1