Excel Power Query同表行查找问题求助

问题背景 #

需要在Excel Power Query中，针对每条记录匹配同一Provider3个月前的推荐人数，用来计算当月项目在途人数。原始数据示例：

Provider | Month  | Contract month  | Number of referrals | 
50505    | Jul-23 |        10       |          54         |
50505    | Jun-23 |         9       |          34         |
50505    | May-23 |         8       |          21         |
50505    | Apr-23 |         7       |          45         |

尝试过复制表格、拼接Lookup字段后合并查询，但操作报错，下面是可行的解决方法。

解决方法 #

方法1：合并查询法（直观易操作） #

1. 打开Power Query编辑器，加载你的原始数据表，命名为原始数据。
2. 复制一份原始数据，重命名为3个月前数据。
3. 在3个月前数据里，添加自定义列生成匹配标识，公式：

   = [Provider] & Text.From([Contract month])
   

   然后只保留这个新列（命名为匹配标识）和Number of referrals列，把Number of referrals重命名为3个月前推荐人数。
4. 回到原始数据，先加一个自定义列计算3个月前的合同月份：

   = [Contract month] - 3
   

   命名为Contract month 3 months ago。
5. 再给原始数据加一个自定义列，生成和3个月前数据对应的匹配标识：

   = [Provider] & Text.From([Contract month 3 months ago])
   

   命名为当前匹配标识。
6. 选中原始数据，点击菜单栏的合并查询，选择3个月前数据作为合并对象，匹配条件选原始数据[当前匹配标识] = 3个月前数据[匹配标识]，合并类型选左外部（确保无匹配数据时返回null，不丢原始记录）。
7. 展开合并后的列，只勾选3个月前推荐人数即可。

方法2：Lookup函数直接匹配（更简洁） #

如果你的数据已经按Provider和Contract month排序，直接添加自定义列就能搞定，公式：

= Table.Lookup(
    原始数据,
    {"Provider", "Contract month"},
    {[Provider], [Contract month] - 3},
    "Number of referrals"
)

这个函数会直接在原始表中查找对应Provider、Contract month减3的记录，返回对应的推荐人数。

关键注意点 #

• 确保Contract month是数字类型，如果是文本格式，先转成数字：= Number.From([Contract month])
• 如果有些Provider在3个月前没有数据，合并查询会返回null，你可以用Table.ReplaceValue把null替换成0或者其他默认值。

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