cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DiscreetRaven
Frequent 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
Frequent 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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors