创建参数查询 (Power Query)

创建参数查询 (Power Query)

你可能非常熟悉参数查询在 SQL 或 Microsoft 查询中的用法。 但是，Power Query参数存在主要差异：

• 参数可用于任何查询步骤。 除了充当数据筛选器之外，参数还可用于指定文件路径或服务器名称等内容。
• 参数不提示输入。 相反，可以使用 Power Query 快速更改其值。 甚至可以在 Excel 中存储和检换单元格中的值。
• 参数保存在简单的参数查询中，但与所使用的数据查询不同。 创建后，可以根据需要向查询添加参数。

创建参数

可以使用 参数自动更改查询中的值，并避免每次编辑查询以更改该值。 只需更改参数值即可。 创建参数后，该参数将保存在特殊参数查询中，可以直接从 Excel 进行更改。

1. 选择“数据”>“>”启动Power Query 编辑器“> 获取其他源的数据。
2. 在Power Query 编辑器，选择“主页”>“管理参数”>“新建参数”。
3. 在“管理参数”对话框中，选择“新建”。
4. 根据需要设置以下内容：

• 名称：这应反映参数的函数，但请尽可能短。
• 说明：这可以包含有助于人们正确使用 参数的任何详细信息。
• 必需：执行下列作之一：任意值可以在参数查询中输入任何数据类型的任何值。值列表可以通过在小网格中输入值，将值限制为特定列表。 还必须在下面选择“默认值”和“当前值”。查询选择列表查询，该查询类似于用逗号分隔并用大括号括起来的列表结构化列。例如，“问题”状态字段可以有三个值：{“New”、“Ongoing”、“Closed”}。 必须事先创建列表查询，方法是打开高级编辑器 (选择“开始>高级编辑器)”，删除代码模板，以查询列表格式输入值列表，然后选择“完成”。创建完参数后，列表查询将显示在参数值中。
• 类型：这将指定 参数的数据类型。
• 建议的值：如果需要，请添加值列表或指定查询来提供输入建议。
• 默认值：仅当“建议值”设置为“值列表”，并指定默认列表项时，才会显示此值。 在这种情况下，必须选择默认值。
• 当前值：如果此参数为空，则查询可能不返回任何结果，具体取决于使用 参数的位置。 如果选择“必需”，则“当前值”不能为空。

5. 若要创建参数，请选择“确定”。

使用参数更改数据源

下面是一种管理数据源位置更改并帮助防止刷新错误的方法。 例如，假设架构和数据源类似，请创建一个参数来轻松更改数据源并帮助防止数据刷新错误。 有时服务器、数据库、文件夹、文件名或位置会更改。 也许数据库管理器偶尔会交换服务器，每月一次的 CSV 文件会进入其他文件夹，或者你需要在开发/测试/生产环境之间轻松切换。

步骤 1：创建参数查询

在以下示例中，使用导入文件夹作导入多个 CSV 文件， (选择数据>从文件获取数据> >从文件夹) 从文件夹 C：\DataFilesCSV1。 但有时，其他文件夹偶尔会用作放置文件的位置，C：\DataFilesCSV2。 可以使用查询中的参数作为不同文件夹的替代值。

1. 选择“主页”>“管理参数”>“新建参数”。
2. 在“管理参数”对话框中输入以下信息：

• 名称：CSVFileDrop
• 说明：备用文件放置位置
• 必需：是
• 类型：文本
• 建议的值：任何值
• 当前值：C：\DataFilesCSV1

3. 选择“确定”。

步骤 2：将 参数添加到数据查询

1. 若要将文件夹名称设置为参数，请在“查询设置”中的“查询步骤”下，选择“源”，然后选择“编辑设置”。
2. 确保“文件路径”选项设置为“参数”，然后从下拉列表中选择刚刚创建的参数。
3. 选择“确定”。

步骤 3：更新参数值

文件夹位置刚刚更改，现在只需更新参数查询即可。

1. 选择“数据>Connections &查询>查询”选项卡，右键单击参数查询，然后选择“编辑”。
2. 在“当前值”框中输入新位置，例如C：\DataFilesCSV2。
3. 选择“开始>关闭 & 加载”。
4. 若要确认结果，请将新数据添加到数据源，然后使用更新的参数刷新数据查询， (选择数据>刷新全部) 。

使用参数筛选数据

