Forum Discussion
PowerBI PowerQuery M - Lag for each group
I have a query consisting of 3 columns, category, month, and volume. I would like to determine one month lag of volume for each category. How can this be achieved using PowerBI PowerQuery M?
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
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"
4 Replies
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
Input will be as follows:
Category Month Volume Dairy products Jan 76 Dairy products Feb 35 Dairy products Mar 34 Dairy products Apr 24 Beveraged drinks Jan 45 Beveraged drinks Feb 34 Beveraged drinks Mar 65 Beveraged drinks Apr 39 Output to be as follows:
Category Month Volume (lag 1) Dairy products Feb 76 Dairy products Mar 35 Dairy products Apr 34 Beveraged drinks Feb 45 Beveraged drinks Mar 34 Beveraged drinks Apr 65 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!
