1.20 PowerBI数据准备-日期表增加节假日、工作日分析相关列

加入 PowerBI自己学 知识星球：下载源文件，边学边练；遇到问题，提问交流，有问必答。

有些场景会针对工作日或者节假日的数据进行汇总分析，或者对每月第几个工作日平移筛选数据，按工作日计算时间进度，又或者按照每月第几个工作日动态切换日期表中当月的标记等。

解决方案

每个组织的假期安排不尽相同，因此用统一的假期标记日期表不合适，每个组织要有自己维护的一份假期表，然后用假期表合并查询到日期表中，在日期表中生成节假日、节假日类型、是否工作日、每月第几个工作日、年月按第几个工作日标记当前月、工作日_Order。

假期表的格式如下，把全年的假期填写到表里面就可以，每年年末补充下一年的数据。在Excel中把全年的年月日填写上，利用DayofWeek（=WEEKDAY(C579,2)）把周末标记出来，然后对比国家或组织的假期日历，标记工作日的假期和删除调休上班的周末，得到一份假期表。

STEP 1 PowerQuery获取假期表数据后，在日期表中，点击菜单栏主页下的合并查询，用两个表的日期字段做关联，使用左外部连接，然后在生成的新列标题上点击右侧的展开按钮，选择HolidayName和HolidayType。

STEP 2 点击菜单栏添加列下的条件列，新增一列Is_Holiday，标记是否节假日，将数据类型改为整数。

STEP 3点击菜单栏添加列下的自定义列，输入代码，生成每月的累计工作日天数。此处用到了List.Sum求和函数、Table.SelectRows筛选函数、(x)=>自定义函数，筛选上一步骤的表，要求年月与当前行的年月相等，日期小于等于当前行的日期，然后将数据类型改为整数。

List.Sum(Table.SelectRows(#"Changed Type",(x)=>x[YearMonth]=[YearMonth] and x[Date]<=[Date])[Is_Workday])

STEP 4 以按照每月第6个工作日动态标记当前月为例。点击菜单栏主页下的新建源，选择空查询，输入代码，生成一个随刷新变化的当天日期。然后在日期表中，新建自定义列，输入代码，生成按照每月第6个工作日动态标记当前月的年月列。

当天：

Date.From(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),8))

年月标记列：

if

List.Min(Table.SelectRows(#"Changed Type1",(x)=>x[Date]=Parameter_Today)[WorkdayOfMonth])>=6 and

[YearMonth]= Number.FromText(Date.ToText(Parameter_Today,"yyyyMM"))

then "当月"

else if

List.Min(Table.SelectRows(#"Changed Type1",(x)=>x[Date]=Parameter_Today)[WorkdayOfMonth])<=5 and

[YearMonth]=

Number.FromText(Date.ToText(Date.AddMonths(Parameter_Today,-1),"yyyyMM"))

then "当月"

else [YearMonth]

STEP 5 点击菜单栏添加列下的索引列，生成索引列后，在fx公式栏中将起步数字从0改为1，双击列标题重命名为Date_Order，用作工作日序号累加的辅助列。

STEP 6 点击菜单栏添加列下的自定义列，输入代码，生成工作日序号列Workday_Order，用于工作日的平移、计算工作日间隔等。

List.Sum(List.Range(#"Changed Type"[Is_Workday],0,[Date_Order]))

日期表增加节假日、工作日分析相关列后，如下：