如何在Power Query中按自定义学期分组日期并生成带前缀的注册ID

需求概述 #

需要完成两项自动化处理：

• 生成格式为M-员工ID-YYYY/YYYY+1的注册ID，年份由「日期」字段决定
• 按自定义规则给日期分组标记学期：
  • 第一学期：当年9月17日 ~ 次年1月16日
  • 第二学期：次年1月17日 ~ 次年6月16日
  • 第三学期：次年6月17日 ~ 下一年9月16日
  • 每一年9月17日进入新的学年循环

原始数据 #

预期结果 #

实现步骤 #

1. 生成注册ID #

在Power Query编辑器中，确保「日期」为日期类型、「员工ID」为数字/文本类型，然后添加自定义列，输入以下M代码：

= "M-" & Text.From([员工ID]) & "-" & Text.From(Date.Year([日期])) & "/" & Text.From(Date.Year([日期]) + 1)

• 用Text.From()转换数字为文本，避免拼接时出现格式错误
• 直接按M-员工ID-年份/下一年份的格式拼接字符串

2. 标记自定义学期 #

这部分需要先确定日期所属的学年周期，再判断学期：

步骤2.1 添加学年起始年辅助列

添加自定义列，输入代码：

= if Date.Month([日期]) >=9 and Date.Day([日期]) >=17 then Date.Year([日期]) else Date.Year([日期]) -1

• 逻辑：日期在9月17日及之后，学年起始年为当年；否则为上一年，统一学年周期为「起始年9月17日 ~ 起始年+1年9月16日」

步骤2.2 添加学期列

添加自定义列，输入代码：

= let
    学年起始年 = [学年起始年],
    学期1开始 = #date(学年起始年,9,17),
    学期2开始 = #date(学年起始年+1,1,17),
    学期3开始 = #date(学年起始年+1,6,17),
    下一学年开始 = #date(学年起始年+1,9,17)
in
    if [日期] >= 学期1开始 and [日期] < 学期2开始 then "第一学期"
    else if [日期] >= 学期2开始 and [日期] < 学期3开始 then "第二学期"
    else if [日期] >= 学期3开始 and [日期] < 下一学年开始 then "第三学期"
    else null

• 先定义当前学年的各个学期起始点，再通过条件判断把日期归类到对应学期

步骤2.3 清理辅助列（可选）

如果不需要保留「学年起始年」列，右键删除即可。

内容的提问来源于stack exchange，提问作者val