cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DiscreetRaven
Regular Visitor

READ 'property' names / descriptions from PBIX.

In Power Query, each 'Query' and each 'Applied Step' in each 'Query' has a name/label and an optional 'Description'. I use these fields as internal documentation to clarify the purpose of each 'Query' and its 'Applied Steps'. I'd like to read these field values as data to populate an external document. Can you tell me how that is done, please?

Similarly, I'd like to read the 'Names' and 'Descriptions' of all tables, columns and measures - as well as each measure's DAX text. What technique do I apply to accomplish this?

BONUS: Can this information also be read from deployed Dataflows and Datasets?

2 REPLIES 2
DiscreetRaven
Regular Visitor

Thanks for taking a shot, @Nathaniel_C,. DAX Studio appears to be a good place to go, but the specific examples in this community post don't function as they may have four years ago. From within SSMS (or, DAX Studio), 'Properties' can be captured from datasets (not dataflows) for 'Names' and 'Descriptions' of TABLES and COLUMNS using: 

  • SELECT [ID], [name], [description] FROM $system.tmschema_tables WHERE [systemflags] = 0
  • SELECT [explicitname], [description], [expression] FROM $system.tmschema_columns WHERE [tableID] = [ID]

'Properties' can be captured for MEASURES using:

  • SELECT [MEASURE_NAME], [DESCRIPTION], [EXPRESSION] FROM $SYSTEM.MDSCHEMA_MEASURES

Beautiful. That still leaves me with my original objective yet to be solved - capturing the name and description of individual 'steps' from each 'power query' query. Oddly, this DMV returns nothing. This makes no sense since (almost) all query imports include a 'source' and a 'navigation' step:

  • SELECT * FROM $SYSTEM.DISCOVER_M_EXPRESSIONS

This one comes close, except for the fact that ALL steps are contained as a single row of text per query.

  • SELECT * FROM $SYSTEM.TMSCHEMA_PARTITIONS

Yes, the result can be parsed and split by line feed, but its not 'partitions' I'm looking for. I'm betting on 'discover_m_expressions' being the better solution, once its fully supported for PowerBI.

This post from Chris Webb caught my interest. It's two years old, but a reader's comment made earlier this year identified the 'discover_m_expressions' view as problematic - not returning complete results. I'm witnessing the same. . .

UPDATE:  "DISCOVER_M_EXPRESSIONS" NOT CURRENTLY SUPPORTED IN POWERBI USING THE XMLA ENDPOINT

Nathaniel_C
Super User I
Super User I

Hi @DiscreetRaven , here is an interesting read on part of your question. https://community.powerbi.com/t5/Desktop/Is-there-a-way-to-retrieve-all-measures/td-p/144939 


If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors