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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jhayes0128
Frequent Visitor

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

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

 

jhayes0128_0-1659363853995.png

 

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!

 

 

2 REPLIES 2
gauthamboppana
Solution Specialist
Solution Specialist

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 !

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors