Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
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
remOthCols
Example query output:
Pete
Proud to be a Datanaut!
Hi @shephee ,
The basic logic is MAX(Date) where Date < Current. When you say you want to "return" this date, what do you mean? Do you want to filter it, use it in a calculation, display t in a column etc.?
Pete
Proud to be a Datanaut!
I want to display it in a new column.
Okay, this sounds like an XY Problem.
Are you trying to bring the previous date/value into each row so you can do comparisons/variances between current row and prior?
Pete
Proud to be a Datanaut!
Yes. So what I would end up with is something that looks like this:
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
remOthCols
Example query output:
Pete
Proud to be a Datanaut!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |