使用方法：在PQ中新建一個【空查詢】，然后在【視圖】打開【高級編輯器】，清除原來的內(nèi)容，輸入以下內(nèi)容，設(shè)置你的起始日期和結(jié)束日期，點完成就ok了，優(yōu)雅方便高效~希望對大家有幫助！

大家也可以在此基礎(chǔ)上調(diào)整代碼，以便獲得自己想要的分析維度。

let

// 設(shè)置起始日期、結(jié)束日期

自定義日期區(qū)間 = {Number.From(#date(2018,1,1)) .. Number.From(#date(2018,12,31))},

轉(zhuǎn)換為表= Table.FromList(自定義日期區(qū)間, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

生成日期列= Table.RenameColumns(轉(zhuǎn)換為表,{{"Column1", "日期"}}),

日期類型 = Table.TransformColumnTypes(生成日期列,{{"日期", type date}}),

整數(shù)日期 = Table.AddColumn(日期類型, "IntDate", each Date.ToText([日期],"yyyyMMdd")),

整數(shù)類型1 = Table.TransformColumnTypes(整數(shù)日期,{{"IntDate", Int64.Type}}),

年份數(shù) = Table.AddColumn(整數(shù)類型1, "YearID", each Date.Year([日期]), type number),

季度數(shù) = Table.AddColumn(年份數(shù), "QuarterID", each Date.QuarterOfYear([日期]), type number),

月份數(shù) = Table.AddColumn(季度數(shù), "MonthID", each Date.Month([日期]), type number),

年月數(shù) = Table.AddColumn(月份數(shù), "yyMMID", each Text.From([YearID]) & Text.PadStart(Text.From([MonthID]),2,"0")),

整數(shù)類型2 = Table.TransformColumnTypes(年月數(shù),{{"yyMMID", Int64.Type}}),

年度周數(shù) = Table.AddColumn(整數(shù)類型2, "WeekOfYearID", each Date.WeekOfYear([日期]), type number),

月度日數(shù) = Table.AddColumn(年度周數(shù), "DayID", each Date.Day([日期]), type number),

周星期數(shù) = Table.AddColumn(月度日數(shù), "DayOfWeekID", each Date.DayOfWeek([日期],1),type number),

年份 = Table.AddColumn(周星期數(shù), "年份", each "Y" & Text.From([YearID]),type text ),

季度 = Table.AddColumn(年份, "季度", each "Q"&Text.From([QuarterID]),type text),

月份= Table.AddColumn(季度, "月份", each Text.From([MonthID]) & "月",type text),

年月S = Table.AddColumn(月份, "年月S", each Date.ToText([日期],"yy-MM"),type text),

年月S格式= Table.ReplaceValue(年月S,"-","'",Replacer.ReplaceText,{"年月S"}),

年月L = Table.AddColumn(年月S格式, "年月L", each Date.ToText([日期],"yyyy年M月"), type text),

年周= Table.AddColumn(年月L, "周", each "W" & Text.From( [WeekOfYearID] ),type text),

月日 = Table.AddColumn(年周, "日", each "D" & Text.From( [DayID] ),type text),

星期 = Table.AddColumn(月日, "星期", each Date.ToText([日期],"ddd"),type text),

調(diào)整列順序= Table.ReorderColumns(星期,{"日期", "年份", "季度", "月份", "日", "星期", "周", "年月L", "年月S", "IntDate", "YearID", "QuarterID", "MonthID", "yyMMID", "WeekOfYearID", "DayID", "DayOfWeekID"})

in

調(diào)整列順序