17.1 重新创建合并文件

在本节中，将从创建 Power Query 的组合文件开始，虽然不会执行单个文件数据转换，但仍将遵循在第 9 章中介绍的 “FilesList” 方法【合并文件】。首先，打开一个空白工作簿，创建 “FilesList” 查询，如下所示。

1. 创建新查询【获取数据】【来自文件】【从文件夹】。

2. 选择 “第 17 示例文件 Source Data” 文件【打开】【转换数据】。

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

4. 筛选 “Extension” 列，【文本筛选器】【等于】【高级】按如下方式设置筛选器。

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

 4.2【且】【柱】选择 “Name” 列，【运算符】选择【不包含】，【值】输入 “~”。

1. 将查询重命名为 “FilesList” 并将其加载为【仅限连接】，接下来，创建 “Orders” 查询，就像在通用做法中所做的那样。

 5.1 右击 “FilesList” 查询【引用】，将新查询重命名为 “Orders”。

此时，应该有一个文件夹，包含所有文件的列表，并准备合并文件夹中的所有文件，如图 17-1 所示。

图 17-1 此时，可以单击【合并文件】按钮，但这有什么意思呢

【注意】

请记住，在【合并文件】之前先拆分查询，只需要通过公式栏 “= FilesList”，而不需要更复杂的 M 代码公式。

与其采取系统默认得简单方法，不如手动重建当单击【合并文件】按钮时发生的效果。这将涉及手动创建示例文件、示例文件参数、转换示例和转换函数。

17.1.1 创建示例文件

要创建示例文件，首先从 “Orders” 表开始。

1. 右击 “Content” 列中的第一个 “Binary” 文件【作为新查询添加】。

2. 将新查询重命名为 “Sample File”。

注意 “Sample File” 查询现在有两个步骤。

1. “Source” 引用 “Orders” 表。

2. “Navigation” 钻取到引用的文件。

这里的挑战是文件路径已经硬编码，这缺少灵活性不是很动态，如图 17-2 所示。

图 17-2 示例文件包含硬编码的文件路径

如果希望文件动态地引用 “Orders” 表中的第一个文件，可以使在第 16 章学到的一个技巧来实现这个目标，如图 17-3 所示。

1. 用 0 替换文件路径，这样公式就变成了 “=Source {0}[Content]”。

2. 删除 Power Query 自动创建的 “Imported Excel Workbook（导入的 Excel 工作簿）” 步骤，以便钻取到示例文件中。

图 17-3 示例文件现在动态地从 “Source” 步骤获取（“Orders” 的）第一行数据

17.1.2 创建示例文件参数

这一步并不困难，但它确实需要手动创建参数，要做到这一点需要进行如下操作，结果如图 17-4 所示。

1. 转到【主页】选项卡【管理参数】【新建参数】。

2. 在【名称】输入框输入 “Sample File Parameter”。

3. 在【类型】选择框选择【二进制】。

4. 将【默认值】和【当前值】设置为 “Sample File”。

图 17-4 配置 “Sample File” 文件参数

【注意】

这个参数的名称并不真正重要，只是需要遵循第 9 章中列出的命名规则。如果愿意的话，也可以将其作为参数 1，这样一切依然可以正常工作。

单击【确定】后，将看到一个非常简单的显示，显示参数的【当前值】是 “Sample File”，结果如图 17-5 所示。

图 17-5 预览将【引用】“Sample File”

17.1.3 创建转换示例

下一件事是创建 “Transform Sample”，它是用来构建数据转换模式的主要工具。正如前面提到的，实际上不会在 “Transform Sample” 中执行任何操作，因为这不是本章的重点，但将构建允许用户转换示例文件的框架。

要创建 “Transform Sample” 查询，请执行以下操作。

1. 右击 “Sample File Parameter”【引用】。

2. 将查询重命名为 “Transform Sample”。

此时应该注意，首先，转换仍然指向公式栏中的 “Sample File Parameter”。其次，它显示了一个表示二进制文件的图像。要下钻到文件本身，可以右击它并选择如何解析文件，如图 17-6 所示。

图 17-6 构建 “Transform Sample”

现在下钻到文件中。

1. 右击 “East.xlsx” 文件，选择【Excel 工作簿】。

这样做之后，注意 Power Query 并没有添加新步骤，而是修改 “Source” 步骤，来使用 Excel.Workbook 函数，它的作用是枚举可以使用的值列表。

1. 单击与 “Parts” 表相关的 “Table” 值（在第 3 行）。

现在，应该可以透过 “Transform Sample” 从示例文件（“East.xlsx”）中看到 “Parts” 表的内容，如图 17-7 所示。

图 17-7 “Transform Sample” 文件已经创建

显然，现在可以在这里做更多的工作，但先不要这么做。相反，要创建一个可以应用于文件夹中所有文件的转换函数。

17.1.4 创建转换函数

假设这个查询使用一个参数来驱动它的二进制文件，下一步实际上很简单。

1. 右击 “Transform Sample” 查询【创建函数】。

2. 在【函数名称】下输入 “Transform Function” 后单击【确定】。

3. 可以选择将 “Sample File” 拖动到新创建的 “Transform Function” 文件夹中。

无论是否相信，现在已经重新创建了 Power Query 在单击【合并文件】按钮时生成的所有组件（只是还没有实际使用它们），如图 17-8 所示。

图 17-8 所有组件都已经成功创建

17.1.5 调用转换函数

现在是证明一切都已正确连接的时候了。

1. 选择 “Orders” 查询，转到【主页】选项卡，选择【选择列】下的【选择列】勾选 “Content” 和 “Name” 复选框【确定】。

2. 转到【添加列】【调用自定义函数】选择 “Transform Function”【确定】。

3. 预览第二个表（与 “North.xlsx” 相关的转换）。

此时应该可以认识到，“Transform Function” 不仅对 “East.xlsx” 文件有效，同时对 “North.xlsx” 文件也有效，如图 17-9 所示。

图 17-9 “Transform Function” 似乎正在提取每个文件的详细信息

17.1.6 更新转换函数

到目前为止，一切都很好，但这实际上与 Power Query 的神奇的【合并文件】按钮连接方式相同吗？换句话说，如果更新 “Transform Sample”，它是否会更新 “Transform Function”？

1. 选择 “Transform Sample”。

2. 右击 “Products” 列【逆透视其他列】。

3. 将 “属性” 列重命名为 “Part”，将 “值” 重命名为 “Units”。

4. 返回 “Orders” 表。

5. 再次预览 “North” 文件的结果。

正如所看到的，所做的更改正在顺利完成，如图 17-10 所示。

图 17-10 更改已经完美地实现了

知道了这一点，可以扩展数据以进行加载。

1. 展开 “Transform Function” 列。

2. 删除 “Content” 列。

3. 设置每列的数据类型。

这个过程的最后一步是加载数据，以便可以使用它，但要注意这里有一个潜在的陷阱。

在这个环节中创建了五个新查询，但没有选择加载目的地。在 Power BI 中，这个问题很容易解决，只需在提交之前确保所有查询都未选中【启用加载】属性即可。但在 Excel 中呢？当选择加载查询时，只能选择一个目的地，如果不小心选择加载到工作表或数据模型，可能会得到一个错误，提示无法加载 “Sample File”（因为它是二进制值）。

【注意】

在 Excel 中，强烈建议【关闭并上载至】【仅创建连接】，并且保存所有查询后，再将 “Orders” 表的加载目标更改为加载到所需的目的地。

17.1.7 观察到的规律

关于刚刚完成的工作，应该认识到的第一件事是，【合并文件】按钮本质上是一个构建所有必需组件的宏。点击一个按钮，魔术就发生了，这太棒了。然而，这个自动化过程的问题是，它可能会导致用户无法观察到需要如何设置这些步骤才能工作。此后将研究如何构建自己的函数，但在实现之前，需要强调一些东西。

1. “Transform Sample” 是完全可编辑的，并为 “Transform Function” 提供了回写功能。这意味着可以用户向 “Transform Sample” 添加或删除额外的参数，这些参数将被合并到 “Transform Function” 中。

2. 虽然 “Transform Sample” 可用于驱动 “Transform Function”，但 “Transform Function” 实际上并不依赖于 “Transform Sample”。如果愿意，实际上可以删除 “Transform Sample“，并且不会对查询的【刷新】功能产生任何影响。当然，如果这样做，将失去通过用户界面和【应用的步骤】窗口轻松更新函数的能力。

3. 并不是每个函数都需要像 “Sample File” 那样的动态示例。事实上，如果用户试图提供一个自己的 “Sample File”，就会很容易触发隐私和 “Formula.Firewall” 错误。

4. 不要在试图使参数动态时犹豫不决。示例值和当前值仅在预览窗口中用于调试转换示例。在运行时，它们将填充传递给它们的任何内容。

5. 不要觉得必须使用一个参数来驱动自定义函数。自定义函数可以依赖查询或参数来驱动其逻辑，或者这两个元素都不需要用。

【注意】

自定义函数功能可以非常强大，只是不要认为它们必须按照 “合并文件” 体验中使用的完全相同的方法工作。

17.2 使用参数构建自定义函数

在本例中，将看到一个相当常见的场景：某人对单个文本文件构建了一个转换，认为这是一个一次性工作。但现在意识到，他们需要对文件夹中的每个文件都应用相同的转换。他们不想从头开始重建整个解决方案，而是想对文件进行改造，将原来的查询变成一个函数，然后使用该函数导入和转换每个文件。

理想情况下，用户会希望能有一个转换示例查询以及转换函数。但最重要的是，“Timesheet” 查询已经被加载到工作表中，业务逻辑是基于该表的。用户不能破坏它，因此这个查询必须保持为加载最终数据的查询。打开 “第 17 章 示例文件 Retrofitting-Begin.xlsx” 文件，其中包含如下两个查询。

1. Timesheet：将重塑 “Timesheet” 子文件夹中包含的 “2021-03-14.txt” 文件。

2. FilesList：包含一个简单的查询，列出 “TimeSheets” 子文件夹中的所有文件，返回一个简单的合并文件路径和名称列表，如图 17-11 所示。

图 17-11 正在从 “FileList” 查询复制第一个值

【警告】

请记住，此时 “Timesheet” 和 “FilesList” 查询中的文件路径都是硬编码的。请确保将示例文件更新到本地电脑上的地址，否则尝试按照示例进行操作时，会出现错误。

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

1. 创建一个 “FilePath” 参数。

2. 创建新的 “Timesheet Transform”（基于原始查询）来利用该【参数】。

3. 创建新的 “Timesheet Function”。

4. 修改原始 “Timesheet” 查询以调用新的 “Timesheet Function”。

17.2.1 创建文件路径参数

首先从创建一个简单参数开始，该参数将以文本形式保存完整的文件路径，结果如图 17-12 所示。

1. 选择 “FilesList” 查询，复制第一个文件的路径。

2. 转到【主页】选显卡，选择【管理参数】单击【新建参数】。

3. 在弹出的【管理参数】对话框，【名称】输入 “FilePath”。

4. 将【类型】设置为【文本】，将文件路径粘贴到【当前值】区域。

此时结果将如图 17-12 所示。

图 17-12 配置基于文本的【参数】

【注意】

请记住，【当前值】仅用于驱动预览中的结果，可以随时通过【管理参数】对话框来更新它，以便稍后使用不同的文件。

17.2.3 创建 Timesheet 转换

创建了参数之后，现在需要基于原始 “Timesheet” 查询创建新的 “Timesheet Transform”，并将其更新为使用新的 “FilePath” 参数。实现它实际上比听起来容易得多。

1. 右击原始 “Timesheet” 查询【复制】。

2. 将 “Timesheet (2)” 查询重命名为 “Timesheet Transform”。

3. 右击 “Timesheet Transform” 的 “Source” 步骤【编辑设置】。

4. 将【文件路径】下的下拉列表更改为【参数】。

【注意】

这就是【参数】真正发挥作用的地方。Power Query 用户界面中的许多输入区域提供了从原始值更改为列和（或）【参数】的选项。不幸的是，查询中很少出现在这个选项列表，即使它们的计算结果是原始值。

如果只有一个【参数】，则会自动选择该【参数】，如果不是，则必须选择对应参数，如图 17-13 所示。

图 17-13 “Timesheet Transform” 转换查询的 “Source” 步骤

无论相信与否，要创建一个新的 “Transform Sample” 查询，只需要做这些。只要为 “FilePath”【参数】的【当前值】提供了有效路径（包括文件名和扩展名），在单击【确定】后，预览应该显示如图 17-14 所示的数据。

图 17-14 文件路径已被 “FilePath”【参数】替换，而预览仍然显示数据

【警告】

预览是否显示错误？转到【主页】选项卡，选择【管理参数】“FilePath”，并确保【当前值】包含两个 “Timesheet” 文件中的任意一个文件的完整文件路径。

17.2.4 创建 Timesheet 函数

由于 “Timesheet” 转换依赖于【参数】，因此创建转换函数非常简单。

右击 “Timesheet Transform”【创建函数】，在【函数名称】输入 “Timesheet Function”【确定】，这样就可以了。

【注意】

启用【创建函数】按钮的秘诀是在查询中至少使用一个【参数】。只要这样做，就可以从中创建新函数，并将原始函数保留为 “Transform Sample”。

17.2.5 更新 Timesheet 查询

修改解决方案的最后一部分是最棘手的。如果弄错了，它将破坏依赖于原始 “Timesheet” 表结构的业务逻辑，因此必须小心。幸运的是，新的 “Timesheet Transform” 查询包含原始表所需的列名，因此可以随时检查这些列名，来确认更新的查询是正确的。

为了更新 “Timesheet” 查询，需要执行以下操作。

1. 删除所有当前步骤。

2. 重新指向查询路径，从 “FilesList” 查询中读取。

3. 调用新 “Timesheet Function”，并删除除函数结果之外的所有列。

4. 展开 “Timesheet Function” 的结果。

5. 设置数据类型。

从理论上讲，如果按照上述操作，应该拥有与原表相同的列，并且在加载时，不会破坏针对原始 “Timesheet” 表创建的任何现有业务逻辑。

开始这个过程的最简单的方法可能是编辑和清除现有 “Timesheet” 查询的内容，需要进行如下操作。

1. 选择 “Timesheet” 表查询【视图】【高级编辑】。

2. 用下面显示的代码替换查询中的所有代码：

let

Source = FilesListin Source

【警告】

