利用PowerQuery自定義函數(shù)法創(chuàng)建日歷表

(optional 請(qǐng)輸入開始年份 as number,optional 請(qǐng)輸入結(jié)束年份 as number)=>let
    x = 請(qǐng)輸入開始年份,
    y = if 請(qǐng)輸入結(jié)束年份 = null then 請(qǐng)輸入開始年份 else 請(qǐng)輸入結(jié)束年份,
    begin_date = if x = null then #date(Date.Year(DateTime.LocalNow()),1,1) else #date(x,1,1),
    end_date = if y = null then #date(Date.Year(DateTime.LocalNow()),12,31) else #date(y,12,31),
    list = {1..Number.From(end_date)-Number.From(begin_date)+1},
    dates = List.Transform( list , (item)=> Date.AddDays(begin_date,item-1) ),
    table = Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "日期"}}),{{"日期", type date}}),
    date_id = Table.TransformColumnTypes(Table.AddColumn(table, "日期序號(hào)", each Date.Year([日期])*10000+Date.Month([日期])*100+Date.Day([日期])),{{"日期序號(hào)", type number}}),
    year_id = Table.AddColumn(date_id, "年序號(hào)", each Date.Year([日期]), type number),
    year_name = Table.AddColumn(year_id, "年份名稱", each "Y"&Text.From([年序號(hào)])),
    quarter_id = Table.AddColumn(year_name, "季度序號(hào)", each Date.QuarterOfYear([日期]), type number),
    quarter_name = Table.AddColumn(quarter_id, "季度名稱", each "Q"&Text.From([季度序號(hào)])),
    month_id = Table.AddColumn(quarter_name, "月份序號(hào)", each Date.Month([日期]), type number),
    month_name = Table.AddColumn(month_id, "月份名稱", each "M"&Text.From([月份序號(hào)])),
    week_id = Table.AddColumn(month_name, "周序號(hào)", each Date.WeekOfYear([日期]), type number),
    week_name = Table.AddColumn(week_id, "周名稱", each "W"&Text.From([周序號(hào)])),
    year_quarter_id = Table.AddColumn(week_name, "年季序號(hào)", each Date.Year([日期])*10+Date.QuarterOfYear([日期]), type number),
    year_quarter_name = Table.AddColumn(year_quarter_id, "年季名稱", each "YQ"&Text.From([年季序號(hào)])),
    year_month_id = Table.AddColumn(year_quarter_name, "年月序號(hào)", each Date.Year([日期])*100+ Date.Month([日期]), type number),
    year_month_name = Table.AddColumn(year_month_id, "年月名稱", each "YM"&Text.From([年月序號(hào)])),
    year_week_id = Table.AddColumn(year_month_name, "年周序號(hào)", each Date.Year([日期])*100+ Date.WeekOfYear([日期]), type number),
    #"year_week-name" = Table.AddColumn(year_week_id, "年周名稱", each "YW"&Text.From([年周序號(hào)])),
    day_in_week_id = Table.AddColumn(#"year_week-name", "日序號(hào)", each Date.DayOfWeek([日期],0), type number),
    day_in_week_name = Table.AddColumn(day_in_week_id, "周天名稱", each if [日序號(hào)] = 1 then "WD1" else
if [日序號(hào)] = 2 then "WD2" else
if [日序號(hào)] = 3 then "WD3" else
if [日序號(hào)] = 4 then "WD4" else
if [日序號(hào)] = 5 then "WD5" else
if [日序號(hào)] = 6 then "WD6" else
"WD7"),
    work_day = Table.AddColumn(day_in_week_name , "工作日", each if [日序號(hào)] = 6 or [日序號(hào)] = 0 then "休息日" else "工作日" )
in
    work_day