Power Query如何实现跨查询参数传递？

下面我将从为什么需要、如何创建、如何使用，到高级应用，全面地为你讲解 Power Query 的参数传递。

为什么需要参数？（参数的好处）

想象一个场景：你每个月都需要从销售数据库中提取数据，但需要筛选出不同的年份和月份，如果没有参数，你可能每个月都要手动修改筛选条件，非常繁琐且容易出错。

使用参数后,你可以：

1. 实现动态筛选：创建一个参数，让它代表“年份”，然后在筛选步骤中引用这个参数，每次刷新时，只需更改参数的值，筛选条件就会自动更新。
2. 提高可维护性：将所有可能变化的值（如服务器名、文件路径、日期范围）都定义为参数，当这些值需要修改时，你只需要在“管理参数”界面更改一次，所有引用该参数的步骤都会自动更新。
3. 创建可复用的查询：你可以将一个复杂的查询（比如一个通用的数据清洗逻辑）发布为数据连接，并带上参数，其他用户可以连接到这个查询，并提供他们自己的参数值，而无需了解你复杂的内部逻辑。
4. 实现自动化：在 Power BI 或 Excel 中，你可以用外部数据（如另一个工作表、单元格或输入框）来动态设置 Power Query 参数的值，实现完全自动化的报表更新。

如何创建和管理参数？

有两种主要方式创建参数：手动创建和从查询创建。

手动创建

这是最直接的方法。

1. 在 Power Query 编辑器中，点击顶部菜单栏的 管理参数 (Manage Parameters)。
2. 在弹出的窗口中,点击 新建 (New)。
3. 填写参数信息：
   • 名称：给参数起一个有意义的名字，CurrentYear。注意：名称不能有空格，可以使用 PascalCase 或下划线。
   • 描述（可选）：添加描述，方便日后自己或他人理解参数的用途。
   • 类型：选择参数的数据类型，如 文本、数字、日期、逻辑值、列表、记录 等。
   • 值：设置参数的默认值，对于 CurrentYear，可以设置为 2025。
   • 可选值（可选）：如果希望参数只能从几个固定的值中选择，可以在这里设置，年份可以从 [2025, 2025, 2025] 中选择。
4. 点击 确定。

你的参数 CurrentYear 就创建好了，并出现在“管理参数”窗口中。

从查询创建

当你有一个查询,其结果是你想用作参数的值时（一个包含所有销售员名字的列表），这个方法非常方便。

1. 在 Power Query 编辑器的左侧查询列表中，右键点击你想要用作参数的查询（一个名为 SalesPersonList 的查询）。
2. 选择 创建参数 (Create Parameter)。
3. Power Query 会自动填充大部分信息：
   • 名称：会建议一个名称，如 SalesPersonList。
   • 描述：会使用原查询的描述（如果有的话）。
   • 类型：会根据原查询的结果自动选择（如果查询返回一个列表，类型就是 列表）。
   • 值：会自动设置为 =SalesPersonList，即引用该查询的结果。
   • 可选值：会自动勾选，因为它的值就是查询的结果。
4. 点击 确定。

这种方法的好处是,如果源数据更新了，导致 SalesPersonList 查询的结果发生变化，参数的值也会自动更新。

如何在查询中使用参数？

创建好参数后,关键在于如何在查询步骤中引用它，引用参数的语法是：#参数名。

示例1：在筛选步骤中使用参数（最常见的场景）

假设你有一个名为 Sales 的查询，你只想查看 CurrentYear 参数所指定年份的数据。

1. 确保 CurrentYear 参数已创建，默认值为 2025。

2. 在 Sales 查询中，选中“日期”列。

3. 点击 转换 选项卡 -> 日期 -> 年，添加一个“年”列。

4. 右键单击新添加的“年”列的标题，选择 筛选 -> 不等于... (Not Equals...)。

5. 在弹出的筛选框中,不要输入数字，而是直接输入 =#CurrentYear 然后按回车。

   • 注意：输入时，Power Query 的智能提示会帮你补全，输入 后会列出所有可用的参数。

6. 你的筛选条件就变成了 Year <> #CurrentYear，如果你想筛选出等于指定年份的数据，可以改为 Year = #CurrentYear。