如果查询的名称为 “Files List”(包含空格)，则需要被引用为 “#"Files List"”。此外，必须确保查询名称的拼写和大小写正确，否则 Power Query 将无法识别它。

单击【完成】后，将有一个新的查询准备就绪，就像在新查询中直接引用了 “FileList” 查询一样，如图 17-15 所示。

图 17-15 “Timesheet” 查询现在引用了 “FilesList” 查询

完成以下操作。

1. 转到【添加列】【调用自定义函数】【功能查询】选择 “Timesheet Function”。

2. 在【FilePath】下选择 “FilePath”【确定】。

此时，居然报错了，如图 17-16 所示。

图 17-16 可怕的 “Formula.Firewall” 拒绝调用自定义函数

这是一个令人非常沮丧的错误消息，真希望能够避免它。

【注意】

虽然 “Formula.Firewall” 试图保护不将数据与不兼容的隐私级别相结合，但这并不是它所保护的唯一问题。即使试图通过禁用隐私设置来解决此问题，在这里也没有任何效果。

在第 19 章中会更详细地介绍 “Formula.Firewall” 错误，但是现在，能做些什么来跳过这个问题？禁用隐私并不能解决问题，那么该怎么办呢？

错误消息似乎在抱怨引用了另一个查询，因此是否可以消除该问题并在引用的查询中执行此操作？换句话说，如果尝试在 “FilesList” 查询中调用函数，它会工作吗？

1. 从 “Timesheet” 查询中删除 “Invoked Custom Function（已调用自定义函数）” 步骤。

2. 选择 “FilesList” 查询。

转到【添加列】【调用自定义函数】【功能查询】选择 “Timesheet Function”，【FilePath】选择 “FilePath”【确定】，此时结果会怎么样呢？

竟然可以正常运行而没有问题，如图 17-17 所示。

图 17-17【自定义函数】可以在 “FilesList” 查询中调用

说到底，这并不是什么大问题。通过在 “FilesList” 表中调用函数，避免了 “Formula.Firewall” 问题，现在在 “FilesList” 查询的表值中嵌套了文件列表及其内容。并没有说必须在这个查询中扩展这些表。实际上，如果需要的话，可以使用另一个查询来查看这些信息，这是很有帮助。

回到 “Timesheet” 查询并完成列的提取。

1. 选择 “Timesheet” 查询。

2. 右击 “Timesheet Function” 列【删除其他列】。

3. 展开 “Timesheet Function” 列中的所有列（取消勾选【使用原始列名作为前缀】复选框）。

4. 设置所有列，【转换】【检测数据类型】。

这里要注意的最后一件事是加载查询时的加载目的地。在这一个 Power Query 会话中再次创建了多个查询。问题是，原始 “Timesheet” 查询已经设置为加载到工作表，因此只能为新查询选择加载目的地，而工作表中不需要这些查询。

1. 转到【主页】选项卡【关闭并上载至】【仅创建连接】。

此时，数据表将更新，以显示 “Timesheets” 子文件夹中两个 “Timesheet” 文件的记录，并且针对该表的公式将完美地更新，如图 17-18 所示。

图 17-18 根据 “Timesheet” 表构建的业务逻辑（公式）将完美地更新

17.3 手动构建自定义函数

到目前为止，事情似乎相当简单。创建一个或多个【参数】，在查询中使用一个（或多个）【参数】，然后根据该查询创建一个函数。不幸的是，用不了多久，就会遇到一个不可能实现这一点的场景。

如果打开 “第 17 章 示例文件 Pivoting Tables-Begin.xlsx” 文件，会发现第一个工作表中有两个表如图 17-19 所示。本案例的目标是逆透视每个表。

图 17-19 目标是能够在追加数据之前进行【逆透视】

当然可以连接到每个单独的表，对其逆透视，然后将它们全部追加在一起。但是，如果转换更加复杂呢？更好的方法是构建一个函数可以一致地应用于每张表。

根据前面的示例，这似乎不太难，但有一个大问题，目前只创建过包含基本值的【参数】。由于需要向函数传递一个表（一个结构化值），所以不能利用【参数】来实现这一点。这意味着需要从头开始构建一个【自定义函数】。

为了构建【自定义函数】，基本上要遵循如下三步流程。

1. 首先构建一个单一使用场景。

2. 将单一使用场景转换为函数。

3. 从另一个查询调用函数。

这在实践中听起来很容易，下面是具体操作步骤。

17.3.1 构建一个单一使用场景

开始前，请打开 “第 17 章 示例文件 Pivoting Tables-Begin.xlsx” 文件，进行如下操作。

1. 创建新查询【自其他源】【空白查询】。

2. 在公式栏中输入 “=Excel.CurrentWorkbook ()”。

3. 将查询重命名为 “Sales”。

4. 筛选 “Name” 列【不等于】“Sales”。

【警告】

在使用 Excel.CurrentWorkbook 函数合并表时，不要忘记在第 8 章学到的经验：始终筛选出要创建的表。

