Forum Discussion
Possible to Query Keywords in SQL Statement Datasource Using API?
You have to run it against each dataset (that you have access to) separately. However you can sort of automate that by running the queries from PowerShell.
Invoke-ASCmd -Server "powerbi://api.powerbi.com/v1.0/myorg/<workspace name here>" -Database "<dataset name here>" -Query "select * from `$SYSTEM.TMSCHEMA_PARTITIONS" | Out-File -FilePath c:\users\xxx\Downloads\dmv.xml
You're in for a treat. You will want to learn about XMLA endpoints, and DMX queries. You can run these against all your datasets and extract the complete M code for all connections (including the embedded SQL).
NOTE: This is only valid for Import Mode connections. For Direct Query you have no control over, or visibility of the queries that are generated on the fly for each visual.
Thank you, do you have any helpful links or resources you'd reccomend?
There's DAX Studio , but beyond that the air is pretty thin. Mostly trial and error.
I have DAX studio, but I'm unfamilar with any function or code that extracts the M code. I'll do some digging, was just hoping to be pointed in the right direction. Thanks again.
In DAX Studio connect to your dataset (local or in service)
Run two queries (individually)
select * from $SYSTEM.TMSCHEMA_PARTITIONS
select * from $SYSTEM.TMSCHEMA_EXPRESSIONS
Grab the QueryDefinition column from the first and the Expression column from the second
Rinse and repeat for all your datasets.