在 Power Query 中参数化数据路径

如果您已经开始使用 Microsoft Excel 中的免费 Power Query 加载项工具，那么很快您就会遇到一个高度专业化但非常频繁且烦人的问题，该问题与源数据的链接不断断开有关。 问题的本质是，如果在查询中引用外部文件或文件夹，则 Power Query 在查询文本中硬编码它们的绝对路径。 在您的计算机上一切正常，但如果您决定将请求的文件发送给您的同事，那么他们会感到失望，因为。 他们的计算机上的源数据有不同的路径，我们的查询将不起作用。

在这种情况下该怎么办？ 让我们通过以下示例更详细地了解此案例。

问题的形成

假设我们在文件夹中 E:销售报告 在于文件 前 100 名产品.xls，这是从我们的企业数据库或 ERP 系统（1C、SAP 等）上传的。此文件包含有关最流行商品的信息，内部如下所示：

可能一开始就很清楚，在 Excel 中以这种形式使用它几乎是不可能的：空行到带有数据的行、合并的单元格、额外的列、多级标题等会干扰。

因此，在同一文件夹中的该文件旁边，我们创建另一个新文件 处理程序.xlsx，我们将在其中创建一个 Power Query 查询，该查询将从源上传文件中加载丑陋的数据 前 100 名产品.xls, 并将它们按顺序排列：

向外部文件发出请求

打开文件 处理程序.xlsx, 在选项卡上选择 时间 命令 获取数据 - 从文件 - 从 Excel 工作簿 （数据 - 获取数据 - 从文件 - 从 Excel），然后指定源文件的位置和我们需要的工作表。 所选数据将加载到 Power Query 编辑器中：

让我们让它们恢复正常：

1. 删除空行 Home——删除行——删除空行 （首页 - 删除行 - 删除空行）.
2. 通过删除不必要的前 4 行 主页 — 删除行 — 删除顶部行 （首页 - 删除行 - 删除顶部行）.
3. 使用按钮将第一行提升到表头 使用第一行作为标题 标签 主页 （首页 - 使用第一行作为标题）.
4. 使用命令将五位数的文章与第二列中的产品名称分开 拆分列 标签 转型 （变换——拆分列）.
5. 删除不必要的列并重命名剩余列的标题以获得更好的可见性。

结果，我们应该得到以下更令人愉快的画面：

仍然需要将这个高贵的表上传回我们文件中的工作表 处理程序.xlsx 团队 关闭并下载 （首页 - 关闭&加载） 标签 主页:

在请求中查找文件的路径

现在让我们看看我们的查询在“幕后”的样子，使用 Power Query 内置的内部语言，简洁名称为“M”。 为此，请在右侧窗格中双击它返回到我们的查询 请求和连接 并在选项卡上 评论 选择 进阶编辑 （查看 - 高级编辑器）:

在打开的窗口中，第二行立即显示了我们原始上传文件的硬编码路径。 如果我们可以将此文本字符串替换为参数、变量或指向预先写入此路径的 Excel 工作表单元格的链接，那么我们以后可以轻松更改它。

添加带有文件路径的智能表

让我们暂时关闭 Power Query 并返回到我们的文件 处理程序.xlsx. 让我们添加一个新的空工作表并在其上制作一个小的“智能”表格，其中唯一的单元格将写入我们的源数据文件的完整路径：

要从常规范围创建智能表，您可以使用键盘快捷键 按Ctrl+T 或按钮 格式化为表格 标签 主页 （首页——表格格式）. 列标题（单元格 A1）绝对可以是任何东西。 另请注意，为清楚起见，我为表格命名 参数 标签 构造函数 （设计）.

从资源管理器复制路径甚至手动输入当然不是特别困难，但最好尽量减少人为因素并尽可能自动确定路径。 这可以使用标准的 Excel 工作表函数来实现 CELL （细胞），它可以给出一堆关于作为参数指定的单元格的有用信息——包括当前文件的路径：

如果我们假设源数据文件总是和我们的处理器在同一个文件夹，那么我们需要的路径可以由以下公式形成：

=LEFT(CELL(“filename”);FIND(“[“;CELL(“filename”))-1)&”Top 100 products.xls”

或英文版：

=LEFT(CELL(«filename»);FIND(«[«;CELL(«filename»))-1)&»Топ-100 товаров.xls»

…函数在哪里 左室SIMV （剩下） 从完整链接到左方括号（即当前文件夹的路径）获取一段文本，然后将我们的源数据文件的名称和扩展名粘贴到它上面。

参数化查询中的路径

最后也是最重要的一点 - 在请求中写入源文件的路径 前 100 名产品.xls，指的是我们创建的“智能”表的单元格 A2 参数.

为此，让我们返回 Power Query 查询并再次打开它 进阶编辑 标签 评论 （查看 - 高级编辑器）. 而不是引号中的文本字符串路径 “E：销售报告前100名产品.xlsx” 让我们介绍以下结构：

Excel.当前工作簿(){[名称=“设置”]}[内容]0、XNUMX、XNUMX、XNUMX {}[源数据的路径]

让我们看看它是由什么组成的：

• Excel.当前工作簿() 是M语言的一个函数，用于访问当前文件的内容
• {[名称=“设置”]}[内容] – 这是对上一个函数的细化参数，表示我们要获取“智能”表的内容 参数
• [源数据的路径] 是表中列的名称 参数我们提到的
  
• 0、XNUMX、XNUMX、XNUMX {} 是表中的行号 参数我们要从中获取数据。 上限不计算在内，编号从零开始，而不是从一开始。

事实上，仅此而已。

它仍然可以点击 完成 并检查我们的请求是如何工作的。 现在，当将包含两个文件的整个文件夹发送到另一台 PC 时，请求将保持正常运行并自动确定数据的路径。

• 什么是 Power Query 以及在 Microsoft Excel 中工作时为什么需要它
• 如何将浮动文本片段导入 Power Query
• 使用 Power Query 将 XNUMXD 交叉表重新设计为平面表