Issues with Table.Schema when iterating over list from #shared

[MattSB]

I've came across some strange behaviour when trying to use the #shared and #sections to generate a list of tables and output them to a powerbi semantic model and generate table schemas.

Ive put together a dummy set with two tables

• City = {{"id", type text}, {"countryId", type text}, {"CityName", type text}, {"Pop", type text}
• Country = {{"id", type text}, {"isoCode", type text}, {"Country", type text}}

// City
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAePkzJJKhUQgw8jQGIyUYnWilYyQZZNAHCNjQ6C0UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, countryId = _t, CityName = _t, Pop = _t])
in
    Source

// Country
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQKi5PzSvJKiSgVDpVidaCUjVEEjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, isoCode = _t, Country = _t])
in
    Source

In power query editor the following queries works perfectly

let
    Source = #sections,
    SelectSection = Source[Section1],
    ConvertedtoTable = Record.ToTable(SelectSection),
    FilterTableType = Table.SelectRows(ConvertedtoTable,  each Value.Is([Value], type table) )
in
    FilterTableType

 But seems to indiacte an unexpected error occured

When loaded into PowerBi hoewever i get 

The same happens with using #shared and Table.Schema

let
    TargetTables = {"Country" , "City"} ,
    Source = Record.ToTable( Record.SelectFields(#shared, TargetTables) ),
    // Filter to only tables just in case
    FilterTableType = Table.SelectRows(Source,  each Value.Is([Value], type table) ),
    RenameCols = Table.RenameColumns(FilterTableType,{{"Name", "TableName"}}),
    //For each table in value creat a schema
    Generate  = Table.TransformColumns( RenameCols , {"Value" , each Table.Schema(_) } ),
    ExpandValues = Table.ExpandTableColumn(Generate, "Value", {"Name", "Position", "TypeName", "Kind", "IsNullable"}, {"Name", "Position", "TypeName", "Kind", "IsNullable"})
in
    ExpandValues

Generates with no errors

however loads a blank table into PowerBi

Table.Schema where i directly reference the target table loads fine. 

Im not sure whats going wrong here. Any Ideas?

[MattSB · Accepted solution]

I also managed to generate a new error

The following table actually generated 

// GetListOfTables (2)
let
    TargetTables = {"Country", "City"},
    GetTable = Record.ToTable( Record.SelectFields(#sections[Section1], TargetTables) )
in
    GetTable

however when using it in the recursive Table.Schema query it failed?

// GetListOfTables (3)
let
    TargetTables = {"Country", "City"},
    GetTable = Record.ToTable( Record.SelectFields(#sections[Section1], TargetTables) ),
    RenameCols = Table.RenameColumns(GetTable,{{"Name", "TableName"}}),
    //For each table in value creat a schema
    Generate  = Table.TransformColumns( RenameCols , {"Value" , each Table.Schema(_) } ),
    ExpandValues = Table.ExpandTableColumn(Generate, "Value", {"Name", "Position", "TypeName", "Kind", "IsNullable"}, {"Name", "Position", "TypeName", "Kind", "IsNullable"})
in
    ExpandValues

However when i filter the table list to only tables, I get blank rows when refreshed in PowerBi

My current thinking is the #sections and #shared don't behave how i think during a refresh and that Value.Is()  does not work how id expect when checking if type is a table.

// GetListOfTables (2)
let
    TargetTables = {"Country", "City"},
    GetTable = Record.ToTable( Record.SelectFields(#sections[Section1], TargetTables) ),
    FilterTableType = Table.SelectRows(GetTable,  each Value.Is([Value], type table) )
in
    FilterTableType

[MattSB]

HI Yolo,

Thanks for your response, I don't get a cyclic reference. I described the tables as a their schema so believe you might have built it differnently. HOwever the cyclic bit does make a bit of sense as im quering the evironment.

I know what is causing the core issue in this case is that  #shared and # source aren't evaluating to a list of tables in powerbi however do in the powerquery environment. But the why is not just cyclic.

The below has no cyclic elements and returns the behaviour, unless #shared and #section creates one by default. If so can some one explain how to even use #shared to query environment data when the object is apart of the environment. 

// Country
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQKi5PzSvJKiSgVDpVidaCUjVEEjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, isoCode = _t, Country = _t])
in
    Source

// City
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAePkzJJKhUQgw8jQGIyUYnWilYyQZZNAHCNjQ6C0UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, countryId = _t, CityName = _t, Pop = _t])
in
    Source

// GetListOfTables
let
    Source = #sections,
    SelectSection = Source[Section1],
    ConvertedtoTable = Record.ToTable(SelectSection),
    FilterTableType = Table.SelectRows(ConvertedtoTable,  each Value.Is([Value], type table) )
in
    FilterTableType

[Anonymous]

Hi @MattSB 

I try the code you offerwered, it display the following error.

You have a cycle reference, you can refer to the following link.

https://blog.crossjoin.co.uk/2023/01/22/understanding-the-a-cyclic-reference-was-encountered-during-...

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.