GroupBy with Fill up/down - a complex case

[JanRak]

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
3
4
1
2
3
4
1
2	2
3
4	5
1
2
3
4

Expected Result:

Quarter No	Desc Index
1
2
3
4
1
2	2
3	2
4
1
2	2
3	2
4
1
2	2
3	2
4
1
2	2
3
4	5
1
2	2
3
4	5

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! 🙏

[jgeddes]

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]

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 · Accepted solution]

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

[JanRak]

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?

[jgeddes]

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.