Forum Discussion

Harvey85's avatar
Harvey85
Helper I
2 years ago
Solved

Previous value in Power Query

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

General Comment
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    2 years ago
    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.

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    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's avatar
      Harvey85
      Helper I
      2 years ago

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