Power Query 真经 - 第 9 章 - 批量合并文件

合并来自多个文件数据的传统方法是极其繁琐和容易出错的。每个文件都需要经历导入、转换、复制和粘贴的过程。根据转换数据量的大小和复杂程度、文件的数量以及解决方案运行的时长，这些问题可能形成可怕的积累效应。

前面章节已经看到使用 Power Query 后不再需要复制/粘贴，尽管它能够逐一导入和追加文件，但还是仍然有一些不完美的问题要应对。

1. 手动导入多个文件是很麻烦的。

2. 手动重复复杂的转换步骤很容易出错。

幸好，Power Query 也有办法来解决这两个问题的。

9.1 示例文件背景介绍

在这一章中，将研究如何为一家制造公司【导入】、【逆透视】和【追加】一系列的季度零件需求数据。生产区域每季度提交一份以其区域命名的数据报告，这些数据报告被存储在一个文件夹中，结构如图9-1所示。

图9-1 每个季度有四个文件，包含在“第 09 章 示例文件\Source Data”文件夹中

在每个工作簿中都有一个名为“Forecast”的工作表，其中包含如图9-2所示透视的数据结构。这里最大的问题是，这个数据的格式像一个 Excel 表格，但它实际上只是一个区域，尽管文件中也存在另一个名为“Parts”的表格。

图9-2 在“2019 Q1\East.xlsx”工作簿的“Forecast”工作表数据

目标是创建一个可刷新的自动化解决方案，以如图9-3所示的格式返回数据。

图9-3 被要求生成的表

这将很棘手，因为此时面临以下问题。

1. 这些文件都存储在“第 09 章 示例文件/Source Files”文件夹的子文件夹中。

2. 每个文件的内容需要【逆透视】才能被【追加】。

3. 不是所有的区域都会生产相同的产品，所以文件的列数也不相同。

4. 文件名中的区域名称必须被保留。

5. 需要从子文件夹名称中保留日期格式（例如“2019 Q4”）。

6. 当以后添加一个新的子文件夹时，用户需要能够刷新解决方案。

然而，即使有这些挑战，用户最后也会发现 Power Query 可以胜任这项任务。

9.1 过程概述

在深入探讨构建解决方案的结构之前，需要快速浏览一下 Power Query 如何处理这项任务。

9.2.1 合并文件的标准流程

合并文件的过程遵循五步标准模式。

1. 步骤 0：连接到文件夹。

2. 步骤 1：筛选文件。

3. 步骤 2：合并文件。

4. 步骤 3：对示例转换文件进行数据清洗。

5. 步骤 4：通过主查询进行数据清洗。

在这一章中，将通过这个标准模式的每个部分，向用户展示它是如何工作的，以及为什么这些步骤很重要。然而，在这之前，理解将要构建的内容体系结构是很重要的。

9.2.2 合并文件的通用架构

让许多用户感到害怕的事情之一是，Power Query 并不只是通过使用一个单一的查询来合并文件。相反，当单击合并文件按钮时，它会要求用户选择一个【示例文件】，然后创建四个新的查询来完成这项工作。如果用户没有发现这点，这可能会让用户有点迷惑。

假设已经创建了一个名为“FilesList”的特定查询来显示想合并的文件，以及一个包含合并文件的结果（将在本章后面讨论）“Master Query”，查询体系结构最终将看起来如图9-4所示。

图9-4 当合并文件时，将创建四个新的查询（显示在下半部分）

虽然每个新查询都是这个过程中的关键组成部分，但其中三个查询将被放在一个【帮助程序查询】文件夹中，用户不需要创建它们。它们很容易被识别为以下内容。

1. 它们将储存在一个名为【帮助程序查询】的文件夹中。

2. 它们用一个看起来不像表格的图标来表示。

如果看上面的图表，会注意到列出的三个查询显示了一个表格图标。

1. 文件列表：这个查询只包含用户希望合并的文件列表。正如在后面将了解到的，这可以是一个独立的查询，也可以是主查询的一部分。无论采取哪种方法，这都是合并文件的地方。

2. 转换示例：在合并步骤中，用户会被要求选择一个文件作为示例文件，这个查询将【引用】该示例，向用户显示选择的文件内容。它的目的是让用户在将所有文件追加到单个表之前，对单个文件执行数据转换（用户在这里执行的步骤会自动在转换函数中自动照搬运行并合并，以便它们可以应用于文件夹中的所有文件）。

3. 主查询：这个查询的目的是将“FilesList”（步骤或查询）中包含的每个文件，传递给转换函数（基于【转换示例文件】中的步骤），并返回每个文件的重塑结果。然后，扩展这些表格，将它们【追加】到一个长的数据表中，并允许用户在必要时做进一步的转换。

这听起来可能有点复杂，但正如看到的，它提供了令人难以置信的灵活性，而且一旦理解了它是如何合并在一起的，实际上使用起来非常简单。最重要的是，这种设置遵循如下流程。

1. 在表被添加之前进行数据转换。

2. 在表被添加后进行数据转换。

3. 保留文件属性，包括名称或日期。

【注意】

这种方法不仅适用于 Excel 文件。它适用于 Power Query 中的任何其他文件类型的连接器（CSV、TXT、PDF 文件和更多文件类型）。

现在开始，把这个概述应用于示例数据。

9.3 步骤 0：连接到文件夹

需要做的第一件事是连接到数据文件夹。如果还记得第一章的内容，每次连接到一个数据源时，Power Query 都要经历如图9-5所示的四个不同的步骤。

图9-5 连接到数据源

从设置开始，在这里选择和配置需要使用的连接器，来连接到相应的文件夹。接下来，Power Query 会检查用户是否需要对数据源进行验证（如果需要，会提示用户进行验证）。在验证了用户可以访问数据源之后，用户会得到初始预览窗口，此时用户可以选择【加载数据】，或者在加载前到 Power Query 编辑器中重新塑造数据。

这里再次提到这一点的原因，以及本标准流程有步骤 0 的全部原因是，实际上有多个不同的连接器可以用来从一个文件夹中读取数据，这取决于用户存放文件的系统。虽然根据系统的类型（Windows、SharePoint、Azure），入口点是不同的，但一旦用户进入数据预览，为合并文件而建立的解决方案都利用相同的模式，如表9-1所示。

表9-1 任何【从文件夹】风格的解决方案背后信息

因此，一旦完成了特定数据源的配置和身份验证步骤，会发现本章中显示的步骤可以应用于各种不同的数据源。

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

到目前为止，最容易创建【从文件夹】的场景是，将文件组合在本地 PC 或映射网络驱动器的文件夹中。由于 Windows 已经对文件夹访问进行了验证，所以不会提示用户填写任何凭据。

在本章中，将使用这种方法来连接到“第 09 章 示例文件/Source Data”文件夹。按如下步骤即可做到这一点。

1. 创建一个新的查询，【来自文件】【从文件夹】。

2. 浏览并选择【文件夹名称】（“第 09 章 示例文件\Source Data”）【打开】。

此时，会弹出预览窗口，不仅显示用户选择的文件夹中的所有文件，而且还显示任何子文件夹中的文件，如图9-6所示。

图9-6 显示文件夹（和子文件夹）中所有文件的预览窗口

需要认识到的重要一点是，这个视图遵循前面显示的模式，所有列出的列的顺序完全相同。

只要连接到一个本地文件夹就行了。剩下的唯一选择是确定加载数据的位置。由于要控制输出，将选择通过【转换数据】按钮来编辑查询。

【注意】

【从文件夹】连接器可用于从个人电脑上的本地文件夹、映射的网络驱动器、甚至从“UNC”文件路径中读取数据。

9.3.2 连接到 SharePoint 文件夹

如果用户将数据存储在 SharePoint 站点中，应该知道，有如下两个选项可以连接到数据。

1. 如果将该文件夹同步到电脑上，则可以使用前面描述的本地文件夹连接器。

2. 如果连接到云端托管版本的 SharePoint 文件夹，则可以用一个 SharePoint 专用连接器来实现。

与连接本地同步版本的文件夹相比，SharePoint 连接器的运行速度较慢，因为在执行查询时需要下载文件，但不需要将文件存储在电脑上。按如下步骤来设置它。

1. 创建一个新的查询【来自文件】【从 SharePoint 文件夹】。

2. 输入【站点 URL】的根目录（不是本地库或文件夹路径）。

挑战在于，与使用本地文件夹不同，用户不能直接连接到一个子文件夹。而是必须连接到根目录，然后向下查找，直到找到需要的文件夹。那么，如何找到这个根目录呢？

最简单的方法是通过用户喜爱的网络浏览器登录 SharePoint 站点，然后检查 URL ，如图9-7所示。将单词“Forms”左边的第二个“/”开始前面的 URL 复制到【站点 URL】。

图9-7 提取 SharePoint 网址的根目录

因此，如果域名是 https://monkey.sharepoint.com ，那么将连接到 https://monkey.sharepoint.com/sites/projects。

【注意】

如果用户公司使用的是 Microsoft 365 ，SharePoint 域名将采用 .sharepoint.com 的格式。如果用户 SharePoint 是由自己的 IT 部门管理，它可以是任何东西。

确认根目录后，如果用户以前从未连接到该网，则会提示用户进行身份验证。此时，用户需要用适当的凭证登录，如图9-8所示。

图9-8 连接到 Office 365 上的 SharePoint

成功浏览此对话框的关键是确保选择正确的账户类型进行登录。由于 SharePoint 的配置方式不同，无法完全预测用户需要使用哪种认证方式，但以下内容应有助于提高首次选择正确登录方法的几率。

1. 如果 SharePoint 托管在 Office 365 上，则必须选择微软账户，用于 Office365 的电子邮件登录。

2. 如果 SharePoint 是由 IT 部门托管，用户甚至都不需要登录就可以匿名访问。当然，如果这不起作用，则需要使用 Windows 凭据登录。

