Use info from another line to create new column value

Question

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?

dufoq3 · Answer

Hi&nbsp;JonStark,Edit 2nd step YourSource = Source (refer to your table):&nbsp;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)=&gt; 
   a[Asset ID] = [Asset ID] and a[Originination Date] &gt; [Originination Date]){0}?, "Originination Date", null), -1) , type date)
in
    Ad_EndDate

jonstark · Answer

Is there a way to do this with M code?&nbsp;

ronrsnfld · Answer

Since your data is already sorted, you can&nbsp;&nbsp;- Group by Asset ID&nbsp;- Within each subgroup, do a custom aggregation that adds a column where the Origination date is shifted up one row&nbsp;- Subtract one day from each End Date date&nbsp;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)=&gt;
        Table.FromColumns(
            Table.ToColumns(t) &amp;
            {List.RemoveFirstN(t[Origination Date],1) &amp; {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"&nbsp;