完成了面向未来的工作后，现在可以构建一个单一使用场景来对两个表中的一个进行【逆透视】。要做到这一点，需要进行如下操作。

1. 右击 “Content” 列中的任意一个表值，选择【作为新查询添加】。

2. 将新查询重命名为 “fxUnpivot”。

3. 右击 “Date” 列【逆透视其他列】。

4. 将 “属性” 列重命名为 “Product”，将 “值” 列重命名为 “Units”。

5. 将数据类型设置为【日期】、【文本】和【整数】。

假设是从 “Table2” 开始，此时输出应该如图 17-20 所示。

图 17-20 这是一个透视的表，现在如何使它成为一个函数呢

17.3.2 将查询转换为函数

该过程的下一步是将查询转换为函数。这包括如下三个步骤。

1. 为保存要替换的数据变量命名。

2. 编辑查询，并在开始处加入以下文字：“(variable_name)=>”。

3. 扫描查询，查找要替换的数据，并用变量名覆盖它。

【注意】

可以在变量名周围加入空格，只要这样做可以使代码更易于阅读。

最好是为所保存的数据设置一个描述性的变量名，因为这有助于自行记录 M 代码。由于目标是将单次使用场景转换为一个函数，在这个函数中可以传递一个表，因此本案例将使用变量名 “tbl”。

现在已经确定了变量名，编辑查询，以将其转换为函数。

1. 右击 “fxUnpivot” 查询【高级编辑器】。

2. 将光标放在 let 语句的正前方。

3. 输入 “(tbl)=>” 并按 Enter 键，代码现在应该如图 17-21 所示。

图 17-21 变量现在就位了

【注意】

此时，已经将查询转换为函数。但是，由于没有在代码中加入变量名，函数实际上不会改变任何东西。

对于下一步，理解查询背后的 M 代码很有必要。需要做的是找出变量的位置。目的是向查询传递一个表值，因此需要知道哪个步骤应该使用这个表值。

如果仔细查看代码，前两个步骤将连接到 Excel 工作簿，并设置一个筛选来删除 “Sales” 表，将这两个表从工作表中删除。下一行，从 “Table1” 开始，导航到 “Table2” 的内容。因此，可以指定 “Table1” 步骤，将其设置为等于 tbl 变量，如下所示：

Table1 = tbl

虽然这是可行的，但并不是真正必要的，因为这一步所做的一切都是记录表的值，以便它可以将其提供给下一行代码：

Table.UnpivotOtherColumns(Table1, {"Date"}, "Attribute", "Value"),

【逆透视其他列】需要一个表作为第一个参数，这就是 tbl 变量所包含的【参数】。因此，与其将 tbl 传递到 “Table1”，然后将 “Table1” 传递到下一行，不如直接将 tbl 变量传递到 Table.UnpivotOtherColumns 函数中，如图 17-22 所示。

图 17-22 把 tbl 变量放在这里

事实上，如果这样做，甚至不再需要查询定义的前三行，可以直接删除它们，如图 17-23 所示。

图 17-23 函数长度变得更短了，但它能有效的工作吗

此时，单击【完成】，查询将更改为函数视图，如图 17-24 所示。

图 17-24 很好，看起来像一个函数了，但它会像函数一样工作吗

17.3.3 调用函数

如果【参数】需要一个原始值，例如一段文本或一个值，可以在对话框中输入该值，然后调用它。这样可以轻松地测试函数，以确保它正确地工作。不幸的是，对于结构化值来说，做到这一点有点困难。

【注意】

遗憾的是，在这里甚至不能像创建【自定义列】时那样，在对话框前面加上 “=” 字符来欺骗这个对话框。输入 “=Excel.CurrentWorkbook (){0}[Content]” 它只是将公式视为文本。

测试此函数最简单的方法就是调用它，将一些表传递给它以供使用，结果如图 17-25 所示。

1. 选择 “Sales” 查询【添加列】【调用自定义函数】。

2. 选择 “fxUnpivot” 列，确保【tbl】设置为 “Content”【确定】。

3. 预览 “Table1” 的结果。

图 17-25 看起来自定义的 “fxUnpivot” 函数正在运行

正如所看到的，虽然【参数】非常有用，但如果想要构建【自定义函数】，实际上【参数】并不是必需的。继续下面的步骤。

1. 右击 “fxUnpivot” 列【删除其他列】。

2. 展开 “fxUnpivot” 列（取消勾选【使用原始列名作为前缀】复选框）。

3. 选中全部列，【转换】【检测数据类型】。

在这个过程的最后，数据看起来很完美，如果有用户添加另一个产品表，它将包含在【逆透视】操作中，如图 17-26 所示。

图 17-26 【逆透视】的结果

17.3.4 调试自定义函数

如果不创建【参数】，就无法保存 “Transform Sample” 的副本。这是使用自定义函数时最痛苦的一件事：失去了轻松处理它们的能力。这使得调试自定义函数变得有点困难。