【注意】

如果用户的公司是使用 Office 365 且域名是以 sharepoint.com 结尾的，那么选择微软帐户，并输入常规工作电子邮件凭据。

【警告】

凭据会存储在用户电脑上的一个文件中，所以选择错误的凭据会让用户进入一个【无法连接】状态。要管理或更改凭据，需要进入【数据】【获取数据】【数据源设置】【全局权限】。选择它并选择【清除权限】。然后在下次尝试连接时，会被再次提示输入【站点 URL】。

一旦用户凭据通过验证，Power Query 将尝试连接到文件夹。如果输入的是一个有效的 URL，它将展示预览窗口。但如果没有输入 URL 或者提供的 URL 不是根路径，那么将会得到一个错误信息，并需要再次尝试。

【注意】

连接到 SharePoint 还有一个细微的差别，那就是人们实际上也可以在 SharePoint 域的根中存储文件。要连接到这些文件，仍然要使用从 SharePoint 文件夹连接器，但要输入 https://（没有尾部的文件夹）的 URL。请注意，这并不会枚举各站点的内部数据。

9.3.3 连接到 OneDrive for Business

OneDrive for Business 的最大秘密是，它实际上是一个在 SharePoint 上运行的个人网站。这意味着，用户在连接 OneDrive for Business 的文件夹时，与连接 SharePoint 站点时有相同的选择：通过【来自文件】选项（如果它同步到用户的桌面），或通过【来自 SharePoint 文件夹】（如果没有的话）。

诀窍在于理解要连接到正确的 URL，因为它与 SharePoint【站点URL】不同。当通过【来自 SharePoint 文件夹】选项进行连接时，用户需要输入以下格式的 URL：

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

用户还应知道，电子邮件地址中的“.”和“@”字符都将被替换为下划线字符。

【注意】

如果用户公司使用 Microsoft 365，那么 SharePoint 域名将采用“-my.sharepoint.com”的格式，但如果用户的 SharePoint 是由 IT 部门管理，它可能是任何东西。到目前为止，获得正确 URL 的最简单方法是在网络浏览器中登录 OneDrive for Business，并将所有内容复制到电子邮件地址的末尾，因为这将为用户获取正确 URL。

9.3.4 连接到其他文件系统

虽然已经介绍了最常见的连接器，但也有其他连接器在连接时返回相同的文件夹模式，包括（但不限于）Blob Storage、Azure Data Lake Gen 1 和 Azure Data Lake Gen 2。每个连接器都需要通过自己的特定 URL 进行连接，并要求进行身份验证，但一旦完成，就会进入与前面列出的那些连接器相同的界面。

但是，如果用户在不同的在线存储系统中存储文件呢？也许把文件保存在 Google Drive、DropBox、Box、Onedrive（个人版），或者其他几十个存储的解决方案中的任何一个。即使不存在与该文件系统的特定连接器，只要供应商提供一个应用程序，可以将文件同步到用户 PC 上的本地副本，用户就可以通过【从文件夹】连接器连接到这些文件。

9.4 步骤 1：筛选文件

在选择适当的步骤 1 并在连接到数据文件夹后，可以查看到该文件夹下以及任何子文件夹中的所有文件的列表。问题是存储在这个文件夹中的任何文件都将被包括在内，但 Power Query 一次只能合并一种类型的文件。

为了防止由于合并多种文件类型而产生的错误，需要确保将文件列表限制为单一的文件类型。即使用户在文件夹中只看到一种类型的文件，也应该这样做，因为用户永远不知道会计部的乔伊（Joey）什么时候会决定把他的 MP3 收藏存储和需要合并的 Excel 文件存放在同一个文件夹里。更大的问题是，Power Query 还会区分文字的大小写，所以如果将列表限制为“.xlsx”文件，当乔伊将文件保存为“.XLSX”时，它们会将被筛选掉。这需要更好的可行性，毕竟乔伊很可能就是自己部门的同事。

9.4.1 标准模式

步骤 1 是关于筛选想合并的文件，并在将来针对不相关的文件对解决方案进行校对。它可以被提炼成一个标准模式，看起来如下所示。

1. 筛选到适当的子文件夹级别（如有必要）。

2. 将扩展名转换为小写字母。

3. 将扩展名筛选限定为同一种文件类型。

4. 在名称中通过筛选排除临时文件（以“~”开头的文件名）。

5. 执行任何需要的额外筛选。

6. 可选：将查询重命名为“FilesList”，并将其作为一个仅限连接的加载（无需实际加载数据）。

接下来更详细地探讨这个问题。

9.4.2 应用于示例场景

