Excel Power Query M 常用日期处理

Excel 数据分析里面，Power Query用来处理日期

let

    源 = Excel.CurrentWorkbook(){[Name="表3"]}[Content],

    改为日期类型 = Table.TransformColumnTypes(重命名的列,{{"pt", type date}}),

    添加年 = Table.AddColumn(改为日期类型, "年", each Date.Year([pt])),

    添加月 = Table.AddColumn(添加年, "月", each Date.Month([pt])),

    添加日 = Table.AddColumn(添加月, "日", each Date.Day([pt])),

    添加星期 = Table.AddColumn(添加日, "星期", each Date.DayOfWeek([pt],Day.Monday)+1),

    添加星期范围 = Table.AddColumn(添加星期, "星期范围", each Date.ToText(Date.StartOfWeek([pt],Day.Saturday), "MM.dd-") & Date.ToText(Date.EndOfWeek([pt],Day.Saturday), "MM.dd")),

    添加年_周 = Table.AddColumn(添加星期范围, "年-周", each [年]*100+Date.WeekOfYear([pt],Day.Saturday)),

    添加年_月 = Table.AddColumn(添加年_周, "年-月", each Date.ToText([pt], "yyyy-MM")),

    添加月_日 = Table.AddColumn(添加年_月, "月-日", each Date.ToText([pt], "MM-dd")),

    更改日期的类型 = Table.TransformColumnTypes(添加月_日,{{"年", Int64.Type}, {"月", Int64.Type}, {"日", Int64.Type},  {"星期", Int64.Type}, {"年-月", type text},{"月-日", type text}}),

    排序的行 = Table.Sort(更改日期的类型,{{"年", Order.Ascending}, {"月", Order.Ascending}, {"日", Order.Ascending}})

in

    排序的行