EP009 同一工作簿下的多个工作表的追加查询合并-小微之家 | 汪斌带你开公司

这种情况有3个核心关键需要搞清楚

① 逻辑健壮性：通过筛选防止查询的循环引用
② 路径健壮性：通过动态路径实现查询的可移植性
③ 结构健壮性：通过动态列名实现查询的可扩展性

主题①：逻辑健壮性 — 通过筛选防止查询的循环引用

1. 痛点呈现

在我们使用Power Query处理“数据源与结果在同一工作簿”的场景时，一个极其危险的“幽灵”常常不请自来。

现象：您第一次刷新查询，得到了6行正确的汇总数据。但是，当您第二次、第三次刷新时，数据量会莫名其妙地变成12行、24行，甚至更多。每一次刷新，都会让数据成倍增长，最终导致整个报表的数据完全错误，我们称之为“数据爆炸”或“循环引用”。

复现场景：假设我们已经通过动态路径连接到了当前工作簿，并展开了所有工作表的数据。此时，我们的数据看起来像这样（简化版）：

Name	员工姓名	销售额
Q1_Sales	张三	5000
Q1_Sales	李四	3500
Q2_Sales	王五	8000
…	…	…
追加1	张三	5000
追加1	李四	3500
追加1	王五	8000

会发现，Power Query不仅读取了原始的Q1_Sales、Q2_Sales等工作表，还把它自己上次加载回去的那个结果表（这里假设名为追加1）也当作了新的源数据，一起给合并进来了。

2. 原因剖析

这个问题的根源在于我们使用的Excel.Workbook()函数。

Excel.Workbook()的工作机制：这个函数会忠实地扫描并列出指定Excel文件中所有的对象，包括所有的工作表(Sheets)、已定义的表格(Tables)和已定义名称(Defined Names)。
循环的产生：
1. 我们的查询指令是：“请读取当前这个文件里所有的工作表。”
2. 第一次运行时，它正确地读取了Q1_Sales, Q2_Sales… 并将结果加载到了一个新的工作表，比如追加1.
3. 第二次刷新时，Power Query再次执行第一条指令。但这一次，当它扫描“当前这个文件”时，它发现工作表的列表里，除了原始的Q1_Sales等，还多了一个我们上次创建的追加1。
4. 由于我们没有告诉它要排除追加1，它便忠实地将追加1也作为原材料，和Q1_Sales等一起进行了合并。
5. 这就形成了一个恶性循环：本是追加查询的结果，却又成为了下一次追加查询的源头数据之一。

3. 解决方案

解决方案的核心思想非常直接：我们必须在数据处理的早期阶段，就通过“筛选”操作，明确地告诉Power Query，哪些是“原材料”，哪些是我们自己创造的“成品”，绝对不能混淆、重复提取。

操作步骤：

定位到关键步骤：在Power Query编辑器的“应用的步骤”列表中，找到并选中“源 (Source)”这一步（或者是紧接着它的某一步，只要能看到所有工作表/表格的列表即可）。
应用筛选规则：
- 此时，您的预览窗口应该有一个名为Name的列，其中列出了Q1_Sales, Q2_Sales, Config, 追加1等所有对象的名称。
- 点击 Name 列的筛选下拉箭头 ▼。
- 在弹出的列表中，取消勾选那个代表您最终汇总结果的工作表名称（追加1），以及其他任何不属于原始数据的工作表（如Config）。
- 点击“确定”。
代码层面的变化：本阶段全程可以通过界面操作完成，代码变化可以直接忽略，参考界面操作步骤即可。

最终效果： 通过添加这一步筛选，我们的查询“配方”步骤里就有了一条明确的指令：“在进行任何合并操作之前，请永远忽略名为追加1和Config这一类的非源数据工作表。” 这样，无论我们刷新多少次，Power Query都只会处理我们指定的、真正的原始数据，无效循环被彻底打破，查询的逻辑健壮性得到了保证。

主题②：路径健壮性 — 通过动态路径实现查询的可移植性

1. 痛点呈现

我们构建自动化报表的初衷，是为了“一劳永逸”。但很多初学者很快就会发现一个令人沮丧的事实：这个“一劳永逸”是有条件的，条件就是——“文件永远不能动！”

现象：您精心制作了一个Power Query自动化报表，它在您的电脑上完美运行。但是，当您遇到以下任何一种情况时，灾难就会发生：

您为了整理桌面，把这个Excel文件从桌面移动到了D:\我的工作\报告文件夹。
您将这个Excel文件通过邮件发送给您的同事。
您将整个项目文件夹上传到了共享网盘。

