Use info from another line to create new column value

[JonStark]

I am trying to figure out how to add a column that will calculate end date based on the other data in a series.

So in the example below, Bob originally owned asset 1234567 from 1/1/2022 and then it was transferred to Andy on 6/15/2023. This means that Bob owned the asset from 1/1/2022-6/14/2023. Can anyone give me some guidance on how to pull the 6/15/2023 from the start date of Andys ownership?

I have sorted in PQ by asset ID and then Origination date but that is where I am stuck.

Anyone dealt with his previously?

[ronrsnfld]

Since your data is already sorted, you can 

 - Group by Asset ID

 - Within each subgroup, do a custom aggregation that adds a column where the Origination date is shifted up one row

 - Subtract one day from each End Date date

let
    Source = Excel.CurrentWorkbook(){[Name="Table36"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Owner", type text}, {"Number", Int64.Type}, {"Asset ID", Int64.Type}, {"Origination Date", type date}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Asset ID"}, {{"Added End Date", (t)=>
        Table.FromColumns(
            Table.ToColumns(t) &
            {List.RemoveFirstN(t[Origination Date],1) & {null}},
            {"Owner", "Number","Asset ID","Origination Date","End Date"}),
            type table [Owner=nullable text, Number=nullable number, Asset ID=nullable number, 
                    Origination Date=nullable date, End Date=nullable date]}}),
    
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Asset ID"}),
    #"Expanded Added End Date" = Table.ExpandTableColumn(#"Removed Columns", "Added End Date", 
        {"Owner", "Number", "Asset ID", "Origination Date", "End Date"}),
    #"Subtract One" = Table.TransformColumns(#"Expanded Added End Date", {"End Date", each Date.AddDays(_,-1), type date})
in
    #"Subtract One"

[dufoq3]

Hi @JonStark,

Edit 2nd step YourSource = Source (refer to your table):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRMgRhI2MTUzNzEEvfUN/IwMhIKVYnWskxL6USKGaEosJM39AUocQ3MzsVKGiMosQCYogxWEVQYk5BBtQeczNTE2MjEAtkBEiNAViNV34q1B6ECoRLYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Owner Number" = _t, #"Asset ID" = _t, #"Originination Date" = _t]),
    YourSource = Source,
    ChangedTypeLocale = Table.TransformColumnTypes(YourSource,{{"Originination Date", type date}}, "en-US"),
    Ad_EndDate = Table.AddColumn(ChangedTypeLocale, "End Date", each Date.AddDays(Record.FieldOrDefault(Table.SelectRows(Table.Sort(ChangedTypeLocale, {{"Originination Date", Order.Ascending}}), (a)=> 
   a[Asset ID] = [Asset ID] and a[Originination Date] > [Originination Date]){0}?, "Originination Date", null), -1) , type date)
in
    Ad_EndDate

[JonStark]

Is there a way to do this with M code? 

[dufoq3]

It is M code 😎

[JonStark]

OK, i guess i will go back to the drawing board. I cant seem to desipher this to model it into my current dataset. Any tips?

[dufoq3]

1. Open Power Query
2. Create Blank Query (right click on left panel / new query / other sources / blank query
3. Open Advanced Editor
4. Delete Whole code and paste there the one I created

At this moment you are able to see my solution with sample data. Sample data is defined in 1st step called Source. You have your data called with different name = let's say MyData. Edit 2nd step of my query called YourSource (it is = Source. Change it to = MyData) and whoala 🙂