db2日期加一天_Power BI Query(M语言)案例 - 生成日期表

需求:生成某一年的所有日期列表,并新增列将每个日期对应的周,月,季度,年份,年月等逐一列出。

解题思路： 假设生成2019年的所有日子，那么第一天是2019-01-01，最后一天是2019-12-31，于是有了初始的列表{"2019-01-01", "2019-12-31"}, 接下来要做的就是以2019-01-01为基础,日期+1,+2,+3.....每加一天生成一个日期,直到2019-12-31 就可以得到列表{"2019-01-01", "2019-01-02", "2019-01-03", ......., "2019-12-31"} 得到日期列表后,再对每一个日期通过date相关函数直接得出对应日期的年,季度,月,周等结果

• 定义开始和结束日期

startDate = #date(2019,1,1)endDate = #date(2019,12,31)

创建日期公式#date(year as number, month as number, day as number) as date

创建时间公式#datetime(year as number, month as number, day as number, hour as number, minute as number, second as number) as any

创建带时区时间公式#datetimezone(year as number, month as number, day as number, hour as number, minute as number, second as number, offsetHours as number, offsetMinutes as number) as any

创建间隔时间公式#duration(days as number, hours as number, minutes as number, seconds as number) as duration

• 生成日期序号序列

dateNumberList = {1..Number.From(end_date)-Number.From(begin_date)+1}  // Number.From(end_date)-Number.From(begin_date) 为 43466 - 43830 = 364// 得到的结果是{1,2,3,4,......,365}  

Number.From(value as any, optional