效果：当你点击 管理参数，将 CurrentYear 的值从 2025 改为 2025 并确定后，返回到 Sales 查询，点击 刷新，筛选条件就会自动更新，只显示 2025 年的数据。

示例2：在源步骤中使用参数（动态路径）

假设你的数据文件每个月都会放在一个以月份命名的文件夹里,你想通过参数来指定文件夹。

1. 创建一个名为 DataFolder 的参数，类型为 文本，默认值为 "C:\Sales\2025-01\"。
2. 创建一个名为 SourceFile 的参数，类型为 文本，默认值为 "SalesData.xlsx"。
3. 创建一个新查询,连接到 Excel 文件，在“文件”路径中，不要直接输入完整路径，而是使用 & 符号将参数连接起来：
   • 在“文件”输入框中输入：= #DataFolder & #SourceFile
4. 点击 确定。

当月份更迭时,你只需要修改 DataFolder 参数的值即可，无需重新创建整个查询。

高级应用：Power BI 中的动态参数

这是参数功能最强大的地方,可以实现报表与用户交互。

在 Power BI 中，你可以将 Power Query 参数与报表上的切片器或输入框绑定。

示例：用切片器控制查询参数

1. 准备参数：在 Power Query 编辑器中创建一个参数，SelectedYear，类型为 数字，默认值为 2025。
2. 准备数据：创建一个查询（SalesData），并像前面的示例一样，在筛选步骤中使用 =#SelectedYear。
3. 发布到 Power BI：点击 关闭并应用，将数据加载到 Power BI。
4. 创建切片器：
   • 在 Power BI Desktop 的报表视图中，点击 插入 -> 切片器。
   • 在右侧的“字段”窗格中，将 SalesData 表中的 Year 字段拖入切片器的“字段”框中。
5. 建立双向交互：
   • 选中你刚刚创建的切片器。
   • 在顶部出现的 切片器 选项卡中，勾选 在此报表页上显示为单个筛选器。
   • 关键一步：在 高级选项 中，找到 将切片器类型更改为...，选择 列表。
   • 仍然在 高级选项 中，找到 在此报表页上筛选，确保你的 SalesData 表被勾选。
6. 连接参数和切片器：
   • 选中切片器。
   • 在 切片器 选项卡中，点击 切片器值。
   • 在弹出的窗口中,选择 按字段，然后选择 Year 字段。
   • （重要）：切片器还无法直接控制 Power Query 参数，我们需要一个“桥梁”。
   • 回到 Power Query 编辑器，创建一个新查询，使用 Value.Matches 或类似逻辑，但这比较复杂。
   • 更简单的方法（推荐）：在 Power BI Desktop 中，使用 新建参数 功能（在“主页”选项卡下），这个参数会直接与 Power Query 参数同步，创建一个名为 pbix_SelectedYear 的新参数，类型为 数字，然后在其“高级选项”中，勾选 可用值 -> 从查询获取，并选择你的 SalesData 表的 Year 列，你需要修改原始的 Power Query 参数 SelectedYear 的值为 =pbix_SelectedYear.Value。

更现代、更简单的方法（DAX 替代方案）： 在 Power BI 中，更常见的做法是不通过 Power Query 参数，而是通过 DAX 来实现动态筛选，因为这样更灵活、性能更好。

• 在 Power Query 中，先不进行年份筛选，把所有年份的数据都加载进来。
• 在 Power BI 中创建一个切片器，切片器的字段是 Year。
• 在创建任何度量值 时，使用 FILTER 函数来应用切片器的筛选条件。

创建一个总销售额度量值：

总销售额 = CALCULATE(SUM(SalesData[SalesAmount]), ALL(SalesData[Year])) // 先忽略所有年份筛选

当你添加了年份切片器后,它会自动与这个度量值交互，你无需任何额外配置，这是 Power BI 的核心设计理念之一。

核心要点：

• 语法：#参数名 是引用参数的关键。
• 类型：为参数选择正确的数据类型非常重要，尤其是在与列表、记录等复杂类型交互时。
• Power BI vs. Excel：在 Excel 中，参数主要通过 VBA 或外部单元格来设置，在 Power BI 中，切片器和 DAX 是更主流和强大的交互方式，但理解 Power Query 参数仍然是构建高效数据模型的基础。

希望这个详细的指南能帮助你完全掌握 Power Query 的参数传递功能！