Power Query功能详解之追加查询

PowerQuery功能详解之追加查询

一、“追加查询”功能完整应用场景

1. 同一工作簿下的多个工作表

• 场景描述：您有一个Excel报告文件，里面包含了一月、二月、三月...等多个工作表，您需要将它们汇总成一张年度总表。

• 实现方式：通过 数据 -> 获取数据 -> 从文件 -> 从工作簿，连接到当前这个工作簿本身。Power Query会列出其中所有的工作表对象，然后您可以将它们全部选中并追加。

• 关键技巧：下面我们会深入探讨，为了让这个查询在文件移动后依然有效，最佳实践是使用动态路径（Excel.CurrentWorkbook()）的方式来实现。

2. 不同工作簿下的多个工作表

• 场景描述：您从不同的人或部门那里，收到了多个独立的Excel文件，比如华东区销售.xlsx、华北区销售.xlsx、华南区销售.xlsx。它们的内部结构完全一样，您需要将它们汇总成全国销售总表。

• 实现方式：
1. 在Power Query中，为每一个Excel文件分别创建一个独立的数据源连接查询（查询_华东, 查询_华北, 查询_华南）。
2. 然后，使用 “将查询追加为新查询” 功能，将这几个独立的查询，合并成一个全新的、总的查询（全国销售总表）。

3. 同一文件夹下的所有文件 - 这是最强大的用法！

• 场景描述：您有一个专门的文件夹，比如“月度销售报告”。每个月，您都会把最新月份的、格式完全一样的Excel或CSV报告文件扔进这个文件夹。您希望有一个报表，能自动地把这个文件夹里所有的报告都汇总起来。

• 实现方式：使用 数据 -> 获取数据 -> 从文件 -> 从文件夹。

• 神奇之处：
• 您只需要设置一次查询。Power Query会创建一个“示例文件转换”的函数。
• 未来，当您向这个文件夹中添加新的文件（比如“四月销售.xlsx”）时，您不需要做任何修改！您只需要在最终的报表中点击一次“刷新”，Power Query会自动发现新文件，并将其内容追加到总表中。
• 同样，如果您删除了或更新了文件夹里的某个文件，刷新后结果也会自动同步。

4. 不同类型的多个文件

• 场景描述：您的销售数据一部分记录在Excel文件里，另一部分记录在CSV文件里，还有一部分记录在文本文档里。但它们的列结构是相似的。

• 实现方式：您可以先分别建立对Excel和CSV的查询，在Power Query内部将它们的列名和数据类型清洗成完全一致的结构，然后再将这两个查询追加在一起。

5. 数据库中的多个表

• 场景描述：在公司的SQL数据库中，由于性能或历史原因，销售数据被分成了Sales_2022、Sales_2023、Sales_2024等多张表。您需要分析近三年的总销售情况。

• 实现方式：在Power Query中，分别连接到这三张数据库表，然后将它们追加成一个查询。

6. 网页或API的分页数据

• 场景描述：您需要从一个网站或API接口获取数据，但这个接口为了防止服务器过载，每次只返回100条记录（即“分页”）。您需要获取全部的几千条数据。

• 实现方式：您可以创建一个函数来获取指定页码的数据，然后调用这个函数获取第1页、第2页、第3页...的数据，并将所有返回的查询结果追加在一起，形成完整的数据集。

总结：追加查询的应用场景

二、同一工作簿下的多个工作表的追加查询

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

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

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

以下视频为以上3项内容的完整讲解：

本视频教案下载地：

以下为文字版和相关的代码资源：

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

1. 痛点呈现

2. 原因剖析

• 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. 解决方案

1. 定位到关键步骤：在Power Query编辑器的“应用的步骤”列表中，找到并选中“源 (Source)”这一步（或者是紧接着它的某一步，只要能看到所有工作表/表格的列表即可）。

2. 应用筛选规则：
• 此时，您的预览窗口应该有一个名为Name的列，其中列出了Q1_Sales, Q2_Sales, Config, 追加1等所有对象的名称。
• 点击 Name 列的筛选下拉箭头 ▼。
• 在弹出的列表中，取消勾选那个代表您最终汇总结果的工作表名称（追加1），以及其他任何不属于原始数据的工作表（如Config）。
• 点击“确定”。

3. 代码层面的变化： 可以直接忽略，参考界面操作步骤即可。

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

