Forum Discussion
Use info from another line to create new column value
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 ...
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
Is there a way to do this with M code?
It is M code 😎
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?
- Open Power Query
- Create Blank Query (right click on left panel / new query / other sources / blank query
- Open Advanced Editor
- 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 🙂