当使用本地【从文件夹】连接器连接到一个文件夹时，能够直接连接到一个特定的子文件夹。这是很方便的，因为用户通常可以直接输入目标文件夹的直接路径。另一方面，如果使用的是一个从 SharePoint 或 Azure 中提取数据的连接器，就没有这么幸运了，需要向下筛选到相应的子文件夹。这可以通过筛选【文件夹路径】列来完成，但这里有一点需要注意：每个文件的整个文件夹路径都包含在这些单元格中。虽然在本地文件系统中很容易阅读，但在 SharePoint 解决方案中，每个文件名前面都有整个网站的 URL。为了解决这个问题，本书建议用户采取以下方法来筛选文件列表，只保留所需的子文件夹。

1. 右击“Folder Path”【替换值】。

2. 【要查找的值】“<原始文件夹路径或站点 URL>”加上文件夹分隔符。

3. 【替换为】什么都不写。

因此，在本地文件夹解决方案的情况下【追加】如下路径数据：

“C:\MYD\第 09 章 示例文件\Source Data”。

把下面的内容替换成空（【替换为】什么都不写）：

“C:\MYD\第 09 章 示例文件\Source Data\”。

单击【确定】后结果将如图9-9所示。

图9-9 在“Folder Path”列现在只显示子文件夹名称

如果用户连接的是一个本地文件夹，并且需要在子文件夹级别进行连接，不用担心，根本不需要这样做。但如果用户是通过 SharePoint、OneDrive 或 Azure 工作，这个技巧可以更容易看到和筛选到适当的子文件夹结构。事实上，对于更深层的文件路径或有大量文件的场景，用户可能要重复这个过程几次，以便进入需要的子文件夹。

1. 将“当前”文件夹路径替换为空（【替换为】什么都不写）。

2. 筛选到下一个子文件夹级别。

3. 为了找到正确的文件夹，可以多次转到 1。

一旦下钻到包含用户预期文件的特定文件夹或子文件夹，需要确保将列表限制为只有一种文件类型。在这个过程中，需要确保永远不会被大小写敏感性问题所困扰，而且筛选掉临时文件也是一个很好的做法，特别是如果正在打开着 Excel 文件。按如下步骤即可做到这一点。

1. 右击“Extension”列【转换】【小写】。

2. 筛选“Extension”列【文本过滤器】【等于】。

3. 单击【高级】。

4. 【柱】选择“Extension”，【运算符】选择【等于】，【值】输入“.xlsx”。

5. 【柱】选择“Name”，【运算符】选择【开头不是】，【值】输入“~”。

此时结果如图9-10所示。

图9-10 通过限制只有有效的 xlsx 文件，来验证解决方案是可行的

【注意】

在本地硬盘上打开 Excel 文件时，会在文件夹中创建一个以“~”字符为开头的第二个副本。当 Excel 关闭时，该文件会自动消失，但在崩溃的情况下，这并不总是这样的。通过筛选删除以“~”开头的文件，可以避免这些文件。如果不合并 Excel 文件，可以跳过这一步，但无论如何，做这一步没有任何影响或问题。

此时，应该仔细检查列表中保留的文件。为了合并这些文件，它们不仅需要有相同的文件类型，而且必须有一致的内部结构。如果仍然有混合的文件（如销售报告、财务报表和预算准备文件等），可能需要在这个阶段做一些额外的筛选，来限制列表中只有那些想要合并的文件，并且具有一致结构。

【注意】

请记住，用户可以根据需要对文件名、文件夹、甚至日期进行筛选。然而，到目前为止，确保只包括相关文件的最简单方法是事先建立一个清晰的文件夹结构，以可预测和可筛选的方式收集文件。

对于这个场景，现在处于一个很好的情况，查看任意 Excel 文件的列表。尽管这些文件仍在主源数据文件夹的子文件夹中，但也可以这样做，并继续下一步。

本节的最后一步是可选的。

1. 将查询重命名为“FilesList”。

2. 将查询加载为【仅限连接】查询。

这些步骤是 Ken 更喜欢构建【从文件夹】方案的方式，因为它提供了以下两个好处。

1. 它构建了一个非常明显的结构，在那里可以去查看哪些文件被合并，而不必通过查询的一部分来确定细节。

2. 它只在解决方案中硬编码一次文件路径。

虽然解决方案将使用这种方法进行说明，但请注意，可以跳过它，继续进行下一步，无论如何一切都会顺利进行，如图9-11所示。

图9-11 将“FilesList”查询作为“暂存”查询加载

9.5 步骤 2：合并文件

随着对文件列表的整理，现在是时候对文件进行合并了。

9.5.1 标准模式

该过程的步骤 2 包括以下操作。

1. 可选：【引用】“FilesList”查询来创建主查询。

2. 重命名主查询。

3. 单击【合并文件】按钮。

4. 选择【示例文件】。

此时，Power Query 将执行它的魔法，创建四个新的查询，并在主查询中添加一系列步骤。

9.5.2 应用于示例场景

强烈建议用户在触发【合并文件】过程之前，一定要重新命名主查询，因为主查询的名称可能会被用于一些创建的文件夹和查询的名称中。（如果用户最终在同一个解决方案中合并了多个不同的文件夹，这将使事情更容易被管理）这里的关键是提供一个描述性的名字，不要太长，而且是用户乐意加载到工作表或数据模型中的。在示例场景中，试图想出一个需要订购的零件清单，所以像“Parts Required”或“Parts Order”这样的名称可能是有意义的。在这个示例中，将保持简短和干净，把主查询称为“Orders”。

