PowerBI - How to pull the last 2 weekdays using PowerQuery

Question

I need help on trying to pull the last 2 weekdays including today (if today is a weekday) using PowerQuery on Power BI.

So far I can pull the last 2 days but need help to exclude the weekends. This is what I have so far.

let
    DateList = {Number.From( DateTime.Date( Date.AddDays(DateTimeZone.LocalNow(),-1) ))..Number.From( DateTime.Date( Date.AddDays(DateTimeZone.LocalNow(),0) ))},
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
    #"Changed Type"

Storax Storax · Accepted Answer · 2021-04-05T19:30:40

Try this

let
    DateList = {Number.From( DateTime.Date( Date.AddDays(DateTimeZone.LocalNow(),-3) ))..Number.From( DateTime.Date( Date.AddDays(DateTimeZone.LocalNow(),0) ))},
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Day of Week" = Table.AddColumn(#"Changed Type", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each [Day of Week] <> 5),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Day of Week] <> 6),
    #"Kept Last Rows" = Table.LastN(#"Filtered Rows1", 2),
    #"Removed Columns" = Table.RemoveColumns(#"Kept Last Rows",{"Day of Week"})
in
    #"Removed Columns"

This removes Saturday and Sunday from the list and keeps the last two days without these days.

Update1: Another approach but I think you need to have four days for that

let    
    //d1 = Date.FromText("03.04.2021"),
    d1 = DateTimeZone.LocalNow(),
    d2 = Date.AddDays(d1,-1),
    d3 = Date.AddDays(d1,-2),
    d4= Date.AddDays(d1,-3), 
    dlist= {d1,d2,d3,d4},   
    dTable  = Table.FromList(dlist, Splitter.SplitByNothing(),null, null, ExtraValues.Ignore),
    changeType = Table.TransformColumnTypes(dTable,{{"Column1", type date}}),
    addWeekday = Table.AddColumn(changeType, "Weekday", each Date.DayOfWeek([Column1]), Int64.Type),
    filter = Table.SelectRows(addWeekday, each [Weekday] <> 5 and [Weekday] <> 6),
    keepRows = Table.FirstN(filter,2),
    removeColumns = Table.RemoveColumns(keepRows,{"Weekday"})
in
    removeColumns

Update 2: Another different approach would be to use if condions

let    
    // td = Date.FromText("03.04.2021"),
    td = DateTime.Date(DateTime.LocalNow()),
    d1= if Date.DayOfWeek(td) = 6 then Date.AddDays(td,-2) else if Date.DayOfWeek(td) = 5 then Date.AddDays(td,-1) else td,    
    d2 = if Date.DayOfWeek(d1) = 0 then  Date.AddDays(d1,-3) else Date.AddDays(d1,-1),
    dList = {d1,d2}
in
    dList

PowerBI - How to pull the last 2 weekdays using PowerQuery

1 Answers