使用Power Query自定義生成日期表

01-26

簡單粗暴，先上代碼：

(optional 請輸入開始年份 as number,optional 請輸入結束年份 as number)=>let x = 請輸入開始年份, y = if 請輸入結束年份 = null then 請輸入開始年份 else 請輸入結束年份, 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, "日期序號", each Date.Year([日期])*10000+Date.Month([日期])*100+Date.Day([日期])),{{"日期序號", type number}}), year_id = Table.AddColumn(date_id, "年序號", each Date.Year([日期]), type number), year_name = Table.AddColumn(year_id, "年份名稱", each "Y"&Text.From([年序號])), quarter_id = Table.AddColumn(year_name, "季度序號", each Date.QuarterOfYear([日期]), type number), quarter_name = Table.AddColumn(quarter_id, "季度名稱", each "Q"&Text.From([季度序號])), month_id = Table.AddColumn(quarter_name, "月份序號", each Date.Month([日期]), type number), month_name = Table.AddColumn(month_id, "月份名稱", each "M"&Text.From([月份序號])), week_id = Table.AddColumn(month_name, "周序號", each Date.WeekOfYear([日期]), type number), week_name = Table.AddColumn(week_id, "周名稱", each "w"&Text.From([周序號])), year_quarter_id = Table.AddColumn(week_name, "年季序號", each Date.Year([日期])*10+Date.QuarterOfYear([日期]), type number), year_quarter_name = Table.AddColumn(year_quarter_id, "年季名稱", each "YQ"&Text.From([年季序號])), year_month_id = Table.AddColumn(year_quarter_name, "年月序號", each Date.Year([日期])*100+ Date.Month([日期]), type number), year_month_name = Table.AddColumn(year_month_id, "年月名稱", each "YM"&Text.From([年月序號])), year_week_id = Table.AddColumn(year_month_name, "年周序號", each Date.Year([日期])*100+ Date.WeekOfYear([日期]), type number), #"year_week-name" = Table.AddColumn(year_week_id, "年周名稱", each "YW"&Text.From([年周序號])), day_in_week_id = Table.AddColumn(#"year_week-name", "日序號", each Date.DayOfWeek([日期],0), type number), day_in_week_name = Table.AddColumn(day_in_week_id, "周天名稱", each if [日序號] = 1 then "WD1" elseif [日序號] = 2 then "WD2" elseif [日序號] = 3 then "WD3" elseif [日序號] = 4 then "WD4" elseif [日序號] = 5 then "WD5" elseif [日序號] = 6 then "WD6" else"WD7"), work_day = Table.AddColumn(day_in_week_name , "工作日", each if [日序號] = 6 or [日序號] = 0 then "休息日" else "工作日" )in work_day

使用方法：

1、新建一個空查詢，點擊高級編輯器，將上邊的代碼替換裡邊內容，如下圖：

2、輸入起始日期，點擊調用，如2015、2017：

3、調用後，我們看下我們的日期表生成完畢，即可上傳到PowerPivot做建模分析：

本篇分享目的：讓你多學習一種日期表的餓創建方法，當然，如果您會使用M語言的話，可以將日期表修整成更適合自己分析習慣的格式。

如果你想更直接一些，那麼，直接使用下邊的代碼。

letdate=(optional 請輸入開始年份 as number,optional 請輸入結束年份 as number)=>let x = 請輸入開始年份, y = if 請輸入結束年份 = null then 請輸入開始年份 else 請輸入結束年份, 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, "日期序號", each Date.Year([日期])*10000+Date.Month([日期])*100+Date.Day([日期])),{{"日期序號", type number}}), year_id = Table.AddColumn(date_id, "年序號", each Date.Year([日期]), type number), year_name = Table.AddColumn(year_id, "年份名稱", each "Y"&Text.From([年序號])), quarter_id = Table.AddColumn(year_name, "季度序號", each Date.QuarterOfYear([日期]), type number), quarter_name = Table.AddColumn(quarter_id, "季度名稱", each "Q"&Text.From([季度序號])), month_id = Table.AddColumn(quarter_name, "月份序號", each Date.Month([日期]), type number), month_name = Table.AddColumn(month_id, "月份名稱", each "M"&Text.From([月份序號])), week_id = Table.AddColumn(month_name, "周序號", each Date.WeekOfYear([日期]), type number), week_name = Table.AddColumn(week_id, "周名稱", each "W"&Text.From([周序號])), year_quarter_id = Table.AddColumn(week_name, "年季序號", each Date.Year([日期])*10+Date.QuarterOfYear([日期]), type number), year_quarter_name = Table.AddColumn(year_quarter_id, "年季名稱", each "YQ"&Text.From([年季序號])), year_month_id = Table.AddColumn(year_quarter_name, "年月序號", each Date.Year([日期])*100+ Date.Month([日期]), type number), year_month_name = Table.AddColumn(year_month_id, "年月名稱", each "YM"&Text.From([年月序號])), year_week_id = Table.AddColumn(year_month_name, "年周序號", each Date.Year([日期])*100+ Date.WeekOfYear([日期]), type number), #"year_week-name" = Table.AddColumn(year_week_id, "年周名稱", each "YW"&Text.From([年周序號])), day_in_week_id = Table.AddColumn(#"year_week-name", "日序號", each Date.DayOfWeek([日期],0), type number), day_in_week_name = Table.AddColumn(day_in_week_id, "周天名稱", each if [日序號] = 1 then "WD1" elseif [日序號] = 2 then "WD2" elseif [日序號] = 3 then "WD3" elseif [日序號] = 4 then "WD4" elseif [日序號] = 5 then "WD5" elseif [日序號] = 6 then "WD6" else"WD7"), work_day = Table.AddColumn(day_in_week_name , "工作日", each if [日序號] = 6 or [日序號] = 0 then "休息日" else "工作日" )in work_dayin date(2017,2018)

date(2016,2017) 代表2016-2017年，可以根據自己日期格式進行調整。

最後增加一種 Power BI中新建表的快速日期表的建立方法：

日期表 = ADDCOLUMNS (CALENDAR ( date(2017,1,1),date(2018,12,31) ),"年", YEAR ( [Date] ),"季度", ROUNDUP( MONTH ( [Date] )/3,0 ),"月", MONTH ( [Date] ),"周", WEEKNUM([Date]),"年季度", YEAR ( [Date] ) & "Q" & ROUNDUP( MONTH ( [Date] )/3,0 ) ,"年月", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),"年周", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date] ),"星期幾", WEEKDAY([Date]))

QQ交流群：雷友會-Excel交流學習 198086726