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