Forum Discussion
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 2 2 3 2 4 1 2 ...
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
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
Thank you so much jgeddes, looks like it works! Hopefully, it will not be too slow as I'm constrained a little bit. Anyways, I was stuck with this problem for a few days, so much appreciated! Also, as it gives the correct output, I will mark the answer as the Accepted Solution.
jgeddes is there any particular learning source that you would recommend for M Language?