Power BI 利用函数动态生成日期表

日期表是万能的维度表，本文介绍一种在Power Query中快速生成日期表的方法，只需要创建一个函数，函数接受三个参数：开始日期，结束日期，财年的开始月份，然后就可以生成一个完整的日期表。

PowerQuery中创建一个空查询，

切入高级模式：

放入以下代码：

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>

  let

    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),

    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),    
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),

    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),

    InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),

    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),

    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),

    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),

    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),

    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date],"MMMM","en-US"), type text),

    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),

    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),

    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])+1),

    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd","en-US"), type text),

    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),

    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),

    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),

    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),

    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),

    InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),

    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear])),

    #"Renamed Columns" = Table.RenameColumns(AddFY,{{"Date", "DateAltkey"}, {"DateInt", "Datekey"}, {"MonthOfYear", "MonthNumber"}, {"DayInWeek", "WeekdayNumber"}, {"DayOfWeekName", "WeekdayName"}}),

    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Datekey", "DateAltkey", "Year", "QuarterOfYear", "MonthNumber", "MonthName", "DayOfMonth", "MonthInCalendar", "QuarterInCalendar", "WeekdayNumber", "WeekdayName", "WeekEnding", "Week Number", "MonthnYear", "QuarternYear", "ShortYear", "FY"})

in

    #"Reordered Columns"

in

    fnDateTable

放入代码后的效果如下：

点击"Done"返回 Power Query，可以看到生成的函数界面如下：

起始日期：2020/7/1

结束日期：2025/6/30

财政月份： 7

点击生成即可生成一个5年的日期表了。

生成的日期表如下：

如果你是英文版的系统，将如下语句：

InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])+1),

改为：

InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date], 0)),

再到生成表中，对WeekdayNumber列进行查找替换，查找0替换为7，这样可以确保星期是正确的，因为有的语言星期是从周日算起，有的是从周一算起。