Forum Discussion

thangdev's avatar
thangdev
Regular Visitor
2 years ago
Solved

split table into many tables at specific rows

Hi Everyone,  I have a data as below. I would like to split my table into 3 small table at these rows which is null of column "item".     let Source = Excel.CurrentWorkbook(){[Name="Table1"]...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    See the sample code below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY47DgQhDEPvknoayIfZsyDKXIBB4vobk5V2Ckf44VjpnegiFY3JxjSuTpXDlBrjeQ5Qw29ozn1APOGWZ15KOAHZex5k2EDnBsolEUBL+CMobUki+Oq29jLtzjNQeId8+cqwMho+oeX+LzhJ5VqUxvgC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Category = _t, Test = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Category", Int64.Type}}),
    cnt = Table.RowCount(#"Changed Type"),
    list_item = List.Buffer(#"Changed Type"[Item]),
    temp_tbl = Table.FromColumns(Table.ToColumns(#"Changed Type") & {List.Generate(()=>[x=0,i=0], each [i]<cnt, each [i=[i]+1, x=if list_item{i}=null and list_item{[i]}<>null and i+1<>cnt then [x]+1 else [x]], each [x])}, Table.ColumnNames(#"Changed Type") & {"Index"}),
    #"Grouped Rows" = List.Transform(Table.Group(temp_tbl, {"Index"}, {{"All", each _}})[All], (x)=>Table.RemoveColumns(Table.SelectRows(x, each _[Item]<>null), "Index"))
in
    #"Grouped Rows"
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
2 years ago

See the sample code below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY47DgQhDEPvknoayIfZsyDKXIBB4vobk5V2Ckf44VjpnegiFY3JxjSuTpXDlBrjeQ5Qw29ozn1APOGWZ15KOAHZex5k2EDnBsolEUBL+CMobUki+Oq29jLtzjNQeId8+cqwMho+oeX+LzhJ5VqUxvgC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Category = _t, Test = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Category", Int64.Type}}),
    cnt = Table.RowCount(#"Changed Type"),
    list_item = List.Buffer(#"Changed Type"[Item]),
    temp_tbl = Table.FromColumns(Table.ToColumns(#"Changed Type") & {List.Generate(()=>[x=0,i=0], each [i]<cnt, each [i=[i]+1, x=if list_item{i}=null and list_item{[i]}<>null and i+1<>cnt then [x]+1 else [x]], each [x])}, Table.ColumnNames(#"Changed Type") & {"Index"}),
    #"Grouped Rows" = List.Transform(Table.Group(temp_tbl, {"Index"}, {{"All", each _}})[All], (x)=>Table.RemoveColumns(Table.SelectRows(x, each _[Item]<>null), "Index"))
in
    #"Grouped Rows"