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 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?

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

     

     

     

     

     

3 Replies

  • 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

     

     

     

     

     

     

     

     

     

     

  • MattSB's avatar
    MattSB
    Helper I
    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