How return a list of query names and descriptions

[Skazal]

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?

[AlienSx]

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

and choose whatever field you need?

[lbendlin]

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

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 · Accepted solution]

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]

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

[lbendlin]

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

[Skazal]

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.

[lbendlin]

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

[Skazal]

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.

[lbendlin]

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.

[Skazal]

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]

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.