1. 痛点呈现

• 您为了整理桌面，把这个Excel文件从桌面移动到了D:\\我的工作\\报告文件夹。

• 您将这个Excel文件通过邮件发送给您的同事。

• 您将整个项目文件夹上传到了共享网络驱动器。

2. 原因剖析

• 绝对路径 (Absolute Path)：这是一个从电脑的“根目录”（如C:\\）开始的、完整的、独一无二的文件地址。它就像一个完整的家庭住址，包含了国家、省、市、街道、门牌号。

• Power Query的默认行为：当您通过 获取数据 -> 从文件 连接到一个Excel工作簿时，Power Query的“源”步骤会忠实地记录下这个绝对路径。
其M语言代码如下所示，路径部分是一个纯文本字符串：

• 问题所在：这个“地址”是硬编码 (Hard-coded) 的。Power Query就像一个只会按固定地址送信的“笨邮差”，它不知道文件已经“搬家”了。当文件位置发生任何变化，这个写死的地址就变成了一个无效地址，导致“查无此件”。

3. 解决方案

1. 新建配置工作表：在您的Excel工作簿中，新建一个工作表，命名为Config（或“路径”等您自己能看懂的名字）。

2. 使用CELL函数：在Config工作表的任意单元格（比如A10），输入这个Excel函数： =CELL("filename", A10) 这个函数会立即返回当前工作簿的完整文件路径，例如：C:\\我的工作\\报告\\[我的分析文件.xlsx]Config。这个路径是动态的，您把文件移到哪里，它就会自动更新成新的路径。但是他是取到工作表级别的路径的，我们只需要取到工作簿路径即可。按下面的函数继续取数。=SUBSTITUTE(LEFT(CELL("filename",A10),FIND("]",CELL("filename",A10))-1),"[","")

3. 定义名称：选中A10单元格，点击顶部 “公式” -> “定义名称”。在“名称”输入框中，给它起一个简洁、有意义的名字，比如 工作簿路径。点击“确定”。
（现在，我们就在Excel内部，创建了一个名为工作簿路径的、随时可以引用的、代表当前文件路径的“全局变量”。）

1. 定位到“源”步骤：进入Power Query编辑器，选中需要修改的查询，并点击第一步“源”。

2. 修改M代码：看向顶部的公式栏，找到那段包含绝对路径的代码。我们的目标，是用一段Excel.CurrentWorkbook(){[Name="工作簿路径"]}[Content]{0}[Column1]能够读取Excel已定义名称的M代码，来替换掉那个写死的路径字符串。
修改前:
修改后 (针对数据源就是当前文件本身):
• 代码解读（核心部分）:
• Excel.CurrentWorkbook(){[Name="工作簿路径"]}[Content]{0}[Column1]：这段代码的作用是：“在当前这个Excel工作簿里，找到名叫工作簿路径的对象，并把它的值（那个长长的路径文本）取出来。”

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

1. 痛点呈现

2. 原因剖析

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

• 默认行为：写死列名：在您点击展开按钮时，Power Query会弹出一个菜单，让您选择要展开的列。当您点击“确定”后，Power Query会在后台生成一步M代码。这个代码会将您当前所选的所有列的名称，以一个固定的文本列表形式，硬编码 (Hard-coded) 在步骤中。
这个M代码通常看起来像这样：

• 问题所在：这个步骤的“配方”被明确地定义为：“请展开Data列，并且只提取出名为员工姓名, 销售区域, 产品ID, 销售额的这几列。”

• “定时炸弹”引爆：当您的源数据增加了新的“销售成本”列后，刷新时，Power Query引擎依然会严格按照这份“旧配方”去执行。由于“销售成本”这个名字不在那份写死的列表中，所以它就被无情地、静默地忽略了。

3. 解决方案

1. 定位到关键步骤之前：在Power Query编辑器的“应用的步骤”列表中，找到并选中“展开列”(Expanded Data或类似名称)这一步的“上一步”。通常是您筛选完需要的工作表之后的那一步（比如在删除的其他列之后）。
• 此时，您的预览窗口应该是一个元数据表，[Data]列里包含了所有需要被合并的嵌套表格。