虽然不是很理想，但有一种方法可以将函数转换回查询，以便对其进行测试。这个过程的不幸之处在于它是一个临时状态，因为将函数转换为可调试状态会将其转换为函数模式，从而中断调试过程中的任何后续查询。尽管如此，这是实现目标的唯一途径，因此在这里将继续探讨。

为了恢复【应用的步骤】窗口，以便调试函数，需要将其转换回查询。为此，需要做如下两件事。

1. 注释掉将查询转换为函数的那行代码。

2. 复制初始行中使用的变量，并给它赋值。

如果少执行了上述任一操作，查询或函数将返回错误的结果（最好的情况），或错误（最坏的情况）。

为了注释掉 M 代码中的一行，可以在该行的开头插入字符 “//”。这会告诉 Power Query 引擎不执行这一行上的剩余字符。

为了复制变量，需要在初始 let 行之后设置一个新步骤，该步骤创建并为变量赋值。该行必须使用以下语法来构建：

Variable_name = assign_value_here ,

变量名必须是当前包含在函数的左括号中的变量，并且行必须以逗号结尾。

1. 右击 “fxUnpivot” 查询【高级编辑器】。

2. 修改查询，使前三行内容如下：

//( tbl )=>

let tbl = Excel.CurrentWorkbook(){0}[Content] ,

【注意】

不要忘记行尾的最后一个逗号，否则代码将无法工作。

【警告】

请注意，如果确实调用了【自定义函数】，它会将整个函数包装在一个新的 let/in 结构中。在这种情况下，需要注释函数的前两行和最后两行，以便将其设置为查询。

当完成后，代码看起来如图 17-27 所示。

图 17-27 修改后的代码，将其转换为一个查询

单击【完成】后，将看到可以单步执行并验证查询中发生的情况，如图 17-28 所示。

图 17-28 步骤返回正确，但是 “Sales” 查询出现了一些错误

这里的好处是，可以遍历每个步骤，包括为调试指定给 tbl 的值。但是有一些坏消息。

【警告】

当函数处于调试模式时，任何引用它的查询都将不起作用。

17.3.5 恢复函数功能

要将查询转换回函数，需要再次编辑 M 代码以执行如下两项操作。

1. 从初始行中删除 “//” 字符。

2. 将 “//” 字符放在当前声明临时变量的行的前面，一旦完成，函数将恢复其正常操作方法，使用此函数的所有查询都将能够再次使用它。

关于创建自定义函数的最后一个想法是，虽然声明每个变量的数据类型不是必需的，但这是一个明智的做法。为此，只需在变量声明行中添加 “as

”。如果在将函数设置回可用状态之前执行此操作，那么代码将如图 17-29 所示：

图 17-29 恢复 “函数” 功能时键入变量的数据

【警告】

忘记注释掉临时变量行将导致该行覆盖传递到函数中的任何变量，所以不要忘记它。

17.4 动态参数表

当考虑【参数】时，除了动态更改文件路径或表之外，它还有很多潜在的功能。例如，如果只需更改【参数】就可以轻松地将数据筛选到特定的业务部门或部门，这不是很好吗？

虽然【参数】很好用，但在使用【参数】的过程中，有几件事情让人沮丧：第一点是，它们鼓励使用静态值。但在实际场景中确实希望能够通过 Excel 公式来驱动 Power Query 参数，在运行时从电子表格传递当前值。第二点是，为了更新一个参数的值，必须使用只能在 Power Query 编辑器中找到的【管理参数】按钮。这意味着每次想要进行更改时都需要额外的单击和等待时间。不仅不方便，而且它对用户一点也不友好。

本节将展示如何设置一个表和一个名为 fnGetParameter 的函数来解决这些问题。虽然它在 Excel 中特别有用，但如果维护一个存放参数的表格，则该技术也可以在 Power BI 中使用。但是，对于 Power BI 来说有如下两个主要缺点。

1. 仍然需要进入 Power Query 编辑器来更新这些【参数】值。

2. 由于最终用户无法访问或修改已发布模型中的 M 代码，因此在发布 Power BI 模型后，用户无法动态更改这些【参数】。

好处是它将所有的【参数】保存在一个易于模型开发人员查看的表中。

【注意】

正如将要看到的，这里不会真正的创建【参数】，而是从表中提取基本值并将其传递给其他查询。

17.4.1 动态文件路径问题

使用 Web 托管数据时，共享文件相对容易，可以通过任何方法简单地共享该文件。当用户在另一端打开文件时，可能会提示他们启用数据连接、设置隐私级别或进行身份验证，但这些问题是意料之中的。由于数据是基于 Web 托管的，因此可以在不更新文件中任何数据源连接器的情况下进行访问。

但是，当构建了一个基于 Excel 的解决方案（如本章前面更改的 “Timesheet” 示例）时，会发生什么呢？数据存储在本地计算机或网络驱动器上的文件夹中。现在，可以压缩解决方案并与其他人共享整个文件夹，就像在本书的示例文件中所做的那样。

