【实例】PowerQuery的自定义日历

由于数据分析经验不足，我对自定义日历体验不深，但看到一些大牛对自定义日历推崇备至，因此照猫画虎也筹划自己的自定义日历。

一、自定义日历的两个来源

• 一个是本地的自定义日历，比如自建，或直接复制别人做好的日历。比较推荐此方法。
• 另一个是用网上现成的日历，比如这个https://api.datamarket.azure.com/BoyanPenev/DateStream/v1/ 。这个日历需要Azure账号，可以免费申请。也很强大，但是速度有点慢。

我个人推荐自己建一个本地日历，因为这样使用方便快速。

二、建立本地自定义日历

我建立了一个自己需要用到的日历：

 let
    源 = List.Dates(#date(1900,1,1),Duration.Days(Date.From(#date(2020,12,31))-    Date.From(#date(1900,1,1))),#duration(1,0,0,0)),
    转换为表 = Table.FromList(源, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    重命名的列 = Table.RenameColumns(转换为表,{{"Column1", "Date"}}),
    复制的列 = Table.DuplicateColumn(重命名的列, "Date", "Date - 复制"),
    重命名的列1 = Table.RenameColumns(复制的列,{{"Date - 复制", "Year"}}),
    更改的类型2 = Table.TransformColumnTypes(重命名的列1,{{"Date", type date}}),
    获取年份 = Table.TransformColumns(更改的类型2,{{"Year", Date.Year}}),
    月份 = Table.AddColumn(获取年份, "Month", each if Date.Month([Date])<10 then "0"&Text.From(Date.Month([Date])) else Date.Month([Date])),
    更改的类型3 = Table.TransformColumnTypes(月份,{{"Month", type text}}),
    获取日期 = Table.AddColumn(更改的类型3, "Day", each if Date.Day([Date])<10 then "0"&Text.From(Date.Day([Date])) else Date.Day([Date])),
    更改的类型4 = Table.TransformColumnTypes(获取日期,{{"Day", type text}}),
    中文星期名称 = Table.AddColumn(更改的类型4, "DayName", each Date.ToText([Date],"dddd")),
    一年中的第几周 = Table.AddColumn(中文星期名称, "Week", each Date.WeekOfYear([Date])),
    更改的类型 = Table.TransformColumnTypes(一年中的第几周,{{"Date", type date}}),
    更改的类型1 = Table.TransformColumnTypes(更改的类型,{{"Date", type date}, {"Month", type text}, {"Day", type text}}),
    获取星座 = Table.NestedJoin(更改的类型1,{"Month", "Day"},星座,{"月份", "日期"},"NewColumn",JoinKind.LeftOuter),
    星座获取完毕 = Table.ExpandTableColumn(获取星座, "NewColumn", {"星座"}, {"星座"})
in
    星座获取完毕

注意星座是在本地建立了一个日期和星座的对应表，然后引入到PowerQuery，再合并到日历中。没需要可以不用管。

三、使用本地日历

要注意到我自定义的日历是到2020年。其实我们实际使用的时候，往往用不到将来的日历。比如我自己就只用到本年的日期。所以我将日历引入到我的分析模型时，用下面的代码对日历做了筛选：

筛选的行 = Table.SelectRows(更改的类型, each Date.IsInYearToDate([Date])),
筛选的行1 = Table.SelectRows(筛选的行, each [Month] < Number.From(Date.Month(Date.From(DateTimeZone.LocalNow()))))

第一次筛选，只筛选出本年度到今天的日期；第二次筛选，则筛选出本月之前的月份。原因在于：我每月月初需要提交至上月月底的分析报告，所以做了这么一个筛选。如果有不同需求，可以进行不同筛选，目的是获取日历的有效时间范围即可。