2. 插入步骤，创建动态列名列表：
• 点击公式栏左侧的 fx 按钮，插入一个新步骤。
• 将公式栏中的内容，替换为下面这段“魔法代码”：
• 代码解读：
1. List.Transform(..., each Table.ColumnNames(_)): 遍历每一个嵌套表([Data])，并提取出它们各自的列名列表。
2. List.Combine(...): 将所有这些单独的列名列表，合并成一个包含所有列名（有重复）的大列表。
3. List.Distinct(...): 对这个大列表进行去重，得到一个最终的、包含了所有源表中出现过的全部唯一列名的“超级列表”。
• 按下回车后，这个步骤的结果就是那个动态生成的列名列表。将这个步骤重命名为一个有意义的名字，比如 列标题，是动态变动的。

3. 改造“展开列”步骤：
• 现在，重新选中您原来的“展开列”那一步。
• 看向顶部的公式栏，找到那段写死的列名列表 {"员工姓名", "销售区域", ...}。
• 执行关键替换：将这个写死的列表，直接替换为您上一步创建的动态列表的名称 列标题。
修改前:
修改后:

1. 刷新时，Power Query首先会执行列标题这一步，它会重新扫描所有最新的源数据，并生成一份包含了所有新旧列名的最新“超级列表”。

2. 紧接着，当执行“展开列”步骤时，它不再依赖于一份过时的“旧清单”，而是直接使用刚刚生成的列标题这个最新的、最全的清单来展开数据。

二、不同工作簿下的多个工作表的追加查询

1. 动态路径问题：如何让你的查询在整个项目文件夹被移动或分享给他人后，依然能“一键刷新”？

2. 动态列问题：当源数据增加了新的列，如何让汇总表自动把它包含进来？

第一部分：基础方法——快速合并，但隐患重重

操作步骤：

1. 创建独立查询：在汇总分析.xlsx中，打开Power Query编辑器。通过 “新建源” -> “文件” -> “Excel工作簿”，分别为工作簿1、工作簿2和工作簿3创建三个独立的查询。

2. 追加为新查询：点击 “主页” -> “追加查询” -> “将查询追加为新查询”。在弹出的对话框中，选择“三个或更多表”，并将刚刚创建的三个查询全部添加进去。

3. 加载数据：点击“关闭并上载”，将最终的汇总查询和三个中间查询都加载到Excel中。

痛点演示：一碰就碎的“绝对路径”

第二部分：终极解决方案——构建“永不迷路”的动态路径查询

步骤一：在Excel中创建“路径配置中心”

1. 在汇总分析.xlsx文件中，新建一个工作表，命名为 配置。这个工作表将成为我们所有动态参数的“控制面板”。

2. 我们需要为每一个数据源文件（工作簿1.xlsx 工作簿2.xlsx 工作簿3.xlsx）都生成一个动态路径。在配置工作表的A列，我们分别填入以下公式：
示例（A1, A2, A3单元格分别填入）：
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)&"工作簿1.xlsx"
=LEFT(CELL("filename",A2),FIND("[",CELL("filename",A2))-1)&"工作簿2.xlsx"
=LEFT(CELL("filename",A3),FIND("[",CELL("filename",A3))-1)&"工作簿3.xlsx"

• 公式解读：这个公式由 CELL("filename"), LEFT, FIND 等函数组合而成。它的作用是先获取当前汇总分析.xlsx文件所在的文件夹路径，然后在其后面拼接上我们已知的数据源文件名（如工作簿1.xlsx），从而动态地构建出数据源的完整路径。

步骤二：为动态路径“命名”

1. 选中包含“工作簿1”路径的单元格（例如A1）。

2. 点击顶部菜单 “公式” -> “定义名称”。

3. 输入名称，例如 工作簿1 ，然后确定。

4. 对其他几个路径单元格重复此操作，分别命名为工作簿2工作簿3。

步骤三：改造Power Query查询——注入“GPS导航”

1. 选中工作簿1的查询，在右侧“应用的步骤”中，点击第一步“源”。

2. 看顶部的公式栏，您会看到一个包含写死路径的M代码。

3. 执行关键替换：将公式中写死的路径文本（"C:\..." 这部分），替换为下面这段能够读取已定义名称的M函数代码：
• 代码示例：
= Excel.CurrentWorkbook(){[Name="工作簿1"]}[Content]{0}[Column1]
用以上这个动态路径获取函数，获取“工作簿1”的实时路径，并且替换原来的被写死的路径。

