Forum Discussion

JanRak's avatar
JanRak
Helper I
1 year ago
Solved

GroupBy with Fill up/down - a complex case

Hello Community

I'm stuck with the following.
Let's say this is my input data:

Quarter No  Desc Index
1 
2 
3 
4 
1 
22
32
4 
1 
2 
3 
4 
 1 
 2 
 3 
 4 
 1 
 22
 3 
 45
 1 
 2 
 3 
 4 


Expected Result:

 

Quarter No  Desc Index
1 
2 
3 
4 
1 
22
32
4 
1 
22
32
4 
 1 
 22
 32
 4 
 1 
 22
 3 
 45
 1 
 22
 3 
 45


We can observe that the Desc Index is filled down based on the corresponding Quarters. However, when a change occurs in any of the four Quarters, it interrupts the continuation of previous Desc Index values. The new Desc associated with the changed Quarters overwrites the previous values and is then filled down for subsequent entries.

Any help would be much appreciated! 🙏

Need Help
  • jgeddes's avatar
    jgeddes
    1 year ago

    Here is a different approach. It is a bit clunky but it may work for you.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVKK1YlWMoIxjGEMExgDRY0RXJERHlWkmQRXZEqcSbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, Desc = _t]),
    set_types = Table.TransformColumnTypes(Source,{{"Quarter", Int64.Type}, {"Desc", Int64.Type}}),
    add_index = Table.AddIndexColumn(set_types, "Index", 0, 1, Int64.Type),
    add_group = Table.AddColumn(add_index, "Group", each Number.IntegerDivide([Index],4), Int64.Type),
    group_rows = Table.Group(add_group, {"Group"}, {{"AllRows", each Table.ToRecords(Table.SelectColumns(_, {"Quarter", "Desc"})), type table [Quarter=nullable number, Desc=nullable number, Index=number, Group=number]}, {"Sum", each List.Sum([Desc]), type nullable number}}),
    replace_sum = Table.ReplaceValue(group_rows, each [Sum], each if [Sum] <> null then [AllRows] else null, Replacer.ReplaceValue, {"Sum"}),
    fill_down = Table.FillDown(replace_sum, {"Sum"}),
    replace_null_rows = Table.ReplaceValue(fill_down,each [Sum],each if [Sum] = null then [AllRows] else [Sum],Replacer.ReplaceValue,{"Sum"}),
    remove_columns = Table.RemoveColumns(replace_null_rows,{"Group", "AllRows"}),
    expand_list = Table.ExpandListColumn(remove_columns, "Sum"),
    expand_records = Table.ExpandRecordColumn(expand_list, "Sum", {"Quarter", "Desc"}, {"Quarter", "Desc"})
in
    expand_records

5 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    1 year ago

    Something like this might work for you...

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVKK1YlWMgIyjMAsYzjLBCaJogquCI8aI0xFpsSZFAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, Desc = _t]),
    set_types = Table.TransformColumnTypes(Source,{{"Quarter", Int64.Type}, {"Desc", Int64.Type}}),
    add_index = Table.AddIndexColumn(set_types, "Index", 0, 1, Int64.Type),
    add_group = Table.AddColumn(add_index, "Group", each Number.IntegerDivide([Index], 8), Int64.Type),
    group_rows = Table.Group(add_group, {"Quarter", "Group"}, {{"AllRows", each _, type table [Quarter=nullable number, Desc=nullable number, Index=number, Group=number]}}),
    fill_down_nested = Table.TransformColumns(group_rows, {{"AllRows", each Table.FillDown(_, {"Desc"})}}),
    expand_rows = Table.ExpandTableColumn(fill_down_nested, "AllRows", {"Desc", "Index"}, {"Desc", "Index"}),
    table_sort = Table.Buffer(Table.Sort(expand_rows, "Index")),
    remove_columns = Table.RemoveColumns(table_sort,{"Group", "Index"})
in
    remove_columns

     

     

    • JanRak's avatar
      JanRak
      Helper I
      1 year ago

      Thank you jgeddes. Your solution worked for the original example I've provided (which contained 16 rows). However, the problem is a bit more complex (as in the describtion under the expected table). I've updated the example input and expected output. Probably should have entered more complex example in the first place! Sorry!

      • jgeddes's avatar
        jgeddes
        Super User
        1 year ago

        Here is a different approach. It is a bit clunky but it may work for you.

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVKK1YlWMoIxjGEMExgDRY0RXJERHlWkmQRXZEqcSbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, Desc = _t]),
    set_types = Table.TransformColumnTypes(Source,{{"Quarter", Int64.Type}, {"Desc", Int64.Type}}),
    add_index = Table.AddIndexColumn(set_types, "Index", 0, 1, Int64.Type),
    add_group = Table.AddColumn(add_index, "Group", each Number.IntegerDivide([Index],4), Int64.Type),
    group_rows = Table.Group(add_group, {"Group"}, {{"AllRows", each Table.ToRecords(Table.SelectColumns(_, {"Quarter", "Desc"})), type table [Quarter=nullable number, Desc=nullable number, Index=number, Group=number]}, {"Sum", each List.Sum([Desc]), type nullable number}}),
    replace_sum = Table.ReplaceValue(group_rows, each [Sum], each if [Sum] <> null then [AllRows] else null, Replacer.ReplaceValue, {"Sum"}),
    fill_down = Table.FillDown(replace_sum, {"Sum"}),
    replace_null_rows = Table.ReplaceValue(fill_down,each [Sum],each if [Sum] = null then [AllRows] else [Sum],Replacer.ReplaceValue,{"Sum"}),
    remove_columns = Table.RemoveColumns(replace_null_rows,{"Group", "AllRows"}),
    expand_list = Table.ExpandListColumn(remove_columns, "Sum"),
    expand_records = Table.ExpandRecordColumn(expand_list, "Sum", {"Quarter", "Desc"}, {"Quarter", "Desc"})
in
    expand_records