Forum Discussion
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...
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 resultPat
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 !