Forum Discussion
PowerBI PowerQuery M - Lag for each group
- 4 years ago
Please post your sample data and result expected. Refer to this - How to provide sample data in the Power BI Forum - https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
- 4 years ago
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcknMLKpUKCjKTylNLilW0lHySswDkuZmSrE6WGTdUpOApLEpdlnfxCKQrAl2WccCkKwRRNYptSy1KDE9NUUhpSgzLxths4kpLnmo3Tj1Q2w3w6kfYr+xpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Month = _t, Volume = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Month", type text}, {"Volume", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Volume (lag 1)", each try if #"Added Index"[Category]{[Index]-1}=[Category] then #"Added Index"[Volume]{[Index]-1} else null otherwise null), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"Volume (lag 1)"] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Volume", "Index"}) in #"Removed Columns"
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcknMLKpUKCjKTylNLilW0lHySswDkuZmSrE6WGTdUpOApLEpdlnfxCKQrAl2WccCkKwRRNYptSy1KDE9NUUhpSgzLxths4kpLnmo3Tj1Q2w3w6kfYr+xpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Month = _t, Volume = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Month", type text}, {"Volume", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Volume (lag 1)", each try if #"Added Index"[Category]{[Index]-1}=[Category] then #"Added Index"[Volume]{[Index]-1} else null otherwise null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"Volume (lag 1)"] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Volume", "Index"})
in
#"Removed Columns"
This is a fantastic translation of the SQL LAG() window function into Power Query! Thank you!