Previous value in Power Query

[Harvey85]

Hello,

I have the following table in Power Query:

Key		Camp_ID		Lot		Campaign		Date		Crop
AA-14C-FF-8586-40		AA-14C-FF		SFBA-STDOM-1-0		23		11/17/2023		Soy
BC-15F-JX-8586-45		BC-15F-JX		SFBA-STDOM-2-0		23		06/24/2023		Barley
MC-12K-LC-8586-48		MC-12K-LC		SFBA-STDOM-2-0		23		12/10/2023		Corn
PS-15C-SL-8586-40		PS-15C-SL		SFBA-STDOM-5-0		24		3/3/2024		Soy
RQ-12X-PX-8586-53		RQ-12X-PX		SFBA-STDOM-5-0		24		4/4/2024		Wheat
RD-10D-GD-8586-40		RD-10D-GD		SFBA-STDOM-5-0		25		4/8/2024		Soy

What I need is to add a new column named "Previous" with the previous value from column Crop (depending on the Date). I have many other rows in the table with more Lots, so I should be able to get the previous Crop for each Lot, if it had a Crop in the past to that row.

Have in mind that for each Lot I have a Campaign that is built in time, so I should have in the future values for each Lot like 23-24-25-26, etc, but it depends on the Date to get the previous record for that Lot.

Expected output:

Key

Camp_ID

Lot

Campaign

Date

Crop

Previous

AA-14C-FF-8586-40

AA-14C-FF

SFBA-STDOM-1-0

23

11/17/2023

Soy

BC-15F-JX-8586-45

BC-15F-JX

SFBA-STDOM-2-0

23

06/24/2023

Barley

MC-12K-LC-8586-48

MC-12K-LC

SFBA-STDOM-2-0

23

12/10/2023

Corn

Barkey

PS-15C-SL-8586-40

PS-15C-SL

SFBA-STDOM-5-0

24

3/3/2024

Soy

RQ-12X-PX-8586-53

RQ-12X-PX

SFBA-STDOM-5-0

24

4/4/2024

Wheat

Soy

RD-10D-GD-8586-40

RD-10D-GD

SFBA-STDOM-5-0

25

4/8/2024

Soy

Wheat

Is there a simple way to do this on Power Query? I'm new to Power BI so I'd want to understand what I should do.

Thank you all in advance!!

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc89C4MwEAbg/+LskVxMrKsmpNAqtU2hgjg4CB1KBenSf9/4FRXqciTvkSd3ZenFMSCXoDVEIgqBU89fMns2OonB3NUlA4S+yQJbEAkeCKPDxbRfr/JLL5GAQsOpmChhey7bUmyhaEgYn6mk7l7NqGX2JTtDKictsm2X7WrICNJZk233Hqzc2CkkmHS1pMu2lhgtbktAgl7iqxVvVztAAfm0ouh/cdkuxAmfocezqT8jpQCpgqNazeSyv5QYqGgzU/UD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Camp_ID = _t, Lot = _t, Campaign = _t, Date = _t, Crop = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Camp_ID", type text}, {"Lot", type text}, {"Campaign", Int64.Type}, {"Date", type date}, {"Crop", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Previous", (k)=> try Table.Last(Table.SelectRows(#"Changed Type",each [Lot]=k[Lot] and [Date]<k[Date]))[Crop] otherwise null)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

NOTE:  The code above assumes your data is sorted. If that assumption is wrong you need to bring your own index column.

[Harvey85]

Hello @lbendlin thanks for your reply!

My data isn't really sorted, I just sorted it for the example. How could I bring my own index column?

Thank you!!!

[lbendlin]