Forum Discussion

JajatiDev's avatar
JajatiDev
Icon for Helper II rankHelper II
2 years ago

Splitting a table of tables

Hi,

I created a table of tables with the following query.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Raw_Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Scheme or Stock name", type text}, {"Investment date", type date}, {"Investment price", type number}, {"Units", type number}, {"Investment amount", type number}, {"Current price", type number}, {"Current market value", type number}, {"Gain", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Scheme or Stock name", Order.Ascending}, {"Investment date", Order.Ascending}}),
    Add_Index = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    Add_Previous = Table.AddColumn(Add_Index, "PreviousContent", each try if [Index] = 1 then null else Add_Index{[Index]-2} [Scheme or Stock name] otherwise null),
    Add_ChangeFlag = Table.AddColumn(Add_Previous, "ChangeFlag", each if [Scheme or Stock name] <> [PreviousContent] then 1 else 0),
    Add_Group = Table.AddColumn(Add_ChangeFlag, "Group", each List.Sum(List.FirstN(Add_ChangeFlag[ChangeFlag], [Index]))),
    GroupedTables = Table.Group(Add_Group, "Group", {{"AllData", each Table.RemoveColumns(_, {"PreviousContent", "ChangeFlag", "Group", "Index"}), type table [Content=nullable text]}}),
    #"Changed Type1" = Table.TransformColumnTypes(GroupedTables,{{"Group", Int64.Type}})
in
    #"Changed Type1"

 

Now I'm trying to expand these tables into separate tables dynamically becasue the number of tables in the table of tables is subject to change.

 

Please guide me on the next steps.

1 Reply

  • Not possible. Power Query does not support dynamic creation of data sources.