M Code - Refer Previous Row for each Category per Reporting Month Group

Hi Team ,

I was referring to the " Refer to the Previous Row with Subgroups - Goodly PQ Challenge" under " Applied Problem Solving with Power Query/M" by @Melissa on Enterprise DNA.

I have come across a similar problem where I need to refer the previous row of each Projected Month with Subgroups but per Reporting Month group like I have category A1 and B1 under each Reporting Month group i.e., under Sep’22, Oct’22 and Nov’22. Please assist on this problem statement. Thanks

Have enclosed the PBIX file and spreadsheet with 2 Sheet tabs ( i.e., Raw Data and Expected Output with expected columns highlighted in Yellow).

Refer Previous Row for each Category under different Reporting Month.xlsx (25.0 KB)

Refer Previous Row for each Category per Reporting Month Group.pbix (19.2 KB)

Regards
Afzal

Hi @afzalshariff,

Give this a go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZhRTuswFET30m8ixXZiJ5+PbSD2v43XpE2x47njuT8gquOhLUc+Kj8/j3mf0jzFOcbH1+NfeH6ZtymF45Hjh1JSfn47Hn78fkH8eqTC5+MIxsPcrzM8+Naja30ObzxqeJzi5sCTb32pHhHw1beefevFt75hfCiNNv+RRsSDbz36XuslTfJJI+LJt35JI+Krbz371otvfcP4UJr0BqfrdhpYkz7DY2UUNuq7H1kWgX2asqtscuxejijs6tjNjt3i2N0A+/pbHweay2BcLICzYvU4LRbAWbEAzooFnjsrFsBZsQDOigVwViyAs2IBnBUL4KxYxBlYLCKNNk+LRaQRcVYsIg26l4k0Is6KRaQRcVYsIo2Is2IRaWCxiDSwWMSarixEGYU1i0Vk6W5qbAouFtFEYc1iEUEU1iwWUUNhB8UKZuJgsXqcFqvDebF6nBarx2mx+udOi9XjtFg9TovV47RYPU6L1eO0WD1Oi2U7g4tlS6PN82LZ0og4LZYtDbyXbWlEnBbLlkbEabFsaQ48pXheHcsSMjlSV+t9RBHnhr9+y0CeVD2dtl62QfUZRSGVj779pmQKX9dM4ZNzv66awq/O/ezcL879zeD/StfcOd9d6ULZz/3r0gJ4Xbo3jj8qfnelE/DgW4+u9aZ0Al6XTsCTb70unYCvvvXsWy++9Q3jQ2m0+aZ0DmlEPPpea106hzQiXpcuzufFGdatCN78nVC00ejs2i6u7Q3Sr1c70OaAlzl/Do68Od/G7XwXpxB3dqRJ3OuI4o6Gt4Eb403fQnq9gHVW/KmPKLeOyq/O/ezcL879zeCfHu3gU/44cQBnietxmjiAs8QBnCUOPHeWOICzxAGcJQ7gLHEAZ4kDOEscwFniiDMwcUQabZ4mjkgj4ixxRBrULCKNiA8SR7wBYSHaaDRJHJFGo0eJI9rAxBFvrMQRd1CziDsaThOH3aGJI/7ARBB7VJ4mjhik8jRxxCKSuGB28Z2J04fPbdXjt8S1Ynf4PXEjPPjWo2v9nrgRfkvcCE++9VviRvjqW8++dZC4KRHeaNzzzEAb2BVbGxGnkbO10XAeOVsbEU/t+vmfPEGbpom7Yg4qkS2ORrPK2daYlbO9wZWzxTErZ8sDs2XLo+G8clAeXjlbIFwJ2yCV55WzDVJ5XjnbIqNyv/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reporting Month" = _t, Category = _t, #"Projected Month" = _t, #"Network Level" = _t, #"Network level Change of current month vs previous month" = _t, #"Actual Network level Change of current FY vs Previous FY" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Reporting Month", type date}, {"Projected Month", type date}, {"Network Level", Int64.Type}, {"Network level Change of current month vs previous month", Int64.Type}, {"Actual Network level Change of current FY vs Previous FY", Int64.Type}}),
    Groups = Table.Group(ChType, {"Reporting Month", "Category"}, 
        {
            {"t", each 
                [
                    nLvl = [Network Level],
                    c = List.Count( nLvl )-1,
                    Change = List.Transform( {0..c}, each try nLvl{_} - nLvl{_-1} otherwise null ),
                    YoY = List.Transform( {0..c}, each try if Number.Mod(_, 12) =0 then nLvl{_} - nLvl{_-12} else null otherwise 0 ),
                    t = Table.FromColumns( { [Network Level], Change, YoY }, {"Network Level", "Change", "YoY"} )
                ][t]
             }
        }
    ),
    Expand = Table.ExpandTableColumn(Groups, "t", {"Network Level", "Change", "YoY"}, {"Network Level", "Change", "YoY"})
in
    Expand

.
With this result

Here’s your sample file
Refer Previous Row for each Category per Reporting Month Group.pbix (22.6 KB)

Hope this is helpful

1 Like

Hi @Melisa -

Season’s Greetings - Thanks for the quick solution. This is what I was looking for and it will definitely help me in my assignment. :smiling_face:

Regards
Afzal

Hi @Melisa,

In my file I need the Projected Month as 3rd category, when I include this column my previous row column and change column is becoming null.

Please assist, I have attached the file for your reference.

Regards
Afzal
Refer Previous Row for each Category under different Reporting Month.xlsx (27.2 KB)

Refer Previous Row for each Category per Reporting Month Group.pbix (22.9 KB)

Hi @afzalshariff,

Try this.

  1. Use Selection to return a list with the column values
  2. Add a column name
  3. Add it to the expand list
  4. and to the new column name list as well

with this result

I hope this is helpful

1 Like

Hi @Melissa,

Thank you so much Melisa. This is awesome :smiling_face:

Regards
Afzal