Forum Discussion
GroupBy with Fill up/down - a complex case
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?
Glad it worked.
There are a lot of great M videos on YouTube. Goodly comes to mind as one creator that does a lot of M code with lists and records etc.
Also this community has been a great source of learning as well. You can read throught the answers that are provided to see some great examples of what M is capable of.Good luck on your M journey.