Truncated results from M script to pull query list from the Model's #shared or #section metadata

[SQLMonger]

I've been working on a model, and wanted to pull query name, column count, row count and column names metadata for the source queries into a table. The idea is to make a dynamic "index" of the other data tables being pulled into the workbook for review.  No major problems working through using the #section metadata as a source and filtering down to what I wanted. The code to do so is below, and should work in any model. 

let
    Source = #sections,
    Section1 = Source[Section1],
    #"Converted to Table" = Record.ToTable(Section1),
    FilterTables = Table.SelectRows(#"Converted to Table", each [Value] is table),
    #"Add TableCols" = Table.AddColumn(FilterTables , "Column Count", each try Table.ColumnCount([Value]) otherwise -1, Int64.Type),
    #"Add TableRows" = Table.AddColumn(#"Add TableCols", "Row Count", each try Table.RowCount([Value]) otherwise -1, Int64.Type),
    #"Add ColumnNames" = Table.AddColumn(#"Add TableRows", "Columns", each try Text.Combine((Table.ColumnNames([Value])), ", ") ),
    #"Expanded Columns" = Table.ExpandRecordColumn(#"Add ColumnNames", "Columns", {"Value"}, {"Column Names"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Columns",{"Value"})
in
    #"Removed Columns"

Well, It works in Excel, but only partially in Power BI Desktop. 

In Desktop, it queries and displays the full set of expected rows in the query editor, but when the table is referenced in the report, only the row for the results of the above query shows up. All other queries are excluded.

Version info:

• Excel - 1705 (Build 8201.2200 Click-to-Run)
• Power BI - 2.68.5432.841 64-bit (April 2019)

I wrote a version of the metadata query that uses the #shared metadata source instead, and get the same results.

Any ideas? Is this a defect, or is it a side-effect of the recursion that is probably going on.  

Cheers,

Clayton

[v-frfei-msft]

Hi @SQLMonger ,

The reason that you're getting different behavior in the Query Editor vs. Load is due to dependency minimization. When we load a query, we first trim out all unreferenced queries from the evaluation. In such a context, the #sections and #shared keywords can return fewer results. For example, if the other queries in your workbook aren't explicitly referenced by the query you're loading (and are thus removed by the minimization process), #sections won't return them during Load.

Maybe you can update your code like this and have a try.

 source = Record.ToTable(#sections[Section1]),

[SQLMonger]

Thanks @v-frfei-msft 

That makes sense, but sadly, that did not work. I tried a few more things, like filtering out the row for the name of the table being returned and adding an Index column. I still find it interesting that it works in the Excel engine, which is most likely a bit behind the desktop version.

One clue:

If you click on a row header in the table results in Power Query Editor, you will get an error message at the bottom of the screen of "Expression.Error: A cyclic reference was encountered during evaluation."

In Desktop, this happens no matter which row you click on. The behavior in Excel is different. You only get the error if you click on the table name of the query itself. For all other rows, the columns are displayed at the bottom of the screen.

Warm regards,

Clayton