Forum Discussion

Patryk_PL_92's avatar
Patryk_PL_92
Helper I
3 years ago
Solved

Formula to get the Workday number in Power Query

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 fo...
Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    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

Patryk_PL_92's avatar
Patryk_PL_92
Helper I
3 years ago

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