Forum Discussion
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
- Nathaniel_C
Community Champion
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 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.
