一、Web免費(fèi)獲取日期/節(jié)假日
api接口：https://www.mxnzp.com/doc/detail?id=1
PowerQuery添加Web查詢吹害，免費(fèi)獲取日期及工作日螟凭、法定節(jié)假日時(shí)間
PowerQuery Web:
https://www.mxnzp.com/api/holiday/list/year/2015?app_id=sxypjdqmgmpppvyr&app_secret=L0tobmFXQ3ppTTY4WDhwMW1jeW5VZz09
二、日期表制作
1它呀、在Excel中制作日期表
在A1：J1中輸入日期表的表頭螺男，然后在A2：J2中分別輸入以下公式：
A2 = 2019-1-1
B2 = DAY(A2)
C2 = MONTH(A2)
D2 = "M"&C2
E2 = LEN(2^C2)
F2 = "Q"&E2
G2 = YEAR(A2)
H2 = G2*100+C2
I2 = G2&F2
J2 = WEEKDAY(A2,2)
然后選中A2：J2，向下公式填充731行纵穿，就可得到從2019到2020年的日期表下隧。
2、使用PowerQuery制作日期表
進(jìn)入PQ編輯器谓媒，新建空查詢淆院，打開高級編輯器，把高級編輯器中的代碼全部刪除句惯，然后把以下代碼輸入到高級編輯器中（（可以在隨書附件中找到此文件）迫筑，
(optional 請輸入開始年份 as number,
optional 請輸入結(jié)束年份 as number)=>
let
x = 請輸入開始年份,
y = if 請輸入結(jié)束年份 = null
then 請輸入開始年份 else 請輸入結(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}}),
data_id=Table.AddColumn(table,"日", each Date.Day([日期]), type number),
month_id = Table.AddColumn(data_id, "月份", each Date.Month([日期])),
month_name=Table.AddColumn(month_id, "月份名稱", each "M"& Text.From(Date.Month([日期]))),
quarter = Table.AddColumn(month_name, "季度", each Date.QuarterOfYear([日期])),
quarter_name = Table.AddColumn(quarter, "季度名稱", each "Q"&Text.From(Date.QuarterOfYear([日期]))),
year_id = Table.AddColumn(quarter_name,"年度", each Date.Year([日期]), type number),
year_month_id = Table.AddColumn(year_id, "年度月份", each Date.Year([日期])*100+ Date.Month([日期]), type number),
year_quarter_id = Table.AddColumn(year_month_id, "年度季度", each Text.From([年度])&[季度名稱]),
day_in_week = Table.AddColumn(year_quarter_id, "周幾", each Number.Mod(Date.DayOfWeek([日期]),7)+1, type number)
in
day_in_week
3、DAX生成日期表
在PowerBI Desktop界面點(diǎn)擊“新建表”宗弯，輸入以下的其中一種DAX表達(dá)式即可。
常用的有以下幾種方式生成日期表：
3.1 利用ADDCOLUMNS與CALENDAR函數(shù)生成日期表
日期表 DAX_1 =
ADDCOLUMNS (
CALENDAR (DATE(2019,1,1), DATE(2020,12,31)),
"日",FORMAT ( [Date], "D" ),
"月份", MONTH([Date]),
"月份名稱", "M" & MONTH([Date]),
"季度", FORMAT ( [Date], "Q" ),
"季度名稱", "Q" & FORMAT ( [Date], "Q" ),
"年度", YEAR ( [Date] ),
"年度月份", FORMAT ( [Date], "YYYYMM" ),
"年度季度", FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" ),
"星期幾", WEEKDAY ( [Date],2 )
)
3.2 利用GENERATE和CALENDAR函數(shù)生成日期表
日期表 DAX_2 =
GENERATE (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2020, 12, 31 ) ),
VAR currentDay = [Date]
VAR day = DAY( [Date] )
VAR month = MONTH( currentDay )
VAR monthname = "M" & MONTH( currentDay )
VAR quarter = QUARTER( currentDay )
VAR quartername = "Q" & QUARTER( currentDay )
VAR year = YEAR ( currentDay )
VAR weekid = WEEKDAY ( currentDay,2)
RETURN ROW (
"日", day,
"月份", month,
"月份名稱",monthname,
"季度",quarter,
"季度名稱",quartername,
"年度", year ,
"年度月份", year*100+month,
"年度季度", year&quartername,
"星期幾", weekid
)
)
3.3 GENERATE與CALENDARAUTO函數(shù)
日期表 DAX_3 =
GENERATE (
CALENDARAUTO(),
VAR currentDay = [Date]
VAR day = DAY( [Date] )
VAR month = MONTH( currentDay )
VAR monthname = "M" & MONTH( currentDay )
VAR quarter = QUARTER( currentDay )
VAR quartername = "Q" & QUARTER( currentDay )
VAR year = YEAR ( currentDay )
VAR weekid = WEEKDAY ( currentDay,2)
RETURN ROW (
"日", day,
"月份", month,
"月份名稱",monthname,
"季度",quarter,
"季度名稱",quartername,
"年度", year ,
"年度月份", year*100+month,
"年度季度", year&quartername,
"星期幾", weekid
)
)
（來源：采悟Power BI商業(yè)數(shù)據(jù)分析）
