+ Reply to Thread
Results 1 to 5 of 5

Power Query WORKDAY?

  1. #1
    Registered User
    Join Date
    02-02-2024
    Location
    Glasgow, Scotland
    MS-Off Ver
    365
    Posts
    44

    Power Query WORKDAY?

    Hi

    Is there a Power Query version of the excel formula WORKDAY?

    I need to filter a report based on the previous working day, and I can use 'Day>Yesterday' in the filter options, however, if the report is run on a Monday this will show Sunday when it needs to show the Friday.

    =workday(today(),-1) does this but I can't see a Power Query version.

    Am I just missing it?

    thanks
    Barry

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2504
    Posts
    19,279

    Re: Power Query WORKDAY?

    Using the search term "excel power query workday function" I got some results from the Microsoft Fabric Community that may be helpful although I have not personally tested them.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,505

    Re: Power Query WORKDAY?

    Here a solution

    PHP Code: 
    let
        Source 
    Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        
    cType Table.TransformColumnTypes(Source,{{"Val"Int64.Type}, {"Date"type date}}),
        
    getPrevWorkday Table.SelectRows(cTypeeach [Date] = List.Last(List.Select(List.Dates(Date.AddDays(Date.From(DateTime.FixedLocalNow()) ,-3),3,#duration(1,0,0,0)), (x)=> Date.DayOfWeek(x,1)<5)))
    in
        getPrevWorkday 
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,505

    Re: Power Query WORKDAY?

    Same code but I did break it down a little for better understanding

    PHP Code: 
    let
        Source 
    Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        
    cType Table.TransformColumnTypes(Source,{{"Val"Int64.Type}, {"Date"type date}}),
        
    prevWorkDay 
            
    let 
                dateMinusThree 
    Date.AddDays(Date.From(DateTime.FixedLocalNow()) ,-3),
                
    listThreeNextDays = List.Dates(dateMinusThree,3,#duration(1,0,0,0)),
                
    removeWeekend = List.Select(listThreeNextDays, (x)=> Date.DayOfWeek(x,1)<5),
                
    previousWorkDay = List.Last(removeWeekend)
            
    in
                Table
    .SelectRows(cTypeeach [Date] = previousWorkDay)
    in
        prevWorkDay 
    Last edited by JEC.; 07-27-2024 at 05:07 AM.

  5. #5
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,505

    Re: Power Query WORKDAY?

    Here a shorter version

    PHP Code: 
    let
        Source 
    Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        
    cType Table.TransformColumnTypes(Source,{{"Val"Int64.Type}, {"Date"type date}}),
        
    getPrevWorkDay = (varDate as date) =>
            
    let
                wkDay 
    Date.DayOfWeek(varDate0),
                
    prevWorkDay Date.AddDays(varDate, {-2,-3,-1,-1,-1,-1,-1}{wkDay} )
            
    in
                prevWorkDay
    ,
        
    result Table.SelectRows(cTypeeach [Date] = getPrevWorkDay(Date.From(DateTime.FixedLocalNow())))
    in
        result 

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 10-31-2023, 11:12 AM
  2. Power Query - Feeding dynamic sheet name to Power query
    By egoosen3 in forum Excel General
    Replies: 5
    Last Post: 09-13-2023, 07:41 AM
  3. [SOLVED] [Power Query] How to filter 0 from two columns in power query editor in one step?
    By daliye in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-16-2023, 09:44 PM
  4. Replies: 1
    Last Post: 07-14-2023, 10:15 AM
  5. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  6. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  7. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1