在以上任何一种情况下，当您或您的同事试图点击“刷新”时，Power Query会立刻“翻脸不认人”，弹出一个冰冷的错误提示，类似如下： DataSource.Error: Could not find file 'C:\Users\你的名字\Desktop\原始数据.xlsx'.

结果：整个自动化流程在第一步就宣告失败。您不得不进入每一个查询，手动修改文件路径，极大地削减了自动化的价值，也让文件协作变得异常困难。

2. 原因剖析

这个问题的根源在于，标准的“获取数据”方法，在Power Query中记录的是一个写死的、绝对的路径。

绝对路径 (Absolute Path)：这是一个从电脑的“根目录”（如C:\）开始的、完整的、独一无二的文件地址。它就像一个完整的家庭住址，包含了国家、省、市、街道、门牌号。
Power Query的默认行为：当您通过 获取数据 -> 从文件 连接到一个Excel工作簿时，Power Query的“源”步骤会忠实地记录下这个绝对路径。其M语言代码如下所示，路径部分是一个纯文本字符串： = Excel.Workbook(File.Contents("C:\Users\你的名字\Desktop\原始数据.xlsx"), null, true)
问题所在：这个“地址”是硬编码 (Hard-coded) 的。Power Query就像一个只会按固定地址送信的“笨邮差”，它不知道文件已经“搬家”了。当文件位置发生任何变化，这个写死的地址就变成了一个无效地址，导致“查无此件”。

3. 解决方案

解决方案的核心思想，是把Power Query从一个“笨邮差”升级为一个“智能侦探”。我们不再给它一个写死的地址，而是赋予它一种“随时找到自己在哪”的能力。

这需要Excel和Power Query的协同作战。

第一步：在Excel中创建一个“动态路径信标”

我们首先需要在Excel文件内部，创建一个能实时报告自己位置的“信标”。

新建配置工作表：在您的Excel工作簿中，新建一个工作表，命名为Config（或“路径”等您自己能看懂的名字）。
使用CELL函数：在Config工作表的任意单元格（比如A10），输入这个Excel函数： =CELL("filename", A10) 这个函数会立即返回当前工作簿、工作表的完整文件路径，例如：C:\我的工作\报告\[我的分析文件.xlsx]Config。这个路径是动态的，不管把文件移到哪里，它都会自动更新成新的路径。但是他是取到工作表级别的路径的，我们只需要取到工作簿路径即可。按下面的函数继续取数。=SUBSTITUTE(LEFT(CELL("filename",A10),FIND("]",CELL("filename",A10))-1),"[","")这样就能只取到工作簿所在的路径了。
定义名称：选中A10单元格，点击顶部 “公式” -> “定义名称”。在“名称”输入框中，给它起一个简洁、有意义的名字，比如 工作簿路径。点击“确定”。 （现在，我们就在Excel内部，创建了一个名为工作簿路径的、随时可以引用的、代表当前文件路径的“全局变量”。）

第二步：在Power Query中引用这个“信标”

现在，我们回到Power Query，改造我们的“源”步骤。

定位到“源”步骤：进入Power Query编辑器，选中需要修改的查询，并点击第一步“源”。
修改M代码：看向顶部的公式栏，找到那段包含绝对路径的代码。我们的目标，是用一段函数A即：Excel.CurrentWorkbook(){[Name="工作簿路径"]}[Content]{0}[Column1]能够读取Excel已定义名称的M代码，来替换掉那个写死的路径字符串。
修改前是: Source = Excel.Workbook(File.Contents("C:\Users\你的名字\Desktop\原始数据.xlsx"), null, true)
修改后 (针对数据源就是当前文件本身): Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="工作簿路径"]}[Content]{0}[Column1]), true, true)
用函数A来替换，上面第3步中的绝对路径（含引号，绿色部分）
- 代码解读（核心部分）:
  - Excel.CurrentWorkbook(){[Name="工作簿路径"]}[Content]{0}[Column1]：这段代码的作用是：“在当前这个Excel工作簿里，找到名叫工作簿路径的对象名称，并把它的值（那个长长的，根据数据源所在变动路径实时更新的完整路径文本）取出来。”

最终效果： 经过改造后，“源”步骤的指令从“去这个写死的地址找文件”，变成了“先问问Excel工作簿里的那个名为工作簿路径信标，即一个名称对象，当前数据源文件在哪里，然后再去那个地址找文件”。

这样，无论您将这个Excel文件移动到任何地方，或者发给任何人，只要他一打开文件，CELL函数就会自动更新路径，“信标”就会指向正确的位置。当他点击“刷新”时，Power Query就会根据“信标”的最新指示，准确无误地找到数据源，从而实现了查询的完全可移植性和路径健壮性。

