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.
Hi Experts !
I am working on the problem to create a formula in PQ which will output a "workday classification", between WD1 - WD4 only (Mon-Fri only).
On below image I am showing the example for December 2022.
Based on Date column, PQ should show values like "WD1/ WD2/ WD3/ WD4" for first 4 workdays every month.
I do not see any built-in formula for workdays in PQ, probably that requires some customization and writing complex formula 😞
But I hope that some of you have already encountered such problem and can share what approach to take to solve that?? 🙂
Thanks in advance!
Solved! Go to Solution.
Here's one way to do it in the query editor. Add a custom column and put the expression below in the pop-up box.
= let
SOM = Date.StartOfMonth([Date]),
first7 = List.Dates(SOM, 7, #duration(1,0,0,0)),
WDs = List.Select(first7, each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))),
first4WD = List.FirstN(WDs, 4),
listposition = List.PositionOf(first4WD,[Date]),
result = if listposition >= 0 then "WD"&Text.From(listposition+1) else null
in
result
Pat
As a follow-up, just wanted to share that I found solution, but it requires more steps:
1) Create "Period column" - [Year] & [Month no.] (if period lenght is less than 6 then add "0" between Year and Month no.
2) Add column to evaluate is the day a "workday" or not and filter to have only workdays
3) Sort by Date (Ascending)
4) Group by "Period" column
5) Add index column for each Group (this article was helpful here: https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query )
6) Expand tables with index per group column
7) Add column with condition that if index is 1/2/3/4 then do "WD" & [Index], else null
Done 🙂
This does not include holidays unfortunately, but I'll find a way to add that functionality as well
Hi !
thanks for your efforts on that 🙂
This is a good start, but what is needed exactly is to place that classification "WD1" etc. only to first 4 working days in each month, rest of the working days in that month shall be skipped.
like you did for January 2023, those WD1/ WD2/ WD3/ WD4 should be applied only to 2/3/4/5 dayof January.
Sometimes first working day in month may be Friday, then:
1st day = WD1
3rd day = WD2
4th dat = WD3
5th day = WD4
Here's one way to do it in the query editor. Add a custom column and put the expression below in the pop-up box.
= let
SOM = Date.StartOfMonth([Date]),
first7 = List.Dates(SOM, 7, #duration(1,0,0,0)),
WDs = List.Select(first7, each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))),
first4WD = List.FirstN(WDs, 4),
listposition = List.PositionOf(first4WD,[Date]),
result = if listposition >= 0 then "WD"&Text.From(listposition+1) else null
in
result
Pat
Wow 😮
magic...
it works !
Thanks a lot !
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.