How to get a list of the queries in the Power BI Desktop report

[Lucian]

Hello,

I would like to have in a Power BI report a table visual listing all of the queries in the current report. I have used the #shared function and managed to get a list of the queries names but this list is visible only as I edit the query. As soon as I close it, in the report I have only one name - the query that should return all the list.

let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Value.Type([Value])),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "TypeOfValue", each fnTypeToText([Custom])),
    #"Filtered Rows" = Table.SelectRows(#"Invoked Custom Function", each ([TypeOfValue] = "table"))
in
    #"Filtered Rows"

where fnTypeToText is a custom function that convert each type to text:

let fnTypeToText = (valueType as type) as text =>
        if Type.Is(valueType, Text.Type) then
            "text"
        else if Type.Is(valueType, Number.Type) then
            "number"
        else if Type.Is(valueType, Table.Type) then
            "table"
        else if Type.Is(valueType, Function.Type) then
            "function"
        else if Type.Is(valueType, Type.Type) then
            "type"
        else if Type.Is(valueType, Record.Type) then
            "record"
        else
            "unknown"
in fnTypeToText

In editing mode I have all the tables:

Editing Mode

But in data mode I have only one:

I have tried also with the #sections function as source but the results were the same.

Is there a way to get the full list of the tables/queries into a table visual from the report?

Kind Regards,

Lucian

[v-juanli-msft]

Hi @Lucian 

I can't reproduce your problem.

Could you show me an example of your data?

Best Regards
Maggie

[Lucian]

Hello @v-juanli-msft 

It seems that I don't have a way to attach a PBIX file so I will describe the steps to reproduce the behaviour:

1. In PowerBI Desktop I've opened the PowerQuery editor (Edit Queries)

2. Created function query named fnTypeToText that will convert type of the query to text using this code:

let fnTypeToText = (valueType as type) as text =>
        if Type.Is(valueType, Text.Type) then
            "text"
        else if Type.Is(valueType, Number.Type) then
            "number"
        else if Type.Is(valueType, Table.Type) then
            "table"
        else if Type.Is(valueType, Function.Type) then
            "function"
        else if Type.Is(valueType, Type.Type) then
            "type"
        else if Type.Is(valueType, Record.Type) then
            "record"
        else
            "unknown"
in fnTypeToText

3. Create the query that will list all the queries from the model, named A list of queries using this code:

let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Value.Type([Value])),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "TypeOfValue", each fnTypeToText([Custom])),
    #"Filtered Rows" = Table.SelectRows(#"Invoked Custom Function", each ([TypeOfValue] = "table"))
in
    #"Filtered Rows"

4. I create 2 dummy queries named Query1 and Query2 using this code:

let
    Source = #table({"First Column", "Second Column"}, {{1,2},{3,4}})
in
    Source

5. Now refreshing the query named A list of queries I get the list of all the three queries:

6. But when I "Close & Apply" in the data model will remain only one query:

So, how could I obtain in the data model all the queries so I could use them further into a table visual?

Kind Regards,

Lucian

[v-juanli-msft]

Hi @Lucian 

As tested, when i click on the "Table" cell for "Table2" row, it would generate a table of "Table2".

But if you load the model to Power Pivot data model(close&&apply), we can't get such table as the same step in Power Query.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lucian]

Hi @v-juanli-msft,

Thank you for your message, but I don't want the content of the table, I want just the name of the table.

I just want to obtain a kind of "inventory" of the queries used into that report. I could copy the list of names from the query (just the Name column) as I edit it and then create a table based on that list, but this list is not dynamic.

So, I could reformulate my questions: Is there a way to get a "dynamic" list of names of the queries used into that report as a list or table?... or this is not possible (yet)?

Kind Regards,

Lucian

[v-juanli-msft · Accepted solution]

Hi @Lucian 

I'm afraid it is impossible.

Best Regards
Maggie

[Lucian]

Hi @ v-juanli-msft,

Thank you for your response... even is not what I hoped... 😉 

But at least I don't chase the wind anymore... 🙂

Kind Regards,

Lucian

[v-juanli-msft]

Hi @Lucian 

Click on the "Table" field, or expand the "Value" column to check if it gives data as you expected.

https://support.office.com/en-us/article/expand-a-column-containing-a-related-table-power-query-d5e5...

Best Regards
Maggie