那么，到底哪个查询是主查询？这取决于用户是否决定使用创建专用“FilesList”暂存查询的可选步骤，步骤如图9-12所示。

1. 如果加载了“FilesList”查询作为暂存查询，主查询将被称为“FilesList (2)”，并通过【引用】“FilesList”查询（右击【引用】）来创建。

2. 如果没有把“FilesList”查询作为一个暂存查询加载，那么“FilesList”查询就是主查询，一旦确定哪个查询是主查询，就可以开始【合并文件】的过程。

3. 选择主查询并将其重命名为“Orders”。

4. 单击“Content”列顶部的合并文件（双箭头）按钮。

图9-12合并一个 Excel 文件中的文件夹

单击【合并文件】按钮（上图中的 #1 ），会弹出一个预览窗口。在这个预览中，用户会被要求选择作为【示例文件】的文件（上图中的 #2 ）。一旦选择【第一个文件】，还会看到【示例文件】中的全部内容（上图中的 #3 ）和所选择对象的数据预览（上图中的 #4 ）。

【注意】

使用单独的“FilesList”查询的一个缺点是，只能选择【第一个文件】作为这里的样本文件选项。如果跳过整个这个步骤，文件夹中的任何文件都可以被选为样本文件使用。当然，这不是什么问题，用户会发现还是有技巧使用任何文件作为样本文件，只需要返回到“FilesList”查询并进行排序或筛选，来获得想要的文件作为【第一个文件】，再将它作为样本文件即可。

在这些工作簿的示例中，会注意到它们中有一个名为“Parts”的表格，以及一个“Forecast”和“Matrix”工作表。不幸的是，虽然“Parts”表很好很干净，但这实际上是作为“Forecast”表上所包含的数据范围的查询表。因此，看起来需要导入不太整洁的数据，即“Forecast”工作表，并执行一些手动清理，现在就开始。

选择“Forecast”工作表【确定】。

Power Query 会计算一小段时间，然后合并文件，结果将如图9-13所示。

图9-13 突然间，主查询中出现了四个新查询和五个新步骤

这里有很多需要注意的地方。

实际上，这里发生的事情是，Power Query 创建了一个“帮助程序查询”的集合，然后在主查询中添加步骤来使用用它们。在左边，会看到一个叫做“帮助程序查询”的文件夹，它包含一个“参数”、“示例文件”和“转换文件”功能。在这下面，还有一个非常重要的“转换示例文件”。

用户还应该注意，查询预览仍然停留在主查询上，可以进一步在此处窗口进行合并文件操作。在本章的步骤 4 中，将进一步解释右边的步骤，但要认识到的重要事情是，Power Query 基本上已经提取了每个文件的“Forecast”内容，并将它们追加到后面。现在，如果数据已经处于纵向追加的目标状态，就算完成了，但是如果看一下图片中显示的第一个和第二个文件，会注意到 Power Query 实际上追加了两个透视表结构的数据，而且每个数据集的标题都不同。

一旦阅读并掌握了整本书的内容，就会意识到，用一个查询来处理这样的透视表结构罗列的数据集其实也是可能的。话虽如此，但这样做太过于复杂。如果能在追加数据之前对这些数据进行【逆透视】，从而避免那种令人头痛的问题，那不是很好吗？好消息是，可以做到。更好的消息是，当利用这些辅助查询时，它是非常容易的。

【注意】

专业提示：虽然看起来在合并步骤中只能访问每个文件中的一个对象，但实际上并非如此。如果需要合并多个工作簿中的多个工作表，或者是每个工作簿中的第二个工作表，而且的确可以做到。只要选择【转换示例文件】的“Source”步骤，这将提取一个列出所有工作簿对象的表格，类似于第 6 章和第 8 章中所示的 CurrentWorkbook 的示例。

9.6 步骤 3：转换示例文件

在触发原始合并之后，要做的下一件事是清洗数据。这一步的总体目标是做以下工作，来创建一个规范化的数据集。

1. 将数据拆分成若干列。

2. 从数据集中删除垃圾行和垃圾列。

3. 为分析而清洗数据。

当然，每个数据集需要处理的方式都不同，但最终的结果是相同的：将其重塑为一个具有描述性标题的数据表，并且每行和每列的交叉点有一个数据点。

9.6.1 使用转换示例文件的原因

在这个扩展的查询集合中，有如下两个地方用户可以重塑数据。

1. “转换示例文件”。

2. 主查询（Orders）。

本书鼓励用户尽可能多地在“转换示例文件”中进行数据清洗，而不是在主查询中。“转换示例文件”的主要好处是，用户可以根据一个“示例文件”构建查询，从而使数据清洗更加容易。完全避免了追加数据集的混乱，因为在数据被追加之前，转换会被应用到数据集上。在像透视、逆透视或分组这样的操作中，这可能会对减低复杂性产生巨大影响。