当用户收到文件时，会将其解压缩到文件夹中，打开解决方案并触发刷新。会发生什么？答案取决于最终用户是否将文件解压缩到与作者完全相同的路径。如果用户这样做了，那么【刷新】将起作用。否则，将收到错误消息，需要更新所有 “本地” 资源的文件路径。

【注意】

这个问题肯定会影响到根据本地数据源构建解决方案的 Power BI 用户，但会更频繁地影响 Excel 用户，因为他们更倾向于使用本地电脑的数据文件。

将 “Timesheet” 示例放到一个真实的业务环境中：用户先构建了主合并文件，将其保存在 “H:Payroll” 中，并且几个月来一直将 “Timesheet” 存储在 “H:Payrolltimesheets” 中的子文件夹中。在辛苦工作了一年之后，终于有了几周的假期，用户不得不在离开的时候把解决方案交给其他人来维护。但有一个问题，他们的系统将开发人员的解决方案路径映射为 “J:HRPayroll”。与其为暂代者重新编码解决方案，然后在用户返回时再重新编码，不如创建相对于工作簿所在位置的路径。这样，如果用户从 “J:HRPayroll” 或 “H:Payroll” 或其他地方打开它，它应该不会有什么区别。

真正的挑战是什么？目前，M 语言中没有任何函数允许用户计算出所使用工作簿的路径。有趣的是，一个 Excel 公式可以完成这项工作。

【注意】

Power BI 没有像 Excel 那样可以枚举源文件位置的函数。如果这是一个 Power BI 解决方案，可以将文件存储在 SharePoint 文件夹中并连接到该文件夹，因为这是一个基于网络托管的的数据集，路径不会更改。

17.4.2 实现动态参数表

回到上一个 “Timesheet” 示例，看看是否可以使文件路径真正动态，以便任何人打开文件都可以执行【刷新】。

【注意】

可以打开 “第 17 章 示例文件 Retrofitting-Complete.xlsx” 文件来了解更多信息。

有如下三个步骤来实现。

1. 在 Excel 中创建参数表。

2. 创建从表中提取值的函数。

3. 更改现有查询以调用该函数。

17.4.3 创建参数表

需要做的第一件事是创建一个表来保存参数。为了可以使用将要提供的函数，此表采用特定的形式，并且需要正确设置某些组件。

在 “Timesheet” 工作表的单元格 F9:G10 中创建如图 17-30 所示的表。

图 17-30 参数表的基本结构

请注意，这个表有如下三个关键特征。

1. 第一列的标题是：“Parameter”。

2. 第二列的标题是：“Value”。

3. 该表的名称为：“Parameters”。

【警告】

这是一个官方的 “CTRL+T” 表，为了使用下载文件中提供的功能，正确设置每个属性是至关重要的。如果这些项中有一项拼写不同，则需要调试表和（或）函数。

现在这个表已经设置好了，可以保存要在解决方案中作为动态变量使用的每一条数据。只需在左侧提供参数的名称，并在 “Values” 列中提供参数的数值。

【注意】

“Values” 列中的项目可以是硬编码的文本或值，可以由数据验证列表驱动，也可以使用公式。如何在单元格中获得正确的值完全取决于作为解决方案开发人员。

接下来，需要确定当前工作簿的文件路径。在单元格 G10 中输入以下公式：

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

【注意】

如果尚未保存文件，此函数将不会返回路径，因为它无法确定工作簿所在的位置。函数的最后一个参数在这种情况可以提示如何解决问题，而不是显示错误。

执行此操作后，应该会看到列出的工作簿的文件路径。唯一的挑战是，希望此文件路径指向 “TimeSheets” 文件夹，因为它是存储 “Timesheet” 的地方。更新公式以添加文件夹路径，如图 17-31 所示。

=IFERROR(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1),

"Workbook needs to be saved!")&"TimeSheets"

图 17-31 使用 Excel 公式动态返回文件路径

【警告】

微软 365 中一个令人恼火的特性是，同步到 OneDrive 或 SharePoint 的文件夹中存储的文件将返回文件的 “https://” 路径，而不是本地文件路径。问题是，需要一个不同的连接器来访问文件内容。如果需要阻止此 “智能” 开关，并强制解决方案使用本地文件路径，可以通过暂时暂停 OneDrive 的同步，然后从本地路径重新打开文件来实现。

17.4.4 实现 fnGetParameter 函数功能

由于参数表现在处于可以保存所需的任何变量的状态，只需要为 Power Query 提供一个读取这些值的方法。此部分可通过使用以下自定义函数完成：

( getValue as text ) =>

let ParamTable = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Result = ParamTable{[Parameter=getValue]}?[Value]?in Resul

【注意】

在 “第 17 章 示例文件” 文件夹中的 “fnGetParameter.txt” 文件中。除了代码之外，它还包含使用函数的说明，以及从单元格返回文件路径的公式。提供该文件是为了给用户一个可以多次存储和使用的模板。

