Forum Discussion

ERing's avatar
ERing
Icon for Post Partisan rankPost Partisan
3 years ago

Need Help - New column with earliest date for each ID

I need to a way to create the Assigned Date column in Power Query. The goal of the coulmn is to assign the earlist Sale Date for each record with the same Territory_ID. One caveat is that the Assigne...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
3 years ago

Insert this step as next step where #"Changed Type" should be replaced with your previous step in both the places

= Table.AddColumn(#"Changed Type", "Custom", (x)=> if x[Territory_ID]=null then x[Sale Date] else List.Min(Table.SelectRows(#"Changed Type"[[Territory_ID],[Sale Date]], each ([Territory_ID] = x[Territory_ID]))[Sale Date]))
  • ERing's avatar
    ERing
    Icon for Post Partisan rankPost Partisan
    3 years ago

    Thank you for this. Would it be possible to introdude Status as a criteria so that

     

    if Status = New than Assigned Date = Sales Date

    If Status = Existing than Assigned Date = EARLIEST Sale Date for records with same Territory_ID?