更棒的是，当用户在“转换示例文件”中执行数据清洗时，这些步骤都会同步到“转换文件”函数中。然后在追加之前，对文件列表中的所有其他文件调用这个函数，并且它会自动神奇地执行。

【注意】

经验法则是尽可能地使用“转换示例文件”。

9.6.2 使用转换示例文件的方法

使用“转换示例文件”来清洗其中一个工作表。单击【查询】导航窗格中的“转换示例文件”查询，会被带入如图9-14所示的视图。

图9-14 基于“FilesList”查询的第一个文件“转换示例文件”的所有 13 行

当用户第一次转到“转换示例文件”时，理解 Power Query 自动创建的步骤很重要。在这种情况下，应用步骤如下所示。

1. Source：包含 Excel 文件中所有可用对象的原始表。

2. Navigation：导航到表示“Forecast”工作表的表格中去。

3. Promoted Headers：将第一行提升为标题。

在仔细观察数据时，被提升标题的那一行似乎并没有什么价值，接下来的五行数据也是如此。用户想要的列标题实际上包含在文件的第七行中（假设第一行没有被提升为标题）。按如下解决这个问题。

1. 删除“Promoted Headers”步骤。

2. 进入【主页】【删除行】【删除最前面几行】“6”。

3. 转到【主页】【将第一行用作标题】。

此时，Power Query 做了一件非常危险的事情如图9-15所示。发现它了吗？

图9-15 其中“Change Type”步骤不是用户自己构建的

每当一行被提升到标题时，Power Query 都会帮助用户自动判别并转换数据类型。虽然这很有用，但它也将列的名称硬编码到步骤中。问题出在哪里？在本章开头的案例背景中提到过这个问题：并非所有的区域都产生相同的产品，所以列的数量因文件而不同。

那么，当用户遇到另一个不生产产品“A”、“B”或“C”的区域时会发生什么？如图9-16所示的“North”分部，将发生步骤级错误。

图9-16 了解数据将有助于在合并文件时预测和避免问题

【注意】

在更改“转换示例文件”时要小心，特别是在文件之间列名可能不同的情况下。只有在确保在所有情况下都会存在的同样列名时才能硬编码。

事实上，在这个阶段，并不需要声明数据类型，而需要继续准备数据，以便进行【逆透视】，但要以安全的方式。

1. 删除“Changed Type”步骤。

2. 筛选“Part Nbr”列，取消勾选“Total”。

3. 找到“Total”列并删除。

4. 右击“Part Nbr”列【逆透视其他列】。

结果将如图9-17所示。

图9-17 【逆透视】的数据集

【注意】

等一下，刚刚在删除“Total”列的时候，不是已经把它的名字硬编码了吗？的确，是这样做了。但是这样做安全吗？这里的答案有点微妙，但既然它似乎在东部和北部的数据范围内都出现了，那么也许可以假设它将出现在所有的数据集中。如果没有，我们可以通过将它留在数据中进行【逆透视】，然后从“属性”列中筛选掉“Total”来解决这个问题，即使那时不存在“Total”，也不会产生任何错误的。

随着数据被正确的【逆透视】，此时可以更改列名，设置数据类型，如下所示即可。

1. 重命名“属性”列为“Product”。

2. 重命名“值”列为“Units”。

3. 选择所有列【转换】【检测数据类型】。

此时结果将如图9-18所示。

图9-18 为“示例文件”生成的 36 行最终输出的一部分

忽略“Forecast”硬编码列名的潜在问题所带来的挑战，当把它保持在单个文件的范围内时，这是一个相当直接的【逆透视】工作。如果试图在主查询中这样做，那就会复杂得多了。

【警告】

如果在运行合并时未能预料到问题，并在其中一个文件中出现步骤级错误，会发生什么？当然，用户需要调试它，回到“FilesList”并插入临时步骤，保留前“x”行或删除前“x”行，直到用户找到是哪个查询导致错误。一旦把它作为“FilesList”中的第一个查询，就可以在“转换示例文件”中调试它，看看哪里出了问题。

9.7 步骤 4：通过主查询规范数据

现在，回到主查询，看看目前的效果。当这样做时，会看到一个步骤级错误。

9.7.1 修复主查询中的错误

不幸的是，这看起来很熟悉，如图9-19所示。

图9-19 这到底是怎么回事

这个错误的根本原因是主查询的“Changed Type”步骤。还记得 Power Query 的“Promoted Headers”步骤吗？这生成了“Production Forecast”、“Column 2”等标题列，而由于 Power Query 在主查询中也硬编码了一个“Changed Type”步骤，这些列名会在这步自动使用。可以在公式栏中去掉那个列名，只将其他列名提升为标题。

这个错误非常常见，只要删除主查询中的“Changed Type”步骤就可以轻松解决。此时结果将如图9-20所示。

