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"]}[Content],
    add_index = Table.AddIndexColumn(Source,"index",1,1),
    list_of_position = Table.SelectRows(add_index,(r)=>r[item]=null)[index],
    Custom1 = List.Transform(list_of_position,(item)=>
Table.SplitAt(add_index,item)

)
in
    Custom1

 

I tried with Table.SplitAt , however this function can not work with list of number. Could you please assist for this case? 

 sample file 

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"

3 Replies

  • 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"
  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi thangdev,

     

    2 versions of code here (v2 is faster and similar to Vijay's)

    let
     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tU5bDsMgDLtLvvvTUmj3ufchUP+WHQCQcv3ZYUK7wCQcxSaxk7OcZZK4RtSQghxTlhv6eUGp1fmVf0Ap5hwtWVNnF/QruVlx4U5DwKj0DWakLg3HrXMM/ZimbVzAkX0Y7oA2bTYyT0BT/a4/GMrUGJY5uvTkEFPr6433j5TjAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Category = _t, Test = _t]),
    v1_ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Item", "Category"}),
    v1_ItemNullPositions = [ nullPos = Table.FromList(List.Transform(List.PositionOf(v1_ReplacedValue[Item], null, Occurrence.All), each _ +1), Splitter.SplitByNothing(), {"Pos from"}),
    nullPos2 = #table({"Pos from"}, {{0}}) & nullPos,
    adIndex = Table.AddIndexColumn(nullPos2, "Index", 0, 1, Int64.Type),
    count = Table.AddColumn(adIndex, "Count", each try adIndex{[Index]}[Pos from] - adIndex{[Index] -1}[Pos from] otherwise adIndex{[Index] +1}[Pos from] -1)
  ][count],
    v1_SeparateTables = List.Accumulate( 
    Table.ToRecords(v1_ItemNullPositions),
    {},
    (s,c)=> s & { Table.Range(v1_ReplacedValue, c[Pos from], c[Count]) }
    ),
    v2_StepBack = Source,
    v2_ReplacedValue = Table.ReplaceValue(v2_StepBack,"",null,Replacer.ReplaceValue,{"Item", "Category"}),
    v2_Ad_GroupId = [
    l_item = List.Buffer(v2_ReplacedValue[Item]),
    lg = List.Generate(
            ()=> [ x = 0, y = if l_item{0} = null then null else 1 ],
            each [x] < List.Count(l_item),
            each [ x = [x]+1, y = if l_item{x} = null then [y]+1 else [y] ],
            each [y]
    ),
    combined = Table.SelectRows(Table.FromColumns(Table.ToColumns(v2_ReplacedValue) & {lg}, Table.ColumnNames(v2_ReplacedValue) & {"Group Id"}), each [Item] <> null)
][combined],
    #"v2_Grouped Rows" = Table.Group(v2_Ad_GroupId, {"Group Id"}, {{"All", each _, type table}}),
    v2_All = #"v2_Grouped Rows"[All]
in
    v2_All