时间戳

首页 > 解决方案 > PowerBI - 如何使用 PowerQuery 提取最近 2 个工作日

powerbi - PowerBI - 如何使用 PowerQuery 提取最近 2 个工作日

问题描述

我需要帮助来尝试使用 Power BI 上的 PowerQuery 提取包括今天在内的最后 2 个工作日（如果今天是工作日）。

到目前为止，我可以拉最后 2 天，但需要帮助排除周末。这就是我到目前为止所拥有的。

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"

标签： powerbipowerquery

解决方案

试试这个

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"

这将从列表中删除周六和周日，并保留最后两天没有这些天。

Update1：​​另一种方法，但我认为您需要四天时间

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

更新 2：另一种不同的方法是使用 if 条件

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

推荐阅读