图9-20 步骤级错误消失

【注意】

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

9.7.2 保存文件属性

虽然“转换示例文件”在最后包含了 36 行的预览，但这里的预览窗口显示 288 行，表明它将数据转换模式应用于文件列表中的每个文件，然后将它们【追加】到一个长表中。这真是太棒了，但仍有一个问题。

提交的每个文件都属于不同的区域，但区域名称并不包含在文件本身中。相反，该文件是使用区域的名称命名的。挑战在于，似乎在这个过程中的某个地方丢失了这个名字。此外，虽然原文件包含了季度末的日期，但这些数据被保存在通过“转换示例文件”删除的前几行中。能够对这些原文件采取一些方法来解决，让每个部门都存储在一个子文件夹中，并以“yyyy-qq”格式命名。但是，在这个过程中，似乎也丢失了文件夹名称。那么如何把这些信息找回来呢？

在这一点上，回顾一下 Power Query【合并文件】时在主查询中生成的步骤是有帮助的，其中第一个步骤是“Filtered Hidden Files1”。

【注意】

如果用户选择创建一个单独的“FilesList”查询，“Filtered Hidden Files1”步骤将是第二个步骤。如果用户选择跳过这一步，它将出现在查询的后面，但将紧接在用户触发【合并文件】过程的位置之后。

下面是后续步骤的内容。

1. Filtered Hidden Files1（筛选的隐藏文件1）：添加一个筛选器，从文件列表中删除任何隐藏的文件（是的，Power Query 也会列出存储在文件夹中的隐藏文件和系统文件）。

2. Invoke Custom Function1（调用自定义函数1）：添加一个新的列，该列利用基于“转换示例文件”中的操作而生成的“转换文件”函数。这一步的作用是创建一个列，生成从每个文件转换后的表。

3. Removed Other Columns1（删除的其他列1）：此步骤删除了所有的列，除了通过调用自定义函数步骤创建的那一列。正是这一步，文件名和文件夹名消失了。

4. Expanded Table Column1（扩展的表格列1）：这个步骤扩展了通过“Invoke Custom Function 1”步骤添加的列的结果。其结果是每个表都被【追加】到一个长表中。

理解了这一点，此时将知道只需要修改“Removed Other Columns 1”步骤，来保留 Power Query 认为不需要的任何文件属性。

1. 选择“Removed Other Columns1”步骤，单击齿轮图标。

2. 勾选“Name”和“Folder Path”旁边的复选框【确定】。

此时，结果将如图9-21所示，现在已经将“Name”和“Folder Path”列恢复到数据集中。

图9-21 修改“Removed Other Columns1”步骤，使关键列重新出现

9.7.3 添加更多的步骤

现在，可以对需要应用于所有文件的操作的查询做进一步的修改。将采取的具体操作如下。

1. 选择“Expanded Table Column1”步骤（只是为了避免在下面的每个操作上都被提示插入一个新的步骤）。

2. 将“Name”列重命名为“Division”。

3. 右击“Division”列【替换值】【要查找的值】输入“.xlsx”【替换为】什么都不填，【确定】。

4. 右击“Folder Path”列【拆分列】【按分隔符】【最左侧的分隔符】【确定】。

【警告】

在拆分列时，Power Query 会自动添加一个“Changed Type”步骤。用户应该考虑一下这是否有必要。如果它可能会在将来引起问题，那么请删除它，并在加载到最终目的地之前将数据类型作为最后一步来应用。

由于“Changed Type”在这里似乎没有必要，所以将删除它，即使它不会引起任何问题。

1. 删除“Changed Type”步骤。

2. 将“Folder Path.1”列重命名为“Year”。

3. 将“Folder Path.2”列重命名为“Quarter”。

4. 右击“Quarter”列【替换值】【要查找的值】输入“\”，【替换为】什么都不输入【确定】。

5. 选择所有列【转换】【检测数据类型】。

此时，主查询已经完成，对数据进行【逆透视】并【追加】，同时保留了文件名和文件夹的部分内容。来增加分析所需的关键元素，如图9-22所示。

图9-22 【逆透视】数据集的前四列是由文件夹和文件名驱动的

【警告】

数据类型永远不会从“转换示例文件”中继承。在加载到工作表或数据模型之前，一定要确保将更改数据类型作为查询的最后一步来设置。

随着数据的成功转换，现在是时候加载它，以便用户可以使用它来做报告。这一次将把它加载到数据模型中，如下步骤所示。

1. 在 Power BI 中，只需单击【关闭并应用】。

2. 在 Excel 中，进入【主页】【关闭并上载至】，选择【仅创建连接】，同时勾选【将此数据添加到数据模型】复选框，如图9-23所示。

图9-23 加载数据到数据模型

将会注意到，尽管在一个会话中创建了多个查询，但只有主查询被加载到目的地。所有的辅助查询，包括“转示例文件”，默认情况下都是作为“暂存”查询仅保持连接的。

9.8 更新解决方案

随着数据的加载，现在可以构建一些可重复使用的商业智能。