此代码连接到工作簿中的参数表，然后选择表中动态参数值与 Excel 表的参数列中的记录匹配的行。匹配完成后，它将返回在 “Value” 列中找到的内容。正是因为这些名称都是在函数中硬编码的，所以 Excel 表的表名和列名都与上面指定的匹配。

与其重新输入这整块代码，不如打开 “第 17 章 示例文件 fnGetParameter.txt” 文件，复制文件中的所有行。有了粘贴缓冲区中的这些函数，在解决方案中实现这个函数就会非常简单。

1. 创建新查询【自其他源】【空白查询】。

2. 转到【主页】选项卡，【高级编辑器】显示窗口中的所有代码行。

3. 按 CTRL+V 以粘贴文本文件的内容。

4. 单击【完成】。

5. 将函数名更改为 “fnGetParameter” 后就完成了。

17.4.5 调用函数

在构建了参数表并准备好函数之后，最后一步是改进现有查询以实际使用它。这样做将允许从单元格中获取文件路径，并在查询中使用该路径。由于重新计算工作簿时文件路径会更新，因此它将始终是准确的，这意味着解决方案将始终在当前文件所在的子目录中查找 “Timesheet” 文件。

要优化 “Timesheet” 查询，甚至不必离开 Power Query 编辑器。

1. 选择 “FilesList” 查询，右击它选择【高级编辑器】。

2. 在 let 行之后立即插入以下代码行：

fullfilepath = fnGetParameter("File Path"),

查询现在应如图 17-32 所示。

图 17-32 调用 “fnGetParameter” 函数

请注意，在这里创建了一个名为 “fullfilepath” 的新变量，用于保存 Excel 表 “File Path” 行中的值。

【警告】

虽然可以跳过此步骤，只需将 “fnGetParameter” 调用嵌套在下一行的文件路径中，但依然建议不要这样做。在函数开头为每个 “fnGetParameter” 创建单独的步骤，有助于避免在上一个示例中触发的 “Formula Firewall” 问题，即在后面的查询步骤中，被阻止访问数据源。

此外，通过在单独的行中添加此调用，可以使查询更易于调试，如下所示。

1. 单击【完成】。

2. 在【应用的步骤】窗口中选择 “fullfilepath” 步骤。

文件夹的 “fullfilepath” 在编辑器中显示得很好，可以感到欣慰的是，已经将该部分更正了，如图 17-33 所示。

图 17-33 “fullfilepath” 变量正在正确地提取 “File Path”

现在已经知道函数通过 Excel 公式返回了正确的路径，可以在 “Source” 步骤中使用变量以代替硬编码的文件路径。

1. 转到【主页】【高级编辑器】在 “Source” 行中找到文件路径。

2. 选择整个文件路径（包括引号）并将其替换为 “fullfilepath” 查询的前三行，如下：

let

fullfilepath = fnGetParameter("File Path"), Source = Folder.Files(fullfilepath),

【注意】

必须通过【高级编辑器】或公式栏手动编辑 M 代码。无法通过单击 “Source” 步骤旁边的齿轮图标来完成，因为 “fullfilepath” 不是 Windows 操作系统的有效参数或文件夹。

修改完成后，可以单击【完成】，可以看到查询的每个步骤仍然正常工作，如图 17-34 所示。、

图 17-34 改进后的查询仍然可以工作

17.5 参数表的意义

特别是在 Excel 中使用 Power Query 时，引用参数表在建立解决方案时提供了极大的灵活性。如果在公司内部构建解决方案，并且需要与团队成员或其他部门共享，现在可以将其设置为从相对于解决方案路径的动态文件夹结构中读取。另一方面，如果是为客户开发解决方案的一个顾问，那么这项技术将产生巨大的影响，因为顾问系统上是否会有与客户完全相同的文件结构是值得怀疑的。在这两种情况下，最不希望做的事情就是向最终用户发送一个文件，并说明如何编辑 M 代码。

通过打开 “Dynamic FilePath-Complete.xlsx” 文件并【刷新】数据来实际看到这一点。只要 “TimeSheets” 文件夹（和文件）与 Excel 文件存储在硬盘上的同一位置（并且该位置未主动同步到 OneDrive 或 SharePoint），【刷新】就会正常工作，如图 17-35 所示。

图 17-35 完全不同的（本地）文件路径不会影响【刷新】功能

但参数表的力量并不止于此。考虑以下每一项可能希望完成的任务。

1. 基于 Excel 工作表中单元格中的日期构建日历表。

2. 根据 Excel 单元格中的值驱动表格筛选器。

3. 确定将四个 Excel 表格中的哪一个加载到解决方案中。

通过设置并使用【自定义函数】从 Excel 表格中读取，就可以实现这些目标中的任何一个。这不仅能够动态地驱动内容，还能够在更熟悉的环境中生成数据，在某些情况下，还可以做 Power Query 不允许做的事情。

(职场责编：拓荒牛 )