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'm looking for a way to add a column in Power Query that can calculate the "Complete by" date using data from 2 different tables.
Context:
The Complete by date should be calculated by using the Category & the Due Date from Table 2 & matching that up with the Check Run schedule in Table 1. Basically, the Complete by date should be 1 business day (M-F) before the next check run (check run is indicated by an X and some categories have multiple check runs in the same week). Special Note: If the Due Date is on the same day as a check run, then the previous check run date will need to be referenced to calculate the Complete by date. The reasoning for this is that check runs are done in the morning so completing the task in the afternoon means the check would be late as it would miss the same day check run.
Column J is what I'm trying to derive in Power Query. Column J currently indicates what I would expect a Power Query formula to return based on the Category and Due Date. I added a pic of a calendar for easier reference. I will give a few examples below that further explain the formula logic I am looking for.
Please note that the current Excel formula (thanks @HansDouwe from Excel Forum) I'm using to calculate the Complete by date (in cell K3) is as follows:
=WORKDAY.INTL(WORKDAY.INTL(I3,-1,CONCAT(MAP(INDEX(B$3:F$7,MATCH(H3,A$3:A$7,0),0),LAMBDA(c,IF(c="X","0","1"))))&"11"),-1,"0000011")
This code works just as I need it to, but I need to be able to do the same thing directly in Power Query.
Since I'm unable to attach the workbook, here is the data from the 2 tables to copy/paste.
Category | Mon | Tue | Wed | Thu | Fri |
A | X | ||||
B | X | ||||
C | X | X | |||
D | X | X | X | ||
E | X | X | X | X | X |
Category | Due Date |
A | 6/8/23 |
A | 6/12/23 |
A | 6/13/23 |
B | 6/6/23 |
B | 6/9/23 |
B | 6/12/23 |
C | 6/5/23 |
C | 6/6/23 |
C | 6/11/23 |
D | 6/12/23 |
D | 6/13/23 |
D | 6/14/23 |
E | 6/8/23 |
E | 6/9/23 |
E | 6/10/23 |
Here are some examples further explaining how the "Complete by" dates need to be derived.
Example 1 (Cell J3):
Due date = 6/8/23. In order to hit this goal, the complete date needs to be 1 business day before the closest check run. Since the closest check run is on Monday (6/5), the complete date needs to be 1 business day prior which is where 6/2/23 comes from.
Example 2 (Cell J3):
This example utilizes the special note above. Due Date = 6/12/23. In order to hit this goal, the complete date needs to be 1 business day before the closest PREVIOUS check run. Since the closest PREVIOUS check run is on Monday (6/5/23), the complete date needs to be 1 business day prior which is where 6/2/23 comes from.
Example 3 (Cell J10):
This example utilizes the special note above. Due Date = 6/6/23. In order to hit this goal, the complete date needs to be 1 business day before the closest PREVIOUS check run. Since the closest PREVIOUS check run is on Thursday (6/1/23), the complete date needs to be 1 business day prior which is where 5/31/23 comes from.
Example 4 (Cell J16):
This example utilizes the special note above. Due Date = 6/9/23. In order to hit this goal, the complete date needs to be 1 business day before the closest PREVIOUS check run. Since the closest PREVIOUS check run is on Thursday (6/8/23), the complete date needs to be 1 business day prior which is where 6/7/23 comes from.
Solved! Go to Solution.
let
due_dates = Excel.CurrentWorkbook(){[Name="due_dates"]}[Content],
check_dates = Excel.CurrentWorkbook(){[Name="check_dates"]}[Content],
bus_days = {-3, -1, -1, -1, -1, -1, -2},
check_rec =
Record.FromList(
List.Transform(Table.ToRows(Table.RemoveColumns(check_dates, "Category")), (x) => List.PositionOf(x, "X", Occurrence.All)),
check_dates[Category]
),
get_completion = (cat as text, due as datetime) =>
[c_dates = Record.Field(check_rec, cat),
gena =
List.Generate(
() => [d = DateTime.Date(due), pass_check = false, next = Date.AddDays(d, bus_days{Date.DayOfWeek(d, Day.Monday)}), first = true],
(x) => not x[pass_check],
(x) =>
[d = x[next],
pass_check = List.Contains(c_dates, Date.DayOfWeek(x[d], Day.Monday)) and not x[first],
next = Date.AddDays(d, bus_days{Date.DayOfWeek(d, Day.Monday)}),
first = false],
(x) => x[next]
),
cdate = List.Last(gena)][cdate],
completion = Table.AddColumn(due_dates, "Complete by", each get_completion([Category], [Due Date]), type date)
in
completion
let
due_dates = Excel.CurrentWorkbook(){[Name="due_dates"]}[Content],
check_dates = Excel.CurrentWorkbook(){[Name="check_dates"]}[Content],
bus_days = {-3, -1, -1, -1, -1, -1, -2},
check_rec =
Record.FromList(
List.Transform(Table.ToRows(Table.RemoveColumns(check_dates, "Category")), (x) => List.PositionOf(x, "X", Occurrence.All)),
check_dates[Category]
),
get_completion = (cat as text, due as datetime) =>
[c_dates = Record.Field(check_rec, cat),
gena =
List.Generate(
() => [d = DateTime.Date(due), pass_check = false, next = Date.AddDays(d, bus_days{Date.DayOfWeek(d, Day.Monday)}), first = true],
(x) => not x[pass_check],
(x) =>
[d = x[next],
pass_check = List.Contains(c_dates, Date.DayOfWeek(x[d], Day.Monday)) and not x[first],
next = Date.AddDays(d, bus_days{Date.DayOfWeek(d, Day.Monday)}),
first = false],
(x) => x[next]
),
cdate = List.Last(gena)][cdate],
completion = Table.AddColumn(due_dates, "Complete by", each get_completion([Category], [Due Date]), type date)
in
completion
Exactly what I was looking for....thank you!
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.