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

serpiva64's avatar
serpiva64
Solution Sage
3 years ago

Hi,

if this is what you need

 

 

look at the attached file. 

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution

  • Patryk_PL_92's avatar
    Patryk_PL_92
    Helper I
    3 years ago

    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

     

    • ppm1's avatar
      ppm1
      Solution Sage
      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

        Wow 😮
        magic...

        it works !

         

        Thanks a lot !