4. 修改后的完整“源”步骤代码应如下所示：
= Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="工作簿1"]}[Content]{0}[Column1]), null, true)

5. 对工作簿2和工作簿3的查询重复此操作，确保将M代码中的Name="工作簿1"相应地修改为工作簿2和工作簿3。

第三部分：最终验证与总结

• 将整个项目文件夹再次移动到任意一个新的位置。

• 打开汇总分析.xlsx，修改任意一个源文件的数据（增行、增列、改值）。

• 回到汇总分析.xlsx，点击“全部刷新”。

重要注意事项与建议

• 相对位置不变：此方法的前提是，您的汇总分析.xlsx和所有工作簿.xlsx数据源文件，必须始终保持在同一个文件夹内，作为一个整体进行移动和分享。

• 文件名依赖：如果您重命名了某个数据源文件（例如，工作簿1.xlsx改成了第一季度.xlsx），您必须回到配置工作表中，手动更新对应的M函数公式，否则刷新会失败。

• 给用户的建议：
• 如果您的文件位置是固定的，完全可以使用第一部分的基础方法，因为它最简单直接。
• 如果您需要频繁移动、分享文件，或者构建一个需要长期维护的报告，那么强烈建议您花时间学习并使用第二部分的动态路径方法，它将为您节省大量后期维护的时间和精力。

三、同一文件夹下的所有文件 - 这是最强大的用法！

场景设定：汇总月度销售报告

• 文件名和内部工作表名各不相同。

• 不同文件的列名、列数不完全统一。

第一部分：基础搭建 — 从文件夹连接

痛点一：如何排除“自己”，避免循环引用？

1. 在Name列的筛选器中，取消勾选你的汇总文件名（总部分析报告.xlsx）。

2. （最佳实践） 使用文本筛选器，只保留文件名中包含特定关键词（如“销售”）或特定扩展名（如.xlsx）的文件。

第二部分：核心技巧一 — 解包并动态展开列

步骤1：解包Excel文件

• 新列名：例如 UnpackedData

• 自定义列公式：
= Excel.Workbook([Content], true)
• 代码解读：Excel.Workbook()函数读取每个文件的二进制内容([Content])，并返回一个包含其内部所有工作表和表格信息的元数据表。第二个参数true，表示让Power Query尝试自动提升每个工作表的标题行。

痛点二：新增的列，为什么刷新不出来？

1. 选中“解包Excel”那一步（“删除的其他列1”）。

2. 点击公式栏左侧的 fx 按钮，插入一个新步骤。

3. 将这个新步骤的公式替换为：
= List.Distinct(List.Combine(List.Transform( 删除的其他列1[Data], each Table.ColumnNames(_))))
• 代码解读：这段代码会遍历Data列中的每一个嵌套表，提取出它们各自的列名列表，然后将这些列表合并并去重，最终得到一个包含了所有唯一列名的动态列表。

步骤2：使用动态列表展开

• 修改前：

• 修改后：

第三部分：核心技巧二 — 构建永不迷路的动态路径

痛点三：绝对路径的“诅咒”

1. 在Excel中创建信标：
• 在总部分析报告.xlsx中，新建一个名为配置的工作表。
• 在A1单元格，输入以下Excel公式，以获取文件夹路径：
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)
• 选中A1单元格，通过 “公式” -> “定义名称”，将其命名为 汇总文件夹路径。

2. 在Power Query中引用信标：
• 回到Power Query编辑器，选中第一步“源 (Source)”。
• 修改其M代码。
• 修改前：
= Folder.Files("C:\Users\Desktop\月度销售报告")
• 修改后：
= Folder.Files(Excel.CurrentWorkbook(){[Name="汇总文件夹路径"]}[Content]{0}[Column1])



• 代码解读：Excel.CurrentWorkbook()函数会在当前工作簿内部查找名叫汇总文件夹路径的对象，并提取其单元格的值（也就是那个动态的文件夹路径），然后将这个路径传递给Folder.Files函数。

结论：你的自动化工厂已建成！

• 逻辑健壮：能通过筛选，避免循环引用。

• 结构健壮：能通过动态列，自动适应源数据结构的变化。

• 路径健壮：能通过动态路径，实现整个项目的自由移动和分享。

如果想深入学习，可以付费￥129，加入我的知识星球，加我的微信，一对一教学。