Forum Discussion

MattSB's avatar
MattSB
Helper I
2 years ago
Solved

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

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 t...
Need Help
  • MattSB's avatar
    MattSB
    2 years ago

    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's avatar
MattSB
Helper I
2 years ago

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