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