PowerBI PowerQuery M - Lag for each group

[danisharoon]

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?

[Vijay_A_Verma · Accepted solution]

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-...

[danisharoon]

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

[Vijay_A_Verma · Accepted solution]

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"

[DataGuru76]

This is a fantastic translation of the SQL LAG() window function into Power Query! Thank you!

[Vijay_A_Verma · Accepted solution]

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-...