Forum Discussion
Solved
Max date less than current record date
Hello, I need help with Power Query logic.
I have a table "asset" which contains asset values by account_no and by a calendar date. I need to identify the previous calendar_date prior to the current calendar_date. For example, in the table below if the current calendar_date is 9/26/2022, I need to return the prior date of 9/23/2022. I appreciate any help.
Cool, makes more sense 🙂
You can create two index columns, one starting from 1, the other from 0, and merge the table on itself using [account] & [Index0] = [account] & [Index1].
See working query below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc3LDcAgDAPQXXJGimNKC7Mg9l+DlH44xMcny+5dzCNJUBSnEqSM9Ks1RY2a1Rj0Xlial9LzdHHs7qfeLVH97Qrqo2ivjgk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [account = _t, calendar_date = _t]), addIndex1 = Table.AddIndexColumn(Source, "Index1", 1, 1, Int64.Type), addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type), mergeOnSelf = Table.NestedJoin(addIndex0, {"account", "Index0"}, addIndex0, {"account", "Index1"}, "addIndex0", JoinKind.LeftOuter), expandCal_Date = Table.ExpandTableColumn(mergeOnSelf, "addIndex0", {"calendar_date"}, {"calendar_date.1"}), remOthCols = Table.SelectColumns(expandCal_Date,{"account", "calendar_date", "calendar_date.1"}) in remOthColsExample query output:
Pete
