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
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!
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_recordsThank 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.