Dynamic Datasource Error - Retrieving Dataset tables and metadata in the PBI Service

[jhayes0128]

I have a dataflow in the PBI service that hits several API endpoints to give me tables of all my dataflows, datasets, refresh times, and other metadata.

I have an idea to leverage the new Enhanced Refresh API in Power Automate to dynamically refresh my Power BI tables when they're refreshed at the source. My testing shows this is possible, but I need the Dataset table names and M query to make it work.

In my dataflow, I am generating a list of my datasets using the REST API. The table looks like this:

datasetName

Dataset_1

Dataset_2

Dataset_3

Since there is no API endpoint to retrieve the tables/queries, I insert a new step to dynamically insert the datasetName into an AnalysisServices connector to retrieve the tables/queries.

AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/MyFakeWorkspace", [datasetName], [Query="select * from $SYSTEM.TMSCHEMA_PARTITIONS", Implementation="2.0"])

This works and returns all of the data I need, HOWEVER i am unable to save this to the service due to 'Dynamic Data Sources'.

I use ReferencePath for the web.contents API calls and that works fine (I had the same issue there and this fixed it), but I haven't found a way to get around this one. Per the MS documentation, I could use Power Query parameters but so far I still get the same error.

Any ideas?

Thank you!

[gauthamboppana]

Hey @jhayes0128   - It's a known issue with using APIs in Dataflows. You get the error esp. when you are trying to make the API calls dynamic. Please refer to tehw ork around below and see if it works.

https://community.powerbi.com/t5/Power-Query/REST-API-Dynamic-Datasource-aren-t-refreshed/td-p/18116...

Did I answer your question? Mark my post as a solution! If not, please feel free to ask me.

Also, I would ❤ Kudos if my solution helped.  It is a token of appreciation!

Thank you very much !

[jhayes0128]

Thank you for the response. I do use RelativePath for calling REST APIs (works great), however there is no REST API endpoint for dataset table names and query definitions. So I need to use AnalysisServices.Database instead of web.contents. There is no RelativePath option on the AnalysisServices.Database connector.