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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Skazal
Frequent Visitor

How return a list of query names and descriptions

In Power BI Power Query, I'm trying to create a table that contains the all the query names and descriptions (from query properties).   I can get a table of query names with this:

 

 

 

let
Source = #sections,
Section1 = Source[Section1],
#"Converted to Table" = Record.ToTable(Section1),
#"getTables" = Table.SelectRows(#"Converted to Table", each [Value] is table),
#"Removed Columns" = Table.RemoveColumns(getTables,{"Value"})
in
#"Removed Columns"

 

 

 

But, how do you return the description value from the query properties?

 

You can do this in DAX Studio with the query:

 

 

SELECT [name], [description] FROM $system.tmschema_tables WHERE [systemflags] = 0

 

 

 How can this be accomplished in Power BI via Power Query?

1 ACCEPTED SOLUTION
ZhangKun
Resolver III
Resolver III

It is maybe impossible to achieve this by relying on Power Query. The Power Query user interface hides some content, such as some internal functions. I guess the query description should be one of them. When you write a query, the internal content is actually like this:

section Section1;

[ Description = "description" ]
shared query1 = let
    Source = 666
in
    Source;

Based on the context, you should be able to guess the meaning of the code. Description is used to record the description information of the query, but unfortunately this field does not seem to be open to users. It is only open to software. Even in the Power Query SDK, this field cannot be accessed.

[Description="Desc", DataSource.Kind="PQExtension3", Publish="PQExtension3.Publish"]
shared PQExtension3.Contents = (optional message as text) =>
    let
        _message = if (message <> null) then message else "(no message)",
        a = "Hello from PQExtension3: " & _message
    in
        a;

shared PQExtension3.OutputMeta = () =>
// Error: The field 'Description' of the record wasn't found.
// But Other field can access(DataSource.Kind, Publish)
    Value.Metadata(PQExtension3.Contents)[Description];

 

View solution in original post

11 REPLIES 11
AlienSx
Super User
Super User

= Value.Metadata(Value.Type(#shared[List.Range]))

and choose whatever field you need?

@AlienSx we are specifically hunting for the Description attribute of a query

 

lbendlin_0-1725114847644.png

so Value.Type is not applicable, and Value.Metadata by itself does only expose the QueryFolding attribute, not the Description attribute.

 

@Skazal Of course it is possible to run the DMV queries from Power Query via self-referencing, but that is cheating.

ZhangKun
Resolver III
Resolver III

It is maybe impossible to achieve this by relying on Power Query. The Power Query user interface hides some content, such as some internal functions. I guess the query description should be one of them. When you write a query, the internal content is actually like this:

section Section1;

[ Description = "description" ]
shared query1 = let
    Source = 666
in
    Source;

Based on the context, you should be able to guess the meaning of the code. Description is used to record the description information of the query, but unfortunately this field does not seem to be open to users. It is only open to software. Even in the Power Query SDK, this field cannot be accessed.

[Description="Desc", DataSource.Kind="PQExtension3", Publish="PQExtension3.Publish"]
shared PQExtension3.Contents = (optional message as text) =>
    let
        _message = if (message <> null) then message else "(no message)",
        a = "Hello from PQExtension3: " & _message
    in
        a;

shared PQExtension3.OutputMeta = () =>
// Error: The field 'Description' of the record wasn't found.
// But Other field can access(DataSource.Kind, Publish)
    Value.Metadata(PQExtension3.Contents)[Description];

 

Skazal
Frequent Visitor

So close, yet so far away.  Thank you for your insight.

Out of curiosity - what are you planning to use the Description data for?

I was going to use the value as an identifier for custom page navigation.  In the report, clicking on a specufic row in a table visual would pass the value for the target page (select row, then click button).  Drill-through isn't an option becase the target page formats will be different.

But the description is a property of a query, not of a page. Can't see the value of that.

Let me restate that,  I want to create a table having a column with the full names of the queries and a column with the short identifiers (from the description value).   The short id would match the name of the report page for that query.

The short id would match the name of the report page for that query.

That is not a thing in general. It's also not something you should do .  Power Query partitions  (what you call "query") and pages have no physical or logical correlation.

I have a fondness for novel and clever ways to harmonize with logic.   I have this working now by placing that ID value as a delimited substring in the partition/query name.  Putting it in a special place (description value) was a hopeful alternative.  

lbendlin
Super User
Super User

That metadata seems to be lost in translation

Power Query M Primer (Part 20): Metadata | Ben Gribaudo

 

Not even Value.Metadata(#sections[Section1][Table])  yields more than a QueryFolding record.

 

You have to be careful too - the data includes a self reference to the current query, and it will have errors for Direct Query connections.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.