Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need some help with some logic in Power Query.
I have a table that has many columns but related to this are [CUSTOMER CODE], [BILL DATE]. The table contains a row for each order for many customer codes.
The field I am trying to create would have the date of the last order for the customer code where the year is less than the current orders bill date year. IE if the current order is today, there was an order yesterday and before that the previous order was 2017, I would want to see the 2017 date in this field.
Solved! Go to Solution.
Hi @e2matt
You could try this:
GroupDates = Table.Group(Table.SelectColumns(PreviousStep, {"CUSTOMER CODE", "BILL DATE"}), {"CUSTOMER CODE"}, {{"Dates", each _[BILL DATE], type list}}),
MergeDatesperCustomer = Table.NestedJoin(PreviousStep, {"CUSTOMER CODE"}, GroupDates, {"CUSTOMER CODE"}, "Data", JoinKind.LeftOuter),
#"Expanded Data" = Table.ExpandTableColumn(MergeDatesperCustomer, "Data", {"Dates"}, {"Dates"}),
AddPreviousOrder = Table.AddColumn (#"Expanded Data", "Previous Order Date in LY", each let bDate = [BILL DATE] in List.Max(List.Select([Dates], each Date.Year(_) < Date.Year(bDate))), type date),
RemoveDateList = Table.RemoveColumns(AddPreviousOrder, {"Dates"})
Which basically first gets all the dates of a Customer Order, merges them back on the table and then computes the desired result, removing the extra column as a last step.
'PreviousStep' is your last step in the code.
Cheers,
Hi @e2matt
You could try this:
GroupDates = Table.Group(Table.SelectColumns(PreviousStep, {"CUSTOMER CODE", "BILL DATE"}), {"CUSTOMER CODE"}, {{"Dates", each _[BILL DATE], type list}}),
MergeDatesperCustomer = Table.NestedJoin(PreviousStep, {"CUSTOMER CODE"}, GroupDates, {"CUSTOMER CODE"}, "Data", JoinKind.LeftOuter),
#"Expanded Data" = Table.ExpandTableColumn(MergeDatesperCustomer, "Data", {"Dates"}, {"Dates"}),
AddPreviousOrder = Table.AddColumn (#"Expanded Data", "Previous Order Date in LY", each let bDate = [BILL DATE] in List.Max(List.Select([Dates], each Date.Year(_) < Date.Year(bDate))), type date),
RemoveDateList = Table.RemoveColumns(AddPreviousOrder, {"Dates"})
Which basically first gets all the dates of a Customer Order, merges them back on the table and then computes the desired result, removing the extra column as a last step.
'PreviousStep' is your last step in the code.
Cheers,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.