Forum Discussion
Issues with Table.Schema when iterating over list from #shared
- 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 GetTablehowever 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 ExpandValuesHowever 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
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