有时，需要一种简单的方法来更改查询的筛选器以获取不同的结果，而无需编辑查询或创建同一查询的略有不同副本。 在此示例中，我们将更改日期以方便地更改数据筛选器。

1. 若要打开查询，请找到以前从Power Query 编辑器加载的查询，在数据中选择一个单元格，然后选择“查询>编辑”。 有关详细信息，请参阅在 Excel 中创建、加载或编辑查询。
2. 选择任何列标题中的筛选器箭头以筛选数据，然后选择筛选命令，例如日期/时间筛选器>之后。 此时将显示“筛选行”对话框。

3. 选择“值”框左侧的按钮，然后执行下列作之一：

• 若要使用现有参数，请选择“参数”，然后从右侧显示的列表中选择所需的参数。
• 若要使用新参数，请选择“新建参数”，然后创建参数。

4. 在“当前值”框中输入新日期，然后选择“开始>关闭 & 加载”。
5. 若要确认结果，请将新数据添加到数据源，然后使用更新的参数刷新数据查询， (选择数据>刷新全部) 。 例如，将筛选器值更改为其他日期以查看新结果。
6. 在“当前值”框中输入新日期。
7. 选择“开始>关闭 & 加载”。
8. 若要确认结果，请将新数据添加到数据源，然后使用更新的参数刷新数据查询， (选择数据>刷新全部) 。

使用单元格值筛选数据

在此示例中，将从工作簿中的单元格读取查询参数中的值。 无需更改参数查询，只需更新单元格值。 例如，你想要按第一个字母筛选列，但可以轻松地将值更改为从 A 到 Z 的任何字母。

1. 在加载要筛选的查询的工作簿中的工作表上，创建包含两个单元格的 Excel 表：标题和值。

MyFilter
G

2. 在 Excel 表中选择单元格，然后选择“数据”>“从表/区域获取数据>”。此时会显示Power Query 编辑器。
3. 在右侧“查询设置”窗格的“名称”框中，将查询名称更改为更有意义，例如 FilterCellValue。
4. 若要传递表中的值，而不是表本身，请在数据预览中右键单击该值，然后选择“向下钻取”。

请注意，公式已更改为= #"Changed Type"{0}[MyFilter]
在步骤 10 中使用 Excel 表格作为筛选器时，Power Query引用 Table 值作为筛选条件。 直接引用 Excel 表会导致错误。

5. 选择“开始>关闭 & 加载>关闭 & 加载到”。 现在，你有一个名为“FilterCellValue”的查询参数，可在步骤 12 中使用。
6. 在“导入数据”对话框中，选择“仅创建连接”，然后选择“确定”。
7. 通过选择数据中的单元格，然后选择“查询**”>**“编辑”，打开要筛选的查询，并使用 FilterCellValue 表中的值（以前从Power Query 编辑器加载的值）。 有关详细信息，请参阅在 Excel 中创建、加载或编辑查询。
8. 选择任意列标题中的筛选器箭头以筛选数据，然后选择筛选命令，例如文本筛选器>开头。 此时将显示“筛选行”对话框。
9. 在“值”框中输入任何值，例如“G”，然后选择“确定”。 在这种情况下，该值是 FilterCellValue 表中值的临时占位符，在下一步中输入该值。
10. 选择编辑栏右侧的箭头以显示整个公式。 下面是公式中的筛选条件示例：

= Table.SelectRows (#“Changed Type”，每个 Text.StartsWith ([Name]，“G”) )

11. 选择筛选器的值。 在公式中，选择“G”。
12. 使用 M Intellisense，输入创建的 FilterCellValue 表的前几个字母，然后从出现的列表中选择它。
13. 选择“开始>关闭>关闭 & 加载”。

结果

查询现在使用您创建的 Excel 表中的值来筛选查询结果。 若要使用新值，请在步骤 1 中编辑原始 Excel 表中的单元格内容，将“G”更改为“V”，然后刷新查询。

控制参数查询的使用

可以控制是否允许参数查询。

1. 在Power Query 编辑器中，选择“文件>选项和设置”>查询选项>Power Query 编辑器。
2. 在左侧窗格中的“全局”下，选择“Power Query 编辑器”。
3. 在右侧窗格中的“参数”下，选择或清除“始终允许在数据源和转换对话框中进行参数化”。

另请参阅

• Microsoft Power Query for Excel 帮助
• 使用查询参数 (docs.com)