主题③：结构健壮性 — 通过动态列名实现查询的可扩展性

1. 痛点呈现

我们已经构建了一个逻辑正确、路径稳健的查询。它现在看起来似乎“完美”了。但是，一个更隐蔽的“定时炸弹”已经埋下。

现象：您的自动化报表已经稳定运行了好几个月。突然有一天，业务部门通知您：“从这个季度开始，我们的源数据表中增加了一个新的指标列，比如‘销售成本’，请把它也加到您的汇总报表里。”

您自信满满地把包含了新列的源数据文件放到指定位置，然后回到您的Power Query报表，点击“刷新”。刷新过程很顺利，没有报任何错误。但是，当您检查结果时，却沮丧地发现——那个新的“销售成本”列，根本没有出现！

结果：您的自动化流程“选择性失明”了。它无法自动适应源数据结构的变化，您不得不再次进入Power Query编辑器，手动修改查询，这让“一劳永逸”的承诺大打折扣。

2. 原因剖析

这个问题的根源在于Power Query中 “展开列 (Expand Column)” 这个步骤的默认行为。

“展开”操作的背后：当您使用 Excel.Workbook() 函数获取数据后，会得到一个包含[Data]列的元数据表，其中每一行的[Data]都是一个嵌套的表格。为了将它们合并，您会点击[Data]列的展开按钮。

图片[1]-EP009 同一工作簿下的多个工作表的追加查询合并-小微之家 | 汪斌带你开公司 | 老汪洞察

默认行为：写死列名：在您点击展开按钮时，Power Query会弹出一个菜单，让您选择要展开的列。当您点击“确定”后，Power Query会在后台生成一步M代码。这个代码会将您当前所选的所有列的名称，以一个固定的文本列表形式，硬编码 (Hard-coded) 在步骤中。这个M代码通常看起来像这样： = Table.ExpandTableColumn( 上一步的结果, "Data", {"员工姓名", "销售区域", "产品ID", "销售额"} // <-- 问题就在这里！ )
问题所在：这个步骤的“配方”被明确地定义为：“请展开Data列，并且只提取出名为员工姓名, 销售区域, 产品ID, 销售额的这几列。”
“定时炸弹”引爆：当您的源数据增加了新的“销售成本”列后，刷新时，Power Query引擎依然会严格按照这份“旧配方”去执行。由于“销售成本”这个名字不在那份写死的列表中，所以它就被无情地、静默地忽略了。

3. 解决方案

解决方案的核心思想，是把那份“写死的列名清单”，替换成一个“动态生成的、能自动更新的列名清单”。我们不再告诉Power Query具体要展开哪些列，而是赋予它一种能力，让它在每次刷新时，自己去侦察并汇总所有应该被展开的列。

这需要我们手动介入，用M语言构建一个动态列表。

操作步骤：

定位到关键步骤之前：在Power Query编辑器的“应用的步骤”列表中，找到并选中“展开列”(Expanded Data或类似名称)这一步的“上一步”。通常是您筛选完需要的工作表之后的那一步（比如在删除的其他列之后）。
- 此时，您的预览窗口应该是一个元数据表，[Data]列里包含了所有需要被合并的嵌套表格。
插入步骤，创建动态列名列表：点击公式栏左侧的 fx 按钮，插入一个新步骤。在应用步骤列表中选中插入的新步骤，将公式栏中的内容，替换为下面这段“魔法代码”：= List.Distinct(List.Combine(List.Transform(筛选后的上一步的步骤名[Data], each Table.ColumnNames(_))))
- 代码解读：
  1. List.Transform(..., each Table.ColumnNames(_)): 遍历每一个嵌套表([Data])，并提取出它们各自的列名列表。
  2. List.Combine(...): 将所有这些单独的列名列表，合并成一个包含所有列名（有重复）的大列表。
  3. List.Distinct(...): 对这个大列表进行去重，得到一个最终的、包含了所有源表中出现过的全部唯一列名的“超级列表”。
- 按下回车后，这个步骤的结果就是那个动态生成的列名列表。将这个步骤重命名为一个有意义的名字，比如 列标题，是动态变动的。
改造“展开列”步骤：
- 现在，重新选中您原来的“展开列”那一步。看向顶部的公式栏，找到那段写死的列名列表 {"员工姓名", "销售区域", ...}。执行关键替换：将这个写死的列表，直接替换为您上一步创建的动态列表的名称 列标题。
修改前: = Table.ExpandTableColumn(筛选后的上一步的步骤名, "Data", {"员工姓名", "销售区域", ...})
修改后: = Table.ExpandTableColumn(筛选后的上一步的步骤名, "Data", 列标题)