cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SQLMonger Frequent Visitor
Frequent Visitor

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

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

2 REPLIES 2
Community Support Team
Community Support Team

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

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]),

 

 

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

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

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 60 members 1,221 guests
Please welcome our newest community members: