Power Query精通指南3：数据库（查询折叠与数据隐私）、批量合并文件、自定义函数

全文参考《精通 Power Query》， 点此下载本文所有示例文件。

九、批量合并文件

9.1 案例背景

  本章以一家制造公司的季度零件需求数据为例。该公司每个生产区域每季度会提交一份以其区域命名的数据报告，这些报告存储在一个文件夹中，每个工作簿包含一个名为 “Forecast” 的工作表（只是一个区域），数据格式为透视表结构。

我们的目标是创建一个可刷新的自动化解决方案，将这些数据合并为一个规范化的表格。

这里面的难点在于：

1. 这些文件都存储在"第 09 章 示例文件/Source Files"文件夹的子文件夹中，每个文件的内容需要【逆透视】才能被【追加】
2. 不同的区域会生产不同的产品，所以文件的列数也不相同。
3. 文件名中的区域名称（East、North…）以及子文件夹名称中日期（例如"2019 Q4"）必须被保留

9.2 合并文件的标准流程

  使用 Power Query 合并文件，标准模式可简单概括为：先对一个文件进行清洗，然后使用同样的方式对所有文件进行清洗，其标准流程为：

1. 连接到文件夹 ：选择合适的连接器，连接到存放数据文件的文件夹；
2. 筛选文件 ：根据文件类型、名称等条件，筛选出需要合并的文件；
3. 合并文件 ：选择一个示例文件，启动合并文件的过程。启动之后，会自动生成"帮助程序查询"文件夹，此文件夹下包含"示例文件参数"、“示例文件”、“转换示例文件”、"转换示例文件函数"四个查询。
4. 对示例文件进行数据清洗 ：在转换示例文件查询中，对一个文件进行数据清洗，清洗步骤会自动封装到转换文件函数中，主查询会自动调用此函数，对每个文件都执行相同的清洗步骤；
5. 对主查询进行数据清洗 ：文件清洗完毕后，对合并的总表数据进行必要的清洗

9.3 示例：合并文件

9.3.1 连接到文件夹

每次连接到一个数据源时，Power Query 都要经历四个步骤：

• 设置：选择和配置需要使用的连接器，来连接到相应的文件或文件夹
• 身份验证：Power Query 会检查用户是否需要对数据源进行验证（如果需要，会提示用户进行验证）
• 数据预览：在验证了用户可以访问数据源之后，用户会得到初始预览窗口
• 查询处理：用可以直接加载数据，或者在加载前使用Power Query 编辑器进行数据处理

9.3.1.1 连接到本地 / 网络文件夹

  选择【来自文件】【从文件夹】，浏览并选择包含数据文件的文件夹（“第 09 章 示例文件\Source Data”），点击【打开】，在弹出的预览窗口中，选择【转换数据】按钮来编辑查询。

• Content： 对实际文件内容的引用
• Name,Extension：文件名称与文件扩展名
• Date accessed, Date modified,Date created：文件最后一次被访问的日期、文件最后修改的日期、文件创建的日期
• Attributes：属性，包含文件大小、可见性状态等项的记录
• Folder Path：文件夹的完整路径

9.3.1.2 连接到 SharePoint 文件夹

如果数据存储在 SharePoint 站点中，有两种连接方式：

1. 同步到本地电脑 ：如果将 SharePoint 文件夹同步到本地电脑，可以直接使用本地文件夹连接器进行连接。
2. 云端托管版本 ：如果需要直接连接到云端托管的 SharePoint 文件夹，则需要使用 SharePoint 专用连接器。这种方式的运行速度相对较慢，因为需要在执行查询时下载文件，但不需要将文件存储在本地。
   • 创建一个新的查询，选择【来自文件】【从 SharePoint 文件夹】。
   • 输入 SharePoint 站点的 根目录 URL ，而不是具体的文件夹路径。

  找到根目录：此方式用户不能直接连接到 SharePoint 的子文件夹，必须先连接到根目录，然后逐步导航到目标文件夹。 找到根目录的方法是通过网络浏览器登录 SharePoint 站点，检查 URL。将 “Forms” 左边的第二个 “/” 开始前面的 URL 部分复制到 Power Query 的【站点 URL】输入框中。比如如果域名是 https://monkey.sharepoint.com，则根目录 URL 是https://monkey.sharepoint.com/sites/projects。

• SharePoint文件存储位置：在 SharePoint 中，文件可以存储在不同的层级结构中，通常文件存储在 SharePoint 站点的子文件夹或子站点中，例如 https://yourdomain.sharepoint.com/sites/yourproject/Shared%20Documents；但是也可以直接存储在 SharePoint 域的根目录中，例如 https://yourdomain.sharepoint.com。
• 用户实际上也可以将文件存储在 SharePoint 域的根目录中，此时可以通过输入根目录 URL（如 https://yourdomain.sharepoint.com）来连接这些文件；但这种方式不会递归列出子站点或子文件夹中的文件。如果需要访问子层级中的文件，需要逐步导航到目标文件夹。

   SharePoint 域名格式：如果用户公司使用 Microsoft 365，那么 SharePoint 域名将采用"-my.sharepoint.com"的格式；如果 SharePoint 是由公司内部的 IT 部门管理，则域名可以是其他形式。

   身份验证： 如果以前从未连接过该 SharePoint 站点，会提示用户进行身份验证。

认证方式：

• 如果 SharePoint 托管在 Office 365 上（公司是使用 Office 365 且域名是以 sharepoint.com 结尾的），必须选择 微软账户，并使用 Office 365 的电子邮件登录（输入常规工作电子邮件凭据）；
• 如果 SharePoint 是由 IT 部门托管，可能可以匿名访问。
• 如果匿名访问无效，则需要使用 Windows 凭据 登录。
• 如果输入的 URL 有效，认证成功后Power Query 将展示预览窗口，显示文件夹中的文件。

  凭据管理：凭据会存储在用户电脑上的一个文件中。如果选择错误的凭据，可能会导致无法连接。 如果需要重新更改凭据，则需要清除之前的选择。进入【数据】【获取数据】【数据源设置】【全局权限】，选择【清除权限】（清楚选择），然后在下次尝试连接时重新输入正确的【站点 URL】。

9.3.1.3 连接到 OneDrive for Business

  OneDrive for Business 实际上是一个在 SharePoint 上运行的个人网站，所以其连接方法与连接 SharePoint 文件夹类似。

1. 通过本地同步文件夹连接：如果 OneDrive for Business 的文件夹已经同步到用户的本地桌面，可以直接使用【来自文件】选项连接。
2. 通过 SharePoint 文件夹连接：如果文件夹没有同步到本地，可以使用【来自 SharePoint 文件夹】选项连接。

  连接时的 URL 格式：OneDrive for Business 与 SharePoint【站点URL】不同，所以连接 OneDrive for Business 时，需要输入一个特定格式的 URL：

https://<SharePointDomain>/personal/<email>

• <SharePointDomain>：SharePoint 域名。
• <email>：用户的电子邮件地址，但需要将电子邮件地址中的 . 和 @ 替换为下划线 _。

  获取正确 URL 的最简单方法是通过浏览器登录 OneDrive for Business，并从地址栏中复制 URL。假设用户的电子邮件地址是 john.doe@example.com，SharePoint 域名是 example.sharepoint.com，那么正确的 URL 应该是：

https://example.sharepoint.com/personal/john_doe_example_com

9.3.1.4 连接到其他文件系统

  除了以上常见的连接器，Power Query 还支持其他类型的连接器，例如 Blob Storage、Azure Data Lake Gen 1 和 Azure Data Lake Gen 2；这些连接器也需要通过特定的 URL 进行连接，并且需要进行身份验证。

  还有一些在线文件存储系统，例如Google Drive,DropBox,Box,OneDrive（个人版） 等，即使Power Query 没有直接的连接器支持，也可以通过将文件同步到用户 PC 上，再在本地进行读取。

9.3.2 筛选文件

在连接到数据文件夹后，可以扫描到该文件夹及其所有子文件夹中的所有文件的列表。由于Power Query 一次只能合并一种类型的文件，且区分大小写（.xlsx与.XLSX是不同的），为防止合并错误，在合并前需要进行文件筛选。一种标准操作步骤为：

1. 筛选到适当子文件夹级别；
2. 将扩展名转小写，然后筛选限定为同一种文件类型；
3. 排除临时打开的文件（文件名以 “~” 开头）（多用户电脑或者有些情况下，待合并的文件会被人你打开，合并就会报错）
4. 根据具体业务逻辑筛选指定文件，比如"
   • 筛选出一致的文件结构：比如有混合的文件（如销售报告、财务报表和预算准备文件等），需要筛选出其中一种结构；
   • 可按需对文件名、文件夹、日期等进行筛选
5. 文件列表筛选完毕之后，将此查询重命名为 FilesList 并作为仅连接加载（可选，推荐）。这么做有几个好处：
   • 结构清晰：重命名后，你可以很容易地知道这个查询是用来列出所有文件的，否则的在主查询中寻找哪部分是用于连接筛选文件的；
   • 方便管理：如果文件路径发生变化，只需在 “FilesList” 查询中更新一次
   • 减少内存和处理时间：仅连接加载意味着数据不会立即加载到内存中，而是在需要时才从源中提取，这可以显著减少内存使用。

  在第一步中，对于本地文件夹，可以输入目标文件夹的完整路径，很简单就能定位到特定的子文件夹；对于 SharePoint 或 Azure 等文件系统，这些连接器可能不会直接显示文件夹路径，而是显示整个网站的 URL 加上文件路径。为了只保留所需的子文件夹，可以通过替换文件夹路径中的特定部分来实现。

• 右击"Folder Path"列，选择【替换值】
• 在【要查找的值】中输入<原始文件夹路径或站点 URL>"加上文件夹分隔符；在【替换为】中什么都不写，即清空该字段。
• 点击【确定】，这样就会将原始路径替换为相对路径，只保留子文件夹名称。例如替换"C:\MYD\第 09 章 示例文件\Source Data"为为空，会得到：
  

  如果用户连接的是一个本地文件夹，那么没必要这么做；但如果用户是连接到 SharePoint,OneDrive,Azure ，那么使用这个技巧可以更容易管理和筛选特定子文件夹中的文件。对于更深层的文件路径或有大量文件的场景，用户可能要重复这个过程几次（多次将"当前"文件夹路径替换为空，进入下一个子文件夹级别），以便进入需要的子文件夹。

接着就是按部就班筛选所需文件

• 右击"Extension"列【转换】【小写】
• 选择"Extension"列，使用【文本过滤器】，选择高级，筛选文件扩展名为.xlsx且文件名不含~的文件
  

9.3.3 合并文件

9.3.3.1 合并步骤

1. 引用 FilesList 暂存查询来创建主查询
2. 将主查询重命名为 Orders
   强烈建议用户在进行数据合并操作之前给主查询取一个清晰且描述性强的名字，这样可以帮助用户快速识别这个查询的用途，避免混淆，尤其是在处理多个查询或文件夹时。
3. 点击 Content 列右侧的合并文件按钮（两个向下的箭头），在弹出的预览窗口中选择【第一个文件】作为示例文件，选择 “Forecast” 工作表，点击【确定】。
   

  创建单独的FilesList查询会有一个限制：只能选择列表中的第一个文件作为示例文件。不过如果需要使用其他文件作为样本，可以通过对"FilesList"查询进行排序或筛选，将所需的文件调整到列表的首位就行。

  Power Query 会计算一小段时间，然后生成四个新的查询，并在主查询中添加5个步骤。可以看到此时每个文件的Forecast表内容已经被追加到了一起（每13行为一个文件）：

9.3.3.2 合并多个工作表

  可以看到，此时查询预览仍然停留在主查询上，意味着可以进一步在此处窗口进行合并文件操作。另外，此示例只是合并每个Excel文件中的一个工作表（Forecast表），但是也可以同时合并多个工作表。在【转换示例文件】的"Source"步骤中，已经列出了每个文件的所有工作簿对象：

9.3.3.3 帮助程序查询文件夹中的四个查询

• 参数：二进制文件类型，这里选择的就是示例文件
  
• 示例文件：文件列表中的第一个文件
  
• 转换示例文件：用于对示例文件进行清洗转换
  
• 转换文件：自定义函数。对示例文件进行清洗转换之后，将其封装为一个函数，然后可以对其它同结构的文件进行调用，执行同样的清洗转换步骤

9.3.3.4 尽可能在"转换示例文件"中进行数据清洗

  现在可以对合并后的数据进行清洗转换，可以在转换示例文件中或者主查询中进行。本书鼓励用户尽可能多地在"转换示例文件"中进行数据清洗，因为：
鼓励尽可能多地在"转换示例文件"中进行数据清洗的原因如下：

1. 便于构建查询：使用"转换示例文件"，用户可以根据一个示例文件来构建查询，这使得数据清洗过程更加直观和容易。

2. 避免追加数据集的混乱：在"转换示例文件"中进行数据清洗可以避免在数据被追加之前处理数据集的复杂性。这在进行透视、逆透视或分组等操作时尤为重要，可以显著降低操作的复杂性。

3. 自动应用到所有文件：在"转换示例文件"中执行的清洗步骤会自动同步到"转换文件"函数中。这意味着当文件列表中的其他文件被追加时，这些清洗步骤会通过函数调用自动应用到所有文件上，而无需手动重复操作。

9.3.4 转换示例文件

选择 “转换示例文件” 查询，对示例文件数据进行清洗和转换。此时已经有三个应用的步骤：

1. Source：包含 Excel 文件中所有可用对象的原始表；
2. Navigation：导航到"Forecast"工作表；
3. Promoted Headers：将第一行提升为标题。

仔细观察数据，被提升标题的那1行以及接下来的五行数据都没什么用，真正的数据从第7行开始

• 删除 “Promoted Headers” 步骤； 删除前 6 行，然后将第1行用作标题。
• 删除自动应用的"Change Type"步骤
  每次提升标题之后，都会自动转换数据类型，此时会将列名硬编码到M公式中。而在本章开头的案例背景中有提到，并非所有的区域都产生相同的产品，所以列的数量因文件而不同，所以需要删除此步骤
• 删除垃圾列与垃圾行：删除 “Part Nbr” 列中的 “Total” 行；删除"Total"列
• 逆透视：对 “Part Nbr” 列进行【逆透视其他列】操作，重命名 “属性” 列为 Product，“值” 列为 Units。
• 转换数据类型：选择所有列，点击【转换】【检测数据类型】。

9.3.5 在主查询中规范数据

9.3.5.1 自动添加的5个步骤

1. Filtered Hidden Files1（筛选的隐藏文件1）：添加一个筛选器，从文件列表中删除任何隐藏的文件（Power Query 会列出存储在文件夹中的隐藏文件和系统文件）。
2. Invoke Custom Function1（调用自定义函数1）：添加一个新的列，该列调用"转换文件"函数，生成每个文件转换后的表。
   
3. Removed Other Columns1（删除的其他列1）：此步骤删除了转换文件之外的所有列，包括文件名和文件夹路径。
   
4. Expanded Table Column1（扩展的表格列1）：展开转换文件列。
   
5. Changed Type：由于上一步在转换示例文件中进行了逆透视，所以列变化了，导致主查询中硬编码的的数据类型转换步骤出错，所以需要将这一步删除。
   

   有经验的用户提前知道要在"转换示例文件"中会重命名列，可以提前主查询中删除"Changed Type"步骤。

9.3.5.2 数据清洗

1. 添加文件属性：修改 “Removed Other Columns 1” 步骤，保留 “Name” 和 “Folder Path” 列。
   

2. 获取区域名：将 Name 列重命名为 Division，将后缀 ".xlsx"替换为空。

3. 获取年份和季度信息：使用 \ 字符拆分 “Folder Path” 列，提取年份和季度信息，将拆分后的列重命名为 “Year” 和 “Quarter”，并替换掉多余的 \ 字符（也可以通过添加自定义列的方式实现）。

4. 转换数据类型：选择所有列，点击【转换】【检测数据类型】。
   

     注意事项：数据类型不会从"转换示例文件"中继承。因此，在加载到工作表或数据模型之前，一定要确保将更改数据类型作为查询的最后一步来设置。

5. 数据加载：将主查询加载到数据模型中

   • 在 Power BI 中，只需单击【关闭并应用】
   • 在 Excel 中，进入【主页】【关闭并上载至】，选择【仅创建连接】，同时勾选【将此数据添加到数据模型】复选框

可以看到，只有主查询被加载，所有的辅助查询，包括"转示例文件"，都是仅保持连接的。

9.4 制作报表与数据更新

9.4.1 更新解决方案

  在Excel中可以插入数据透视表（来自数据模型），在Power BI中可以生成矩阵视觉对象。然后将行字段设为"Part Nbr"，列字段设为"Year"，“Quarter”，值字段设为"Units"。

  当需要添加新文件时，只需将新文件夹拖入源数据文件夹中，然后在 Power BI 或 Excel 中点击【刷新】按钮，即可更新数据。下面将"2019 Q4"文件夹拖入"Source Data"文件夹中，然后刷新数据。

  现在，无论用户是在旧有文件中更新数据（比如新文件覆盖旧文件，文件名不变），还是在新的文件中添加新数据（新文件名），都可以通过简单的操作快速更新数据。

9.4.2 只更新最后X个文件

  当源数据文件夹中的文件数量不断增加时，刷新速度会越来越慢。假设一个解决方案运行了10年，每年有16个数据文件（4个区域 x 4个季度），到2030年时，需要处理的文件数量将超过176个。如果每个文件需要5秒钟来刷新，那么总刷新时间将超过14分钟，这会显著影响用户体验。

  其实用户完全没必要分析那么多数据，比如业务只需要与前一年的数据进行比较，那么可以通过按日期的降序对文件进行排序，并使用【保留最前面几行】来限制保存的文件数量，以提升速度。在这个示例中，可以使用"Folder Path"列，因为文件是按照逻辑顺序命名的。如果没有这样的结构，可以考虑使用文件的"创建日期"或"修改日期"字段。

注意：复制文件通常不会保留原文件的创建日期，而修改日期可能会在文件被编辑或创建新文件时更新。在某些操作系统中，仅仅是打开文件（即使不进行任何修改）也可能更新文件的修改日期。

9.5 手动创建合并文件流程

参考《重新创建合并文件》

  Power Query 的【合并文件】按钮是一个自动化宏，它背后自动生成了所有必要的组件（如 Sample File、参数、转换逻辑、函数等）。虽然一键操作很方便，但用户可能无法理解这些组件是如何协同工作的，导致难以手动调整或自定义流程。下面手动创建整个过程。

9.5.1 创建核心组件

1. 创建FilesList 查询：【从文件夹】导入第 09 章 示例文件 Source Data，使用同样的步骤（“Extension”列转为小写，然后筛选“.xlsx”文件后缀；筛选非临时文件，最后加载为【仅限连接】）
2. 创建Orders查询：引用 “FilesList”查询，并将其重命名为“Orders”。
3. 创建Sample File查询：动态获取 Orders 中的第一个文件作为示例。
   • 引用第一个文件：右击Orders查询“Content”列中的第一个“Binary”文件，选择【作为新查询添加】，将新查询重命名为Sample File。Sample File查询现在有两个步骤——“Source”引用“Orders”表；"Navigation”钻取到引用的文件
     
   • 优化代码：此时，"Navigation”中的表名是硬编码的，将其替换为“=Source{0}[Content]”，删除自动生成的Imported Excel Workbook（导入的 Excel 工作簿）”步骤。
     
4. 创建Sample File Parameter 参数：新建参数，重命名为“Sample File Parameter”，设置如下：
   
   
5. 创建Transform Sample 查询：引用Sample File Parameter”参数，右击excel文件，选择解析为excel工作簿：
   
   单击“Parts”行旁边的Table关键字，展开“Parts”表：
   
6. 创建Transform Function 函数：右击 Transform Sample 选择【创建函数】，【函数名称】为Transform Function。

现在将“Sample File”拖动到自动创建的“Transform Function”文件夹中，整个界面为：

9.5.2 调用函数清洗数据

1. 筛选数据：筛选Orders查询，只保留"Content"列和"Name"列
2. 调用转换函数：转到【添加列】【调用自定义函数】选择“Transform Function”，此时可以看到，Orders中所有二进制文件（excel文件）都按照“Transform Function”函数中定义的清洗步骤进行清洗。
   
3. 更新转换函数：选择“Transform Sample”，右击“Products”列【逆透视其他列】。将“属性”列重命名为“Part”，将“值”重命名为“Units”。通过更新“Transform Sample”查询来更新其封装的“Transform Function”函数。最终，调用此函数的“Orders”查询也被更新：
   
4. 展开并上载数据：展开“Transform Function”列，删除“Content”列，设置所有列的数据类型，最后上载数据
   • Excel 中只能设置一种连接方式。建议选择【关闭并上载至】【仅创建连接】，将所有查询先设置为仅限连接。加载完之后，再在[【查询/连接】窗口设置“Orders”查询为【加载到表】。
   • Power BI 中可自由设置所有查询的连接方式。

9.5.3 查询结构分析

查询/参数名称	类型	主要作用	依赖关系
FilesList	查询	获取文件夹中所有文件的列表，并筛选出目标文件。	数据源为文件夹，被Orders引用
Orders	查询	作为合并文件的入口，保留Content（二进制文件）和Name列 最终调用Transform Function处理每个文件。	引用FilesList 调用Transform Function。
Sample File	查询	动态引用Orders中的第一个文件作为示例文件，供后续转换使用。	依赖Orders查询的第一行数据。
Sample File Parameter	参数（二进制）	将 Sample File 查询作为参数传递给Transform Function函数。	默认值为Sample File查询
Transform Sample	查询	定义单个文件的转换逻辑，并将其步骤封装为Transform Function	引用Sample File Parameter参数
Transform Function	自定义函数	可被Orders调用以批量处理所有文件	由Transform Sample生成

1. 数据流与依赖关系

   • 初始数据： FilesList → Orders → Sample File （获取文件列表 → 引用列表 → 动态提取第一个文件）
   • 参数化与转换逻辑： Sample File → Sample File Parameter → Transform Sample → Transform Function （示例文件 → 参数化 → 定义转换逻辑 → 封装为函数）
   • 最终应用： Orders 调用 Transform Function，对每个文件的 Content 列应用转换逻辑。

2. 为什么需要额外定义一个 Sample File Parameter 参数，而不是直接让 Transform Sample 引用 Sample File ？
     这样做是为了引入参数化。如果 Transform Sample 直接引用 Sample File ，那么将其封装为函数时就没有参数了，Orders查询 也就无法传递 Content 列作为参数来动态调用。如果强行在 Transform Function 中 手动加入一个参数来传递文件路径，那么此函数就会与 Transform Sample 查询断开联系，更新 Transform Sample 时其步骤不会同步到 Transform Function ，那么就只能自己手动更新Transform Function 代码了。
   
   

   手动加入file参数，但此时更新已经不同步了

3. 函数的独立性
   Transform Function 在生成后，运行时仅依赖传入的参数（如二进制文件），不再需要 Transform Sample。即使删除 Transform Sample，函数仍能正常工作（但失去通过界面编辑的能力）。

4. 动态示例（Sample File）的局限性
   不是所有函数都需要动态示例文件，某些函数可能只需静态逻辑（如固定公式）。如果手动提供 Sample File（如硬编码路径），可能触发 Power Query 的隐私防火墙（Formula.Firewall）错误，因为外部数据源可能需要权限验证（隐私级别不一样时）。

十二、关系型数据源

当用户所在的公司允许用户直接访问公司内部的数据库时，这将是一个非常理想的获取数据的方式，具体来说，主要有以下两方面优势：

• 数据的时效性更高：例如，如果公司数据库中记录了最新的销售数据，用户可以直接从数据库中提取这些数据，而不会因为数据更新延迟而错过重要信息。如果用户从文件（如 Excel 文件）中加载数据，这些文件可能已经过时。例如，一个销售数据的 Excel 文件可能是几天前生成的
• 数据加载效率更高：从数据库加载数据通常比从文件中加载数据效率更高。
  • 索引优化：数据库是专门为高效存储和检索数据而设计的，其内部优化和索引机制可以显著提高数据读取速度。例如，数据库可以通过索引快速定位所需的数据，而不需要像文件一样逐行扫描。
  • 大数据处理：数据库通常支持并发访问和高效的查询优化，能够处理大量数据的复杂查询。相比之下，文件（如 Excel 文件）在处理大量数据时可能会变得非常缓慢，甚至出现性能瓶颈。例如，一个包含数百万行数据的 Excel 文件可能会导致 Excel 崩溃或响应缓慢，而数据库可以轻松处理这种规模的数据。

12.1 连接数据库

12.1.1 数据库连接方式

Power Query 支持连接多种数据库，连接方式主要有以下三种：

• 【获取数据】【来自数据库】 ：直接访问常见数据库。
• 【获取数据】【来自 Azure】 ：连接 Azure 云服务平台上的数据库。
• 【获取数据】【自其他源】 ：若所需数据库不在前两者之列，可通过安装供应商的 ODBC 驱动程序后，选择【ODBC】进行连接。

12.1.2 连接Azure SQL 数据库

  以连接到【Microsoft Access 数据库】中的【AdventureWorks 数据库】为例，这是一个 SQL 数据库，托管在微软的 Azure web services 上。这意味着无论用户身处世界何处，都可以连接并浏览其中的数据。具体操作如下：

1. 设置连接方式：选择【获取数据】【来自 Azure】【从 Azure SQL 数据库】。
2. 输入数据库信息：填写服务器名称 xlgdemos.database.windows.net 和数据库名称 AdventureWorks。
   

  在【高级选项】部分下，可以提供自定义 SQL 语句和其他特定用于连接器的选项。除非用户是一名 SQL 专家，能够编写非常高效的代码，或者数据库管理员给用户提供了连接数据库的明确方法，否则请避免使用这些方法。

3. 输入凭据：初次连接时，系统会提示用户输入凭据以连接到数据库。这里有几种选项：
   1. 使用 Windows 凭据（默认）：
      如果用户在公司内部局域网中工作，并且 IT 部门允许使用 Windows 身份验证，那么可以直接使用当前登录计算机的 Windows 凭据进行连接。

      注意：虽然这种方式方便，但并不是最佳实践。建议联系 IT 团队获取更安全的访问权限。

   2. 提供另一组 Windows 凭据：
      如果需要使用不同的用户凭据连接到数据库，可以选择在同一选项卡中输入另一组 Windows 凭据。
   3. 使用数据库凭据：
      对于连接到 Azure 数据库，需要切换到【数据库】选项卡。本例中，需要输入用户名：DataMaster以及密码：D4t4M@ster!，然后点击【连接】。
      

选择在 Windows Azure 上托管数据库的原因是：

• 高可用性：Azure 提供了接近 100% 的正常运行时间，这意味着数据库几乎可以随时访问，不会因为服务器故障或维护而频繁中断。
• 稳定性：自本书出版以来，作者一直使用 Azure 数据库作为示例，这证明了 Azure 在长期运行中的稳定性和可靠性。

如果出行无法连接的问题，可能是以下原因：

• 忘记选择【数据库安全凭据】，直接使用了默认的【Windows 凭据】；
• 【用户名】或【密码】输入错误
• 公司的防火墙或 VPN 设置阻止了对 Azure 数据库的访问。
  如果是这种情况，可以考虑使用本地的数据库版本，如第 12 章 示例文件中提供的微软 Access 版本数据库AdventureWorks.accdb（Access 数据库是本地文件数据库，不受网络限制，使用【Access 数据库】连接器进行连接）。

12.1.3 管理凭据

  当用户第一次使用 Power Query 连接到 Web 数据或数据库时，系统会弹出身份验证框，要求您输入凭据（如用户名和密码）。这些凭据在第一次设置后会被缓存，因此在后续的连接中，系统会自动使用缓存的凭据进行身份验证。

• 缓存位置：凭据被存储在本地用户的加密文件中，这意味着凭据是安全的，并且不会随着文件共享而泄露。假设用户A将包含查询的 Excel 文件通过电子邮件发送给其他用户，其它用户将无法直接使用用户A的凭据连接到数据库，从而保护了用户的敏感信息。
• 自动使用：在后续的连接中，Power Query 会自动使用缓存的凭据，无需用户再次手动输入。

若输入错误的连接信息或需修改凭据，可按以下步骤操作：

• Excel ：【数据】【获取数据】【数据源设置】。
• Power BI ：【主页】【转换数据】【数据源设置】。

这将启动【数据源设置】对话框，可通过搜索快速定位目标数据源。

此时用户有如下三个选择：

1. 更改源：用于更改 URL ，将查询指向一个新的数据库或服务器；

2. 编辑权限：此选项允许用户更改【用户名】和【密码】、查看或编辑凭据类型
   

3. 清除权限：如果需要完全删除缓存的凭据，可以选择【清除权限】。这将删除所有与该数据源相关的凭据信息，下次连接时，系统会再次提示您输入凭据。

12.1.4 使用导航器选择表

  连接成功后，进入【导航器】界面，可利用搜索功能快速找到所需表格。例如，在搜索框输入 "SalesOrder"，然后点击 "SalesLT.SalesOrderHeader" 表，Power Query 会加载该表的部分样本数据以供预览。

选择完表格后，单击【转换数据】，进入Power Query编辑器。此时已经有两个步骤：

• Source：返回数据库的原始模式。在这个步骤中，你可以查看数据库中所有可用的表、视图和其他对象。
• Navigation：直接加载用户在【导航器】界面中选择的表或视图

12.1.5 数据库中的表关系

  在数据库中，表之间常常存在一对多的关系（类似PowerBI中的模型关系）。例如，一个客户可以有多个销售订单，但每个销售订单只能对应一个客户,这种关系在Customers表和SalesOrderHeader表之间存在。数据库和 Power Query 的一个强大功能是自动关系检测。这意味着用户可以利用数据库中已经定义的关系来自动连接和合并数据，而无需进行手动连接（第10章中的横向合并数据）。

比如此示例中，先对数据进行清洗操作：

1. 删除多余列，仅保留 “OrderDate”、“SalesOrderNumber”、“SubTotal”、“TaxAmt”、“Freight”、“SalesLT.Customer” 和 “SalesLT.SalesOrderDetail”。
2. 将 "OrderDate" 列转换为【年】，并重命名为 "Year"。
3. 将 "SalesOrderNumber" 列重命名为"Order#"。

上图中，最后两列就显示了来自其他表的相关数据：

• "SalesLT.Customer"列：显示的是"Value"，表示的是与每个销售订单相关的单个客户的所有信息（来自Customer表）。
  

• "SalesLT.SalesOrderDetail"列：显示的是"Table"，表示与主订单相关的所有子订单的详细信息（来自SalesOrderDetail表）（每个主订单可以有多个相关的子订单记录，这些记录存储在另一个表中，用户可以展开这个表来查看每个子订单的详细信息）。
  

12.1.6 数据清洗

1. 删除不必要的"SalesLT.SalesOrderDetail"列；
2. 展开"SalesLT.Customer"列：单击"SalesLT.Customer"列，右上角的展开箭头，只勾选"SalesPerson"；
3. 替换值：将"SalesPerson"列的"adventure-works\"替换为空；
   
4. 重命名查询：将查询重命名为OrdersBySalesPerson；
5. 转换数据类型：选择所有列【转换】【检测数据类型】
6. 上载数据：转到【主页】选项开，【关闭并上载至】【表】【新工作表】
7. 创建透视表
   • 创建透视表：选择任意有数据单元格右击选择【插入】【数据透视表】，在【表格和区域】中填入H2
   • 配置透视表：行字段设为"SalesPerson"和"Order #"；值字段设为"SubTotal","Tax Amt","Freight"
   • 设置数据格式：将每列设置为无符号的2 位小数格式（会计格式，右击任意数值列，弹出的对话框选择【数字格式】【会计专用】【小数位数】输入“2”，【货币符号】选择【无】）

12.2 查询折叠：提升 Power Query 数据处理效率的关键

12.2.1 查询折叠机理：将多个数据处理步骤合并成一个更高效的查询

  查询折叠（Query Folding） 是 Power Query连接数据库时的一项重要优化机制。其核心思想是：将用户在 Power Query 中定义的数据转换步骤，自动转换为数据库原生的查询语句（如 SQL），让数据库服务器直接执行这些操作，而非将原始数据下载到本地后再处理。这一机制显著提升了查询性能，在处理大规模数据时效果尤为明显。

1. 查询折叠机理：

   • 操作下推（Pushdown）
     Power Query 不会将所有数据拉到本地处理，而是尽可能将筛选、聚合、排序等操作转换为数据库支持的语法，直接在数据库中执行。

   • 生成高效查询语句
     例如，你在 Power Query 中添加一个“筛选行”步骤，Power Query 会将其转换为 SQL 中的 WHERE 子句；添加“分组”则会转换为 GROUP BY 语句。

   • 减少数据传输量
     数据库仅返回处理后的结果（如过滤后的少量数据或聚合值），而非传输全部原始数据，节省网络带宽和本地内存。

  假设用户要求从包含 100 万条记录的表中筛选出特定部门的记录，用户首先发出指令“选择所有表中的记录”，然后紧接着发出另一个指令“排除 150 以外的所有部门”。服务器不是加载全部 1,000,000 条记录来进行筛选，而是直接构建一个更高效的查询：

// 返回tblTransactions表中所有部门编号为 150 相关的交易记录
Select * From tblTransactions WHERE Dept = '150'

这种方式只选择 符合条件的1,500 条记录，节省了处理 998,500 条记录的处理时间。

2. 查询折叠的优势：

   • 性能大幅提升 ：利用数据库服务器的计算能力（通常比本地更强），避免本地处理海量数据的性能瓶颈。

   • 减少资源消耗 ：本地只需处理最终结果，降低内存和 CPU 占用，尤其对大型数据集至关重要。

   • 动态优化查询 ：数据库可根据自身优化器（如 SQL Server 的查询优化器）生成更高效的执行计划。

3. 支持查询折叠的典型操作

   • 数据源操作：连接数据库、选择表或视图。
   • 行筛选：使用筛选器或 Table.SelectRows。
   • 列选择/重命名：Table.SelectColumns、Table.RenameColumns。
   • 聚合：Table.Group（转换为 SQL 的 GROUP BY）。
   • 排序：Table.Sort（转换为 ORDER BY）。
   • 合并查询：Table.Join（转换为 JOIN 语句，需数据库支持）。

4. 可能导致折叠中断的操作

   • 自定义函数或复杂表达式：如 Table.AddColumn 中使用未映射到数据库函数的自定义逻辑。
   • 本地解析的数据转换：如某些文本拆分、非标准日期处理。
   • 引用本地数据：例如合并数据库表和本地 Excel 表。
   • 部分聚合函数：使用数据库不支持的函数（如某些统计函数）。

12.2.2 如何验证查询折叠是否生效？

1. 查看生成的 SQL 语句 ：用户可以通过右击查询步骤并选择【查看本机查询】来检查查询是否被折叠。如果选项可用，则说明折叠成功。部分数据源不支持显示原生查询，但仍可能执行折叠。
   
   继续检查，可以看到查询折叠一直持续到“Replaced Value”步骤，后续步骤将使用本地处理器和 RAM 进行处理。
   
2. 查询折叠指示器：在Power Query 在线版中，有一个查询折叠指示器功能，提供了一种更直观的方式来显示 Power Query 查询中哪些步骤已经被折叠。比如下图显示了针对【Azure 数据库】的查询折叠指示器，可以清楚的看到查询折叠从从“Navigation”持续到“Changed column type”，从“Kept bottom rows”步骤就被打破了。
   

12.2.3 查询折叠的最佳实践

要注意的是，一旦打破查询折叠，后续步骤将无法折叠。为避免此情况，需注意以下几点：

1. 优先使用可折叠的操作 ： 尽量在数据库端完成筛选、聚合等操作，将不可折叠步骤（如最终计算列）放在查询末尾。

   • 调整步骤顺序 ：当查询折叠中断时，可尝试改变【应用的步骤】窗口中步骤的顺序，有时这能使查询折叠恢复（右击【应用的步骤】窗口下的步骤，然后选择【前移】或【后移】）。
   • 避免过早使用自定义 SQL 语句 ：除非是 SQL 专家且能确保所写 SQL 比 Power Query 自动生成的更高效，否则不建议在初始步骤使用自定义 SQL，这会立即中断查询折叠。
   • 避免过早加载数据 ：在数据清洗完成前，不要将中间结果加载到本地（如避免“提升标题”等操作打断折叠）。

2. 监控折叠状态 ：定期通过“查看原生查询”检查折叠是否生效，尤其是添加新步骤后。

3. 正确选择数据库连接器 ：选择正确的连接器非常重要，因为不同的连接器可能对查询折叠的支持程度不同。优先使用为数据源定制的连接器，而非 ODBC 连接器（容易打断查询折叠）。

  一个用户的BI项目连接到 SQL 数据库时，使用了ODBC连接器而不是Power Query自带的SQL Server连接器，检索前一周的百万条数据。一开始没有问题，但随着时间推移，解决方案变得越来越慢，甚至上班的8个小时都刷新不完。
  问题的关键在于ODBC连接器在筛选数据时会中断查询折叠，迫使Power Query下载整个数据库的数据到本地再进行筛选，这非常低效。将连接器更换为SQL Server连接器之后，数据几分钟就刷新完成了。

在高效地从数据库中提取数据以及查询折叠技术方面，还存在一些常见的混淆和误解：

1. 误解 1：使用单一 SQL 查询完成所有工作
   此误解认为最有效率的数据提取方法是通过一个复杂的 SQL 查询在一个 Power Query 步骤中完成所有工作。虽然此方法确实很快，甚至比Power Query 更高效，但它缺乏灵活性。当业务需求变化时，难以进行修改维护。使用 Power Query 用户界面构建查询可以提供更大的灵活性和易用性。

2. 误解 2：存在一个列出哪些步骤可以折叠的列表
   没有这样的名单，因为哪些命令能折叠可能取决于 Power Query 连接器和步骤的顺序。你用多了自然就知道哪些操作会破坏查询折叠。

3. 误解 3：查询折叠不能在多个查询中持续存在（查询与引用查询）
   查询折叠可以在多个查询中持续存在，只要这些查询之间的连接没有中断。比如右击上述查询中的“Removed Other Columns”步骤，选择【提取之前的步骤】，将新生成的查询重命名为“Database”，这样“OrdersBySalesPerson”查询就直接引用了“Database”查询。检查“OrdersBySalesPerson”查询，可以看到其中使用查询折叠的步骤依旧在使用。

通过合理利用查询折叠，可以显著提升 Power Query 处理数据库数据的效率，尤其是在处理千万级数据时，性能差异可能达到数量级！

12.2.4 哪些数据源支持查询折叠

1. 查询折叠依赖于计算引擎：查询折叠是一种将数据处理工作推送到数据源的技术，因此需要数据源具备计算引擎。这通常适用于具有存储和计算功能的数据库。

2. 文件类型不支持查询折叠：TXT、CSV 和 Excel 文件等不包含计算引擎，因此通常不支持查询折叠。这意味着当连接到这些文件类型时，查询折叠不可用。

3. 例外情况：微软 Access 是一个例外，虽然是文件，但它是一个文件型数据库，支持查询折叠。然而，由于它通常托管在本地 PC 上，性能提升可能不如服务器上的数据库明显。

4. 数据库支持差异：并非所有数据库都支持查询折叠。如果数据库不支持，Power Query 将下载完整数据集并在本地处理，这可能导致效率低下。

5. 连接器的影响：不同的数据库连接器对查询折叠的支持程度不同。ODBC 连接器是一个通用连接器，没有对特定数据集进行专门优化，容易中断查询折叠。建议用户在没有选择时才使用 ODBC，并在每个步骤中检查查询折叠的状态，以确保效率。

12.2.5 简单示例

假设你从 SQL Server 中加载一个 Sales 表，并执行以下步骤：

1. 筛选 Year = 2023
   → 转换为 WHERE Year = 2023。
2. 按 ProductID 分组求总销售额
   → 转换为 GROUP BY ProductID, SUM(Revenue)。
3. 按销售额降序排序
   → 转换为 ORDER BY SUM(Revenue) DESC。

最终生成的 SQL 可能是：

SELECT ProductID, SUM(Revenue) AS TotalRevenue
FROM Sales
WHERE Year = 2023
GROUP BY ProductID
ORDER BY TotalRevenue DESC

12.3 数据隐私级别管理

12.3.1 隐私级别不兼容问题

• 查询折叠可能触发跨数据源数据传输 ：当 Power Query 合并多个数据源时（例如将 Excel 表格与 SQL 数据库关联），查询折叠会尝试生成统一查询语句。如果涉及不同隐私级别的数据源，系统会检查是否符合隐私规则。

• 数据预览可能泄露敏感信息 ：用户在 Power Query 中操作时，系统可能自动发送 两个查询 到数据库：

  1. 参数探测查询：获取变量值（如最新日期、客户名称），用于生成最终查询逻辑。
  2. 最终折叠查询：实际执行的查询。 若参数查询包含敏感数据（如专用数据源的字段值），且隐私级别设置不当，可能导致数据泄露。

12.3.2 数据隐私的三个级别及其交互规则

  在Power Query 中，每个数据源都有不同的 隐私级别，目的是 控制敏感数据在不同数据源之间的传输风险，防止意外泄露。隐私级别及其交互规则为：：

• 专用（Private） ：包含高度敏感或机密数据（如个人身份信息、财务数据），不可与其他任何数据源交互（包括其他专用数据源）。

• 组织（Organizational） ：数据仅限组织内部可见（如公司内部数据库），不可与公共数据源交互，但可与同属“组织”级别的数据源交互。比如将公司销售数据（组织）发送到公共网站（公共）会被阻止。

• 公共（Public） ：数据完全公开（如公开的 API、网站数据），可发送到“公共”或“组织”级别的数据源。比如将公开的天气 API 数据（公共）合并到公司数据库（组织）是允许的。

  在合并数据时，如果违反此规则，将触发公式防火墙错误（Formula Firewall）。例如，如果一个 Excel 电子表格被标记为组织隐私级别，那么不能将其数据发送到公共网站（公共数据源）；或者在同一查询中，合并不同隐私级别的数据源：

12.3.3 声明数据隐私级别

  Power Query 的 隐私级别 是一种数据安全机制，通过限制不同敏感级别的数据源之间的交互，防止查询折叠过程中意外泄露敏感信息，不仅限于数据库数据。当 Power Query 检测到跨数据源操作（如下图合并 FilesList 查询与 SharePoint）时，会触发黄色弹窗提示，要求用户为每个数据源指定隐私级别。单击【继续】，将弹出隐私设置对话框。

  通常可在 数据源设置 中选择目标数据源 → 编辑权限 → 调整隐私级别，可查看或修改数据【隐私级别】（【管理凭据】的同一对话框）

管理【Azure 数据库】的【隐私级别】

  警告：Power Query 的隐私设置与工作簿的保存状态绑定，当你在 Excel 中使用 Power Query 设置数据源的 【隐私级别】 后，如果 未保存工作簿 就关闭文件，Power Query 会 丢弃已配置的隐私设置，下次打开工作簿时，需要 重新声明隐私级别。

  频繁的隐私设置重置会导致用户体验差，若用户忘记重新设置隐私级别，还可能意外触发数据泄露风险。作者网站提供了一个 基于 VBA 的自动化工具，当用户修改 Power Query 查询或隐私设置后，弹出提示强制保存工作簿。若无 VBA，需养成“修改后立即保存”的习惯，或启用 Excel 自动保存功能。

12.3.4 数据隐私与查询性能的权衡

  启用隐私检查可能导致刷新时间延长，尤其在跨数据源查询时（如合并 Excel 本地数据与外部数据库）。同一数据源豁免检查，即所有数据来自同一文件（如工作簿内的表）时，无需隐私检查，对性能无影响。

  下图是使用了 Ken 的 Monkey Tools 插件（ Ken 的网站上可下载免费试用版），显示了两个查询5次刷新操作的平均时间。某些情况下，禁用隐私设置会显著提高刷新时间。

12.3.5 禁用隐私引擎

禁用隐私引擎的优点	禁用隐私引擎缺点
刷新时间更短	数据泄露风险
不会出现公式防火墙错误	Power BI 计划刷新可能被阻止（ 禁用隐私引擎可能会导致 Power BI 服务不允许覆盖隐私设置，从而阻止模型刷新。）

  禁用隐私引擎需要谨慎决策，当内部环境100%可控时（如公司内部网络），禁用后可提升开发效率（不必担心“公式防火墙”）与性能。隐私设置通过【查询选项】对话框进行控制：

• Excel：【数据】【获取数据】【查询选项】
• Power BI：【文件】【选项和设置】【选项】

  打开后会发现有两个【隐私】选项卡：一个是【全局区域】（适用于所有工作簿），一个是【当前工作簿】。推荐通过 【当前工作簿】部分单独禁用隐私检查，切勿选择“始终忽略隐私设置”（类似关闭宏安全警告）。

1. 始终根据每个源的隐私级别设置合并数据：这将根据凭据区域中配置的级别继承每个数据源的隐私级别设置，在合并数据时强制执行隐私检查（无论用户是否觉得需要）。

2. 根据每个文件的隐私级别设置进行数据合并（默认）：允许用户禁用特定解决方案的【隐私级别】，本书建议用户坚持使用此选项。

3. 始终忽略隐私级别设置：完全禁用隐私检查，允许数据自由流动。类似于在 Excel 2003 及更早版本中关闭 VBA 宏安全性，虽然可以避免警告提示，但会完全暴露专用数据源。本书建议用户不要选择这个选项。

12.4 性能优化

为提升 Power Query 查询性能，可采取以下策略：

1. 优先使用特定数据库连接器：避免使用 ODBC 连接器（更容易中断查询折叠）
2. 谨慎使用自定义SQL语句：自定义SQL会强制中断后续所有查询折叠，除非用户是SQL专家且能写出比Power Query自动生成的更高效的查询，否则不建议使用。优先通过Power Query界面操作（如点选筛选、排序），让系统自动生成优化后的SQL。
3. 将计算压力推送给数据库：数据库服务器性能远强于本地机器，应利用查询折叠的优势尽可能将操作（如聚合、连接）下推到数据库执行。
4. 尽可能使用 Power Query 用户界面命令完成工作：Power Query设计初衷是为非技术用户提供可视化工具，自动生成高效的底层代码（如SQL或M），也不是替代专业数据库工具（如SSMS）。在初始查询设计中，多用 Power Query 用户界面命令，少用自定义 M 代码或复杂表达式（如动态参数表），这些操作通常无法折叠。

策略	操作建议	性能影响
使用专用连接器	避免ODBC，选数据库专属连接器	⭐⭐⭐ 显著提升折叠成功率
避免自定义SQL	通过UI操作生成查询，除非SQL专家优化	⭐⭐ 防止折叠中断
下推计算到数据库	优先用数据库支持的聚合、筛选、连接	⭐⭐⭐ 大幅减少本地负载
减少自定义M代码	用界面按钮替代手动编码	⭐⭐ 降低折叠失败风险
选择性禁用隐私检查	仅限安全环境，通过工作簿设置而非全局关闭	⭐ 提升速度，但增加风险

十七、自定义函数

17.1 手动创建合并文件流程（见9.5章节）

17.2 函数参数化改造

  在数据处理过程中，有时会遇到清洗完一个文件的数据之后，随着业务的进展，发现还有其它文件也需要同样的方式进行清洗。此时最好的方式，就是将原文件的清洗步骤封装为一个参数化的函数，以便进行调用。

  下面以第17章示例文件 Retrofitting-Begin.xlsx为例。此文件有一个"Timesheet"查询，是处理2021-03-14.txt文件后得到的（详细h亲逻辑见14.2章节）。现在要将其改造为能够处理"Timesheets"子文件夹中所有文件的自动化解决方案，同时不破坏现有基于"Timesheet"查询的业务逻辑。

  除了"Timesheet"查询，原文件中还有一个FilesList查询，列出了“TimeSheets”子文件夹中的所有文件，并最终返回一个简单的文件路径列表：

在这个解决方案中，需要采取如下四个步骤：

1. 创建一个“FilePath”参数（类似“Sample File Parameter”）；
2. 基于"Timesheet"查询创建新的“Timesheet Transform”查询，并利用该【参数】（类似“Transform Sample”）；
3. 将“Timesheet Transform”封装为“Timesheet Function”；
4. 修改原始“Timesheet”查询以调用新的“Timesheet Function”（类似“Orders”查询）。

17.2.1 创建文件路径参数（FilePath）

• 从"FilesList"查询复制第一个文件路径
• 创建名为"FilePath"的文本类型参数
• 将复制的路径粘贴为参数的当前值
  
  这种方式等同于将文件夹中第一个示例文件设置为参数。

17.2.2 创建 Timesheet 转换查询与 Timesheet Function 函数

• 复制原始"Timesheet"查询并重命名为"Timesheet Transform"

• 编辑"Source"步骤，将硬编码文件路径替换为"FilePath"参数
  

• 将该查询封装为“Timesheet Function”函数

17.2.3 更新原始Timesheet查询

1. 更新查询：清除Timesheet查询的所有步骤，并将其设为引用"FilesList"查询，有两种方式：
   • 删除Timesheet查询，引用FilesList查询并将其重命名为Timesheet查询(这种方式，上载数据之后无法覆盖原Timesheet工作簿中的数据位置，所以还是方法2更好）
   • 在Timesheet查询中输入：

     let
         Source = FilesList
     in
         Source
     

2. 调用"Timesheet Function"函数：在Timesheet查询中，使用如下方式调用函数，但此时遇到"Formula.Firewall"错误。
   
3. 重新调用函数：在"FilesList"查询中使用同样的方式调用函数，得到中间结果。
   
4. 在Timesheet查询中清洗数据：
   • 删除"Timesheet Function”列之外的所有列，然后将此列完全展开（取消勾选【使用原始列名作为前缀】复选框）；
   • 转换所有列的数据类型（选择所有列，【转换】【检测数据类型】）
5. 上载数据
   

17.2.4 注意事项

• 路径问题：所有查询中的文件路径最初都是硬编码的，需要更新为本地路径
• 业务逻辑保护：必须确保改造后的查询输出与原始结构一致，不影响现有依赖
• Formula.Firewall错误：直接在目标查询调用函数会遇到隐私限制错误，需在源查询(“FilesList”)中调用
• 加载设置：新创建的辅助查询应设为"仅创建连接"，只有主查询加载到工作表

  改造后的解决方案能够自动处理"Timesheets"子文件夹中的所有文件，合并它们的记录，同时保持原有业务逻辑正常工作。这种方法提高了查询的复用性和可维护性。

17.2.5 为什么在 “Timesheet” 查询中调用函数会触发防火墙错误，而在原查询中调用则不会？

• FilesList查询：文件列表，是明确的Public级别数据源，直接访问本地文件夹，采用静态路径
• Timesheet Function：自定义函数，接受"FilePath"参数（静态参数）；
• Timesheet查询：引用FilesList查询，并执行数据清洗步骤，隐私级别取决于查询组成。

调用函数失败的根本原因在于数据流隐私级别冲突。

1. 在Timesheet查询中调用函数：链式调用（分段创建新上下文），路径为：FilesList(Public) → Timesheet查询 → Timesheet Function。

   • Timesheet查询启动执行
   • 引用FilesList查询(Public)
   • 复合查询(引用多个查询的查询)会创建新的隐私上下文，当尝试调用Timesheet Function(形成新隐私上下文)，防火墙检测到Public→Private的潜在隐私泄露风险
   • 阻止执行

   // Power Query 看到的逻辑结构
   let
       Source = FilesList,                          	// Public
       Processed = TimesheetFunction(Source{0}[Path])  // 动态路径 → Private
       // 静态分析器无法验证 TimesheetFunction 内部是否会安全处理数据，因此默认拒绝。
   in
       Processed
   

2. 在FilesList查询在调用函数：同级调用（统一上下文），路径为：FilesList(Public) → Timesheet Function。

   • FilesList查询启动执行，获取文件列表(Public操作)
   • 在同一上下文中调用函数处理每个文件，所有操作保持在Public域内
   • 防火墙验证通过

   let
       Files = Folder.Files("C:\Data"),            // Public
       // 函数直接在FilesList查询中被调用，所有操作共享同一个执行上下文，无隐私切换
       Results = Table.TransformRows(Files, (row) => TimesheetFunction(row[Path])            
   in
       Results
   

这种差异体现了Power Query的几个核心设计原则：

1. 数据获取与数据处理分离：

   • 鼓励在数据获取阶段完成所有敏感操作，将复杂函数调用上移到数据源查询，而非复合查询；
   • 数据处理阶段应只操作已获取的数据，避免跨查询传递可能携带隐私信息的数据，同时符合Power Query的查询折叠优化条件。对于复杂场景可采用星型拓扑：

             FilesList
                 |
        +--------+--------+
        |        |        |
    Transform1 Transform2 Transform3
        |        |        |
        +--------+--------+
                 |
             FinalQuery
   

2. 隐私域最小化：确保所有数据获取在同一上下文中完成，明确的数据流方向，禁止可能形成循环或复杂交叉引用的设计。

对于大型项目，建议采用以下分层架构：

1. 数据接入层：每个数据源独立查询，完成基础清洗，输出标准化结构

2. 转换层：使用函数库处理业务逻辑，，所有转换在参数化查询中完成

3. 语义层：简单的关联/合并，不包含任何数据获取操作

4. 展示层：仅做最终格式调整，设置加载目标

17.3 手动构建自定义函数（以表为参数）

17.3.1 案例背景

  在 "第 17 章 示例文件\Pivoting Tables-Begin.xlsx” 文件中，第一个工作表中有两个表。本案例的目的是将左侧两个表进行逆透视，将两个表的数据合并之后得到右侧的逆透视表：

  最好的解决方案是构建一个函数来处理每一张表，但是在Power Query中，通过界面管理的函数参数只能是基元类型，所以这种方式是行不通的。本章节将详细介绍如何手动构建自定义函数来实现动态逆透视操作，并分享调试技巧和最佳实践。

为了构建【自定义函数】，一般要遵循如下流程：

1. 构建单一应用场景
2. 将查询转换为函数
3. 调用函数

17.3.2 构建单一应用场景

1. 导入数据：
   • 打开 “第 17 章 示例文件\Pivoting Tables-Begin.xlsx” 文件，创建【空白查询】
   • 在公式栏输入 =Excel.CurrentWorkbook()，并将将查询重命名为"Sales"
2. 筛选数据表：筛选"Name"列【不等于】“Sales”（避免包含自己创建的表）
3. 创建示例文件：右击"Content"列中的任意表值（此例在选择Table2），选择【作为新查询添加】，将新查询重命名为"fxUnpivot"
4. 数据清洗：
   • 右击"Date"列选择【逆透视其他列】
   • 重命名"属性"列为"Product"，"值"列为"Units"
   • 设置三列数据的类型分别为【日期】、【文本】和【整数】
     

17.3.3 将查询转换为函数

转换过程需要三个关键操作：

1. 命名参数：选择一个描述性的变量名（variable_name）
2. 编辑查询：在let语句前添加( variable_name )=>并按Enter，引入参数
3. 替换数据：用参数名替换实际数据引用

  添加参数：右击“fxUnpivot”查询【高级编辑器】，在开头输入“(tbl)=>”并按 Enter 键，此时已经将查询转换为函数。

  添加参数引用位置：在将查询转换为自定义函数时，需要精简代码结构，直接传递参数到真正需要它的步骤，而不是保留所有原始查询的中间步骤。在原来的“Table1”步骤中，我们导航到“Table2”表格，所以可以将“Table2”替换为tbl变量。但是仔细分析M代码，“Table1”步骤得到的表，其实是要传递到下一步的Table.UnpivotOtherColumns参数中，所以直接在这一步进行替换，前三行都可以删了。

17.3.4 调用函数

1. 调用函数：选择"Sales"查询，【添加列】→【调用自定义函数】
   
2. 数据清洗：
   • 删除“fxUnpivot”列之外的其它列
   • 展开“fxUnpivot”列（取消勾选【使用原始列名作为前缀】复选框）
   • 选中全部列，【转换】【检测数据类型】
     

17.3.5 调试自定义函数的技巧

由于自定义函数无法像普通查询那样通过界面操作逐步调试，所以需要临时将其转换回查询：

1. 注释函数声明：在高级编辑器中，在函数声明行前添加//

2. 添加临时变量赋值：为了复制变量，需要在初始 let 行之后设置一个新步骤，该步骤创建并为变量赋值：

   Variable_name = assign_value_here ,
   

   在本例中，右击“fxUnpivot”查询【高级编辑器】，修改代码

   //(tbl) =>
   let
   	tbl = Excel.CurrentWorkbook(){0}[Content],  // 获取当前工作簿所有表中第一个表的内容
   

   此时，fxUnpivot函数变成了一个查询，可以进行逐步调试了，只不过任何引用它的查询都将报错。
   

3. 调试完成后恢复：调试完成后，取消注释函数声明， 注释或删除临时变量赋值行，查询将重新转换回函数。为提高代码健壮性，可以为变量添加类型声明：
   

4. 如果需要逆透视的固定列名也可能变化，可以将其也设为参数：

   (tbl as table, fixedColumns as list) =>
   let
       #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(tbl, fixedColumns, "Attribute", "Value")
   in
       #"Unpivoted Other Columns"
   

这种技术特别适用于以下场景：

1. 多表合并：多个结构相似的表需要先转换后合并
2. 定期报表：每月/每周新增数据表需要相同处理
3. 数据标准化：将不同来源但结构类似的数据统一为标准格式

17.4 动态参数表

在Power Query的实际应用中，传统参数管理存在两大核心问题：

1. 参数值固定：在实际的数据处理场景中，我们常常需要根据不同的环境或需求动态调整数据源路径、筛选条件或其他参数，传统的静态参数设置方式无法应对；
2. 操作不便：每次修改必须通过Power Query编辑器中的"管理参数"界面

  为了解决这些问题，我们可以在 Excel 中创建一个参数表，并结合自定义函数从该表中读取参数值，实现参数的动态更新和灵活配置，且无需修改 M 代码（也就无需进入 Power Query 编辑器），特别适合需要灵活部署的解决方案。

• M语言原生无法获取工作簿当前位置，但是Excel 公式可以完成；
• Power BI对发布后模型的参数存在修改限制

动态参数表适用于多种场景，以下是一些常见的应用示例：

1. 动态文件路径
2. 基于日期的动态筛选 :可以通过参数表中的日期值动态筛选数据，例如根据当前日期生成日历表或筛选特定日期范围内的数据。
3. 动态表格选择 :根据参数表中的值动态选择要加载的 Excel 表格，例如从多个表格中选择一个特定表格进行数据处理。

17.4.1 创建动态参数表

  打开“第 17 章 示例文件\Retrofitting-Begin.xlsx”文件，在 Timesheet”工作表创建一个表，列名必须为“Parameter”和“Value”。然后使用“Ctrl+T”功能将其创建为一个正式的表格，并命名为“Parameters”，以便后续能够通过名称引用它。

如果需要动态获取工作簿所在的文件路径，可以在“Value”列中使用以下 Excel 公式：

=IFERROR(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1), "Workbook needs to be saved!") & "TimeSheets\"

• &之前的公式部分返回的是当前Excel表的路径。由于要读取的数据在TimeSheets子文件夹中，所以末尾加上子文件夹名称。
• 如果使用 Excel 公式动态获取文件路径，确保文件已保存，否则公式无法返回正确的路径。此外，对于同步到 OneDrive 或 SharePoint 的文件，可能需要额外处理以确保返回本地文件路径。

17.4.2 构建fnGetParameter函数

为了从参数表中读取参数值，我们需要编写一个自定义函数：

// 由 Ken Puls (FCPA, FCMA, MS MVP Excel) 编写的 fnGetParameter 函数

// 1. 使用本函数需在工作簿中创建名为"Parameters"的表格，表头必须包含"Parameter"和"Value"两列
// 2. 数据行应在第一列填写参数名称，第二列填写对应的参数值，并将将此查询重命名为"fnGetParameter"
// 3. 可在其他查询中通过以下方式调用：
//  =fnGetParameter("您的参数名称")

// 4. 实用示例 - 获取当前工作簿路径：
// 参数名称:    File Path
// 参数值:
//   Excel 2019及更早版本: =IFERROR(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1),"请先保存工作簿！")
//   Excel 365版本:        =LET(filepath,CELL("filename",A1),IFERROR(LEFT(filepath,FIND("[",filepath,1)-1),"请先保存工作簿！"))
// 调用方式: "fnGetParamater("File Path")"
( getValue as text ) =>
let
    ParamTable = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],	// 读取“Parameters”参数表内容
    // // 查找与指定参数名称匹配的记录行，并返回其“Value”列的值。
    Result = ParamTable{[Parameter=getValue]}?[Value]?					
in
    Result

17.4.3 调用函数

  在【高级编辑器】中修改“FilesList”查询代码：在 let 行之后立即插入fullfilepath = fnGetParameter("File Path"),，并在Source步骤中传入此参数。

虽然技术上可以直接在需要使用参数的地方内联调用 fnGetParameter 函数，即：

// 不推荐的写法（直接嵌套调用）
Source = Folder.Files(fnGetParameter("File Path"))

但最佳实践是先在单独的步骤中获取参数值，再在后续步骤中使用这个变量：

// 推荐的写法（分步执行）
fullfilepath = fnGetParameter("File Path"),
Source = Folder.Files(fullfilepath),

• 防火墙问题：Power Query 的安全机制可能会阻止嵌套函数直接访问数据源，单独步骤可以明确数据流。
• 调试便利性：独立的步骤允许你单独检查参数值是否正确获取（点击步骤即可预览值），而嵌套写法难以调试。
• 嵌套调用在复杂查询中可能引发级联错误

  打开"Dynamic FilePath-Complete.xlsx”文件，可看到以上完整操作内容（Timesheet Function是正确的，但是Timesheet Transform查询和FilePath参数是错误的，但是结果不影响，因为函数独立）。

十九 、公式防火墙(Formula.Firewall)

  公式防火墙(Formula.Firewall)是Power Query中最令人困扰的错误之一，尤其是在处理动态数据源或合并来自不同数据源的数据时。错误通常以"请重建此数据组合"结尾，但实际原因可能有多种。

19.1 隐私级别不兼容

  在本文第12.3章节介绍过， 当在同一个查询中，试图访问具有不兼容隐私级别的多个数据源时（比如查询折叠可能触发跨数据源数据传输），将会触发“公式防火墙”错误。可以通过正确声明和管理数据隐私级别、优先在单一数据源内完成查询折叠 、禁用隐私级别（谨慎） 来解决。

19.2 数据源访问问题

  当尝试使用一个数据源中的值动态检索或筛选另一个数据源中的数据时，也会触发“公式防火墙”错误。这种情况常见于使用 Excel 参数表或基于 Web 的数据源。在本文17.2.5 更新 Timesheet 查询章节，直接调用"Timesheet Function”来更新“Timesheet”查询，会弹出公式防火墙错误：

  解决方法采用同级调用（FilesList→ Timesheet Function），将数据获取与数据转换操作分开，避免直接在复合查询中传递数据源参数。

19.3 动态参数传递引发防火墙错误

19.3.1 业务背景

  当尝试使用动态值作为数据源函数的参数时，Power Query 会抛出"公式防火墙"错误。这是因为：

• Power Query 在计算查询结果前会进行静态分析，检查数据源的隐私兼容性
• 动态参数需要先计算才能确定数据源位置，违反了 Power Query 的惰性计算引擎的核心结构，与静态分析过程冲突

以“第 19 章 示例文件\Firewall-Begin.xlsx”文件为例，其中有以下关键组件：

1. “Parameter Table”表：动态参数表，其中File Path参数使用的是17.4.1中同样的excel公式来动态获取文件路径；
   

2. fnGetParameter 函数 ：获取参数表的值，代码同17.4.2章节

3. Timesheet 查询与EmployeeDepts 查询：都在Source步骤之前，调用fnGetParameter 函数动态生成文件路径（FilePath），获取数据并进行后处理

  Timesheet 查询与EmployeeDepts 查询分别包含员工上班记录和员工部门信息，现在想将EmployeeDepts查询合并到Timesheet 中，补全部门信息：

合并时，界面下方提示【无法确定该选择将返回多少个匹配项】，点击确认后弹出：

19.3.2 链式重构

19.3.2.1 链式重构合并逻辑

如果遵循在第 2 章中介绍的多查询体系结构，则不会遇到这个问题，因为不在Timesheet 中执行合并操作。

1. 保持 Timesheet 和 EmployeeDepts 为仅连接查询；
2. 创建新的"Output"查询，该查询引用自 Timesheet查询；
3. 在 Output 查询中执行合并操作，此时不会触发防火墙错误；
4. 展开EmployeedPts”列中的“Department”字段。

19.3.2.2 动态筛选试验，再次触发防火墙错误

1. 在“Department”列中筛选“Accounting”值
2. 将公式栏中的““Accounting””替换为“fnGetParameter(“Department”)”，此时再次触发公式防火墙错误：
   

解决方法是将将参数检索隔离到自己的步骤中：

3. 直接在fnGetParameter中输入"Department"进行函数调用，生成固定的参数值，将此查询重命名为“Department”
   
4. 修改之前的筛选公式，将“fnGetParameter(“Department”)”替换为“Department”，即可避免公式防火墙错误
   

19.3.2.3 完善动态筛选逻辑

最后为这个解决方案添加一个动态可选的部门筛选器，通过参数表的空值处理可以实现。

• 原方案：强制用户必须选择一个部门（在Excel单元格C8的下拉菜单中）
• 新方案：允许用户清除筛选（此时该参数返回null值）以查看所有部门数据

原筛选公式为：

= Table.SelectRows( #"Expanded {0}", each ([Department] = Department ) )

将其改为：

= if Department <> null  
	then Table.SelectRows( #"Expanded {0}", each ( [Department] = Department ) ) 
	else #"Expanded {0}"

其逻辑为：当Department有值时执行正常的筛选操作，当Department为null：跳过筛选，返回未筛选的完整表。

  这种设计方式，仍然遵循"参数查询隔离"原则，只是增加了null值处理逻辑，不改变隐私级别结构。同样的模式可应用于其他筛选条件，比如用户选择特定日期 → 加载对应文件。

19.3.2.4 公式防火墙的深层机制解析

1. 默认隐私级别设定：

   • 本地文件：未特别设置时，受信任的本地文件默认隐私级别为"Public"。如果来自不同安全区域（如一个在C盘，一个在网络共享），可能自动提升为Organizational；
   • 数据库/Web数据源：通常默认为"Organizational"
   • 动态数据源：无法在静态分析阶段确认的数据源会被视为"Private"

2. 第一次错误（合并查询）的隐私冲突

组件	数据源类型	隐私级别判定	冲突原因
Timesheet查询	动态文本文件路径	Private（路径由fnGetParameter动态生成）	两个Private数据源尝试合并
EmployeeDepts查询	动态Excel文件路径	Private（同样动态生成路径）	违反Private-Private交互禁令

• 关键点：虽然实际都是本地文件（本应是Public），但因路径是动态生成的，静态分析阶段无法验证，故双双被提升为Private级别。
• 解决办法：引用查询。引用后的查询视作物化数据（隐私级别重置为Public），合并操作变为Public-Public交互。

3. 第二次错误（动态筛选）的隐私冲突

组件	数据源类型	隐私级别判定	冲突原因
Output查询	已加载的Timesheet数据	Public（静态数据）	数据处理中混入数据获取操作
fnGetParameter调用	Excel参数表	Private（实时读取工作表）	Public查询中嵌入Private操作

• 特殊机制：即使主查询数据是Public，但其中的某个步骤尝试执行Private操作（实时获取参数），整个查询会被降级为Private。
  解决办法：参数获取与数据处理分离，筛选时只是值比较，不涉及数据源访问

这种设计体现了Power Query在灵活性和安全性之间的权衡，理解这些底层规则后，就能主动设计出符合防火墙要求的查询结构。

19.3.3 展开式重构（一体化查询）

  另一种实现办法是，将以上4个查询的所有数据获取、调用、清洗步骤都在同一个查询中进行（一体化查询），避免跨查询的动态数据源访问。详细实现方式见“第 19 章 示例文件\Firewall-Complete.xlsx”中的“All_In_One”查询。

1. 将 fnGetParameter 函数重命名为 fxGetParameter 以避免与原始函数冲突；
2. 调用各个数据源，包括原Timesheet和EmployeeDepts两个查询的路径、调用部门参数的结果
3. 复制原始“EmployeeDepts”查询，仅更新以明确定义步骤名称；
4. 复制原始“Timesheet”查询，仅更新以明确定义步骤名称；
5. 复制原始“Output”查询中相同的步骤（合并文件与筛选部门）

这种方法的好处在于它是完全自包含的，因此可以更容易地从一个解决方案复制到另一个解决方案。

19.3.4 传值重构(用于SQL查询)

  有时候用户希望从数据库中获取数据，但需要根据某些动态条件（如用户输入的筛选器）来调整 SQL 查询，但直接传递会导致“公式防火墙”错误。比如在在“第 19 章 示例文件\Dynamic SQL.xlsx”文件中，有一个“Parameters”表，用户希望可以在SQL查询中通过fnGetParameter 函数动态传递此参数，返回Minimum到Maximum之间的数据行。

  此问题还是因为Power Query 的核心机制是惰性计算和查询折叠，Power Query 在执行查询时，会先进行静态分析，检查查询中涉及的所有数据源及其隐私级别是否兼容。动态参数无法在静态分析阶段被确定，因此会触发错误。

  本书仍然建议用户使用 Power Query 连接到数据源，并通过用户界面执行筛选，允许它折叠查询步骤以避免这个问题。但如果非要这么做，可以使用 Value.NativeQuery 函数，它允许安全地将动态参数传递到 SQL 查询中，同时避免“公式防火墙”错误，下面是详细步骤：

1. 定义两个查询，调用fnGetParameter函数获取参数表的最小值和最大值

   =Text.From( fnGetParameter( "Minimum" ) )	// valMin查询
   =Text.From( fnGetParameter( "Maximum" ) )	// valMax 查询
   

2. 利用 Value.NativeQuery 函数将参数传递到 SQL 查询中，代码如下：

   let
       Source = Sql.Database( "xlgdemos.database.windows.net", "AdventureWorks"),	// 连接到数据库
       Return = Value.NativeQuery( Source,
           "SELECT SalesOrderID, OrderQty,ProductID, UnitPrice, LineTotal 
           FROM [SalesLT].[SalesOrderDetail] 
           WHERE LineTotal >= @Minimum AND LineTotal <= @Maximum",
           [Minimum=valMin, Maximum=valMax] 
       )
   in
       Return
   

• Value.NativeQuery 的第二个参数是 SQL 查询语句；第三个参数类似于一个隐含的 DECLARE 语句，定义了 SQL 查询中使用的变量及其值；
• @Minimum 和 @Maximum 是 SQL 查询中的参数占位符
• 这里先通过valMin和valMax查询分配值，然后通过@Minimum和@Maximum变量传递到 SQL 语句中

  使用 Value.NativeQuery 函数时，系统会提示用户批准将发送到数据库的每个唯一的 SQL 查询实例(出于安全考虑）。虽然可以禁用此提示，但建议在执行此操作之前查看【全局】【安全性】部分。

查询利用动态构建的 SQL 语句返回其结果

1. 推荐优先使用 Power Query 界面筛选 ，让引擎优化查询（查询折叠），避免动态 SQL 的问题。

2. Value.NativeQuery 的适用于必须动态生成 SQL 时 ，如调用存储过程或复杂条件查询。 支持参数化查询，避免 SQL 注入风险。

3. 安全性提示 ： 每次修改 SQL 语句时，Power Query 会要求重新批准查询。 可在 【全局选项】→【安全性】 中禁用提示，但需谨慎评估风险。

4. 错误排查 ：如果直接在同一个查询中计算参数并传递（如示例文件中的 NativeQuery-Flat），仍可能触发防火墙错误。 务必 拆分参数查询和 SQL 查询，确保参数通过独立查询获取。

19.3.5 最佳实践

1. 优先在单一数据源内完成查询折叠 ： 减少跨隐私级别数据源的合并操作，降低错误风险。

2. 性能优化：仅在必要时禁用隐私检查，优先优化查询逻辑（如减少跨源操作）

  Power Query 的 隐私级别 是一种数据安全机制，通过限制不同敏感级别的数据源之间的交互，防止查询折叠过程中意外泄露敏感信息。正确配置隐私级别，既能利用查询折叠优化性能，又能确保符合数据安全策略。