9.8.1 使用数据

为了演示从导入到刷新的完整周期，需要使用“矩阵”或“数据透视表”建立一个快速报告。创建这个对象的步骤将取决于用户使用的是哪种应用程序。

如果使用的是 Power BI。

在【报表】页面，进入【可视化】面板【矩阵】。

如果使用的是 Excel。

1. 在一个空白工作表上选择 B3 【插入】【数据透视表】。

2. 选择【来自数据模型】【确定】。

一旦创建了这个对象，从右边的“Orders”表中拖动以下列，到字段区域，如下所示。

1. 值：“Units”。

2. 行：“Part Nbr”。

3. 列：“Year”，“Quarter”。

结果（在 Excel 和 Power BI 中）如图9-24所示。此时 Power BI 中展开到了季度级别来显示季度数据。

图9-24 比较 Excel 和 Power BI 的结果

9.8.2 添加新文件

现在是时候探索一下当解决方案中添加新数据时会发生什么。

如果在 Windows 资源管理器中打开“第 09 章示例文件”文件夹，会发现它不仅包含连接的示例数据文件夹；还有一个“2019 Q4”文件夹，它包含不同区域的更新数据。将该文件夹拖入“Source Data”文件夹中，这样在驱动解决方案的文件夹中就有四个季度的文件夹，如图9-25所示。

图9-25 现在是时候向解决方案添加一些新的数据了

移动文件夹后，返回解决方案并点击【刷新】。

1. Power BI：转到【主页】【刷新】。

2. Excel：转到【数据】【全部刷新】。

几秒钟后，可以看到数据结果已经包括了第四季度的数据，如图9-26所示。

图9-26数据已更新

这是多么令人难以置信，不仅可以很容易地【追加】多个文件，而且刚刚创建了一个可【刷新】的商业智能报表，当加入新的数据时，只需单击几下就可以更新文件，这就是现在的解决方案。

在这里，需要真正要认识到的是，用户可以根据接收数据的方式选择构建和更新解决方案。考虑一下如图9-27所示的图表，它显示了在更新外部文件上的解决方案时可用的灵活性和更新方法。

图9-27 更新连接到外部文件的解决方案

无论用户直接用同一文件覆盖旧文件，或者想建立一个不断增长（或滚动）的积累文件的解决方案，Power Query 都能满足这些需求。

9.8.3 只用最后 x 个文件以提升速度

尽管【从文件夹】的解决方案很神奇，但用户需要考虑，如果只是不断向源数据文件夹添加新的文件，它最终会变慢。处理一百个文件的时间要比处理十个文件的时间长，这也是合理的。特别是考虑到 Power Query 不能被配置为只更新新的或数据发生改变的文件。每次用户单击【刷新】按钮时，Power Query 都会重新加载文件夹中所有文件的所有数据。

想象一下，把以前构建的解决方案，保持运行 10 年。每年有 16 个数据文件（ 4 个区域 x 4 个季度），从 2020 年到 2030 年结束时，将会处理超过 176 个文件。现在，公平地说，这些文件是相当小的，但如果每个文件需要 5 秒钟来刷新呢？现在超过 14 分钟来刷新解决方案，这个时间比较长，会让人感觉就像永远一样。

在构建这些解决方案时，用户必须问自己的第一个问题是，是否真的需要所有这些数据。在 2030 年，真的会关心 2019 年的数据吗？如果要与前一年的数据进行比较，可能最多需要 32 个文件。那么，为什么不限制解决方案来做到这一点呢？

限制文件的秘诀是回到查询的文件列表部分，按如下步骤操作。

1. 按日期的降序对文件进行排序。

2. 使用【保留最前面几行】来保留需要的前几个文件。

诀窍实际上是要弄清楚哪一个字段要用于日期排序。在这个示例中，可以使用“Folder Path”列，因为用户是按照逻辑顺序来命名这些文件的。如果没有这样的结构，那么可能想依靠“创建日期”或“修改日期”字段中的一个。

【注意】

请记住，保存的文件数量可以在一个合理需要的任何数量之间变化。根据过去多个项目的经验，一般只保留过去 24 个滚动月的数据。

【警告】

如果用户只是把新的数据文件复制和粘贴到一个文件夹中，在排序时使用“创建日期”属性应该是安全的，但是，要注意“创建日期”字段可能比“修改日期”要新。其原因是，通过复制和粘贴创建的文件在粘贴时将被“创建”，尽管它在源文件最后一次被修改时已经被“修改”。依靠“最后修改日期”也可能是危险的，因为仅仅是打开某些文件类型就可能算是修改。

正在学习 Power Query 吗？可以加入本主题的交流群一些交流分享。

Power Query 真经连载

Power BI 终极系列课程《BI真经》

BI真经 - 让数据真正成为你的力量

扫码与精英一起讨论 Power BI，验证码：data2022
扫码与精英一起学习 Power Query，验证码：PQ真经

点击“阅读原文”进入学习中心

↙