在Excel中创建Power Query公式：从基础到高级

在Excel中创建Power Query公式：从基础到高级

Power Query是Excel中一个强大的数据查询和转换工具，可以帮助用户从多个数据源导入数据，并通过一系列步骤清洗和转换数据。本文将详细介绍如何在Excel中使用Power Query创建公式，包括基础操作、高级编辑以及全局设置等内容。

Power Query 编辑器概述

Power Query 编辑器为 Excel 提供数据查询和整形体验，可用于重塑来自多个数据源的数据。 若要显示Power Query 编辑器窗口，请在 Excel 工作表中从外部数据源导入数据，在数据中选择一个单元格，然后选择“查询>编辑”。 下面是main组件的摘要。

2. 用于调整数据的Power Query 编辑器功能区
3. 用于查找数据源和表的“查询”窗格
4. 上下文菜单是功能区中命令的便捷快捷方式
5. 显示应用于数据的步骤的结果的数据预览
6. 列出查询中的属性和每个步骤的“查询设置”窗格

公式概述

在后台，查询中的每个步骤都基于在编辑栏中可见的公式。

有时可能需要修改或创建公式。 公式使用Power Query公式语言，可用于生成简单表达式和复杂表达式。 有关语法、参数、注释、函数和示例的详细信息，请参阅Power Query M 公式语言。

遵循已应用步骤和公式的演练

以足球锦标赛列表为例，使用Power Query获取网站上找到的原始数据，并将其转换为格式正确的表格。 在“查询设置”窗格的“已应用步骤”下和“编辑栏”中，观察如何为每个任务创建查询步骤和相应的公式。

过程

2. 若要导入数据，请选择“数据>从 Web”，在“URL”框中输入“http://en.wikipedia.org/wiki/UEFA_European_Football_Championship”，然后选择“**确定**”。

3. 在“导航器”对话框中，选择左侧的“结果[编辑]”表，然后选择底部的“转换数据”。 此时会显示Power Query编辑器。

4. 若要更改默认查询名称，请在“查询设置”窗格中的“属性”下删除“结果 [编辑]”，然后输入“UEFA champs”。

5. 若要删除不需要的列，请选择第一列、第四列和第五列，然后选择“开始>删除列”>“删除其他列”。

6. 若要删除不需要的值，请选择Column1，选择“开始>替换值”，在“要查找的值”框中输入“详细信息”，然后选择“确定”。

7. 若要删除其中包含“Year”一词的行，请选择Column1中的筛选箭头，清除“年份”旁边的检查框，然后选择“确定”。

8. 若要重命名列标题，请双击每个列标题，然后将“Column1”更改为“Year”，将“Column4”更改为“Winner”，将“Column5”更改为“最终分数”。

9. 若要保存查询，请选择“开始”>“关闭 & 加载”。

结果

下表汇总了每个应用的步骤和相应的公式。

使用编辑栏

重要事项 编辑“源”、“导航”和“更改类型”步骤时要小心，因为它们是由Power Query创建的，用于定义和设置数据源。

显示或隐藏编辑栏

编辑栏默认显示，但如果它不可见，你可以重新显示它。

• 选择“视图>布局>编辑栏”。

在编辑栏中插入公式****

2. 若要打开查询，请找到以前从Power Query 编辑器加载的查询，在数据中选择一个单元格，然后选择“查询>编辑”。 有关详细信息，请参阅在 Excel 中创建、加载或编辑查询。

3. 在“查询设置”窗格中的“应用步骤”下，选择要编辑的步骤。

4. 在编辑栏中，找到并更改参数值，然后选择 Enter
   图标或按 Enter。 例如，将此公式更改为同时保留 Column2：Before：
   = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   After：= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

5. 选择“输入
   ”图标或按 Enter 查看数据预览中显示的新结果。

6. 若要查看 Excel 工作表中的结果，请选择“开始>关闭 & 加载”。

在编辑栏中创建公式

对于一个简单的公式示例，让我们使用Text.Proper函数将文本值转换为正确的大小写。

2. 若要打开空白查询，请在 Excel 中选择“数据>从其他源获取数据> >空白查询”。 有关详细信息，请参阅在 Excel 中创建、加载或编辑查询。

3. 在编辑栏中，输入=Text.Proper("text value")，然后选择 Enter
   图标或按 Enter。

结果显示在数据预览中。

6. 若要查看 Excel 工作表中的结果，请选择“开始>关闭 & 加载”。

结果:

在应用步骤中使用公式

创建公式时，Power Query验证公式语法。 但是，在查询中插入、重新排序或删除中间步骤时，可能会中断查询。 始终在数据预览中验证结果。

重要事项 编辑“源”、“导航”和“更改类型”步骤时要小心，因为它们是由Power Query创建的，用于定义和设置数据源。

使用对话框编辑公式

此方法使用因步骤而异的对话框。 无需知道公式的语法。

2. 若要打开查询，请找到以前从Power Query 编辑器加载的查询，在数据中选择一个单元格，然后选择“查询>编辑”。 有关详细信息，请参阅在 Excel 中创建、加载或编辑查询。

3. 在“查询设置”窗格中的“已应用步骤”下，选择要编辑的步骤的“编辑设置
   ”图标，或右键单击该步骤，然后选择“编辑设置”。

4. 在对话框中进行更改，然后选择“确定”。

插入步骤

完成对数据进行重塑的查询步骤后，查询步骤将添加到当前查询步骤的下方。 但在步骤中间插入查询步骤时，后续步骤中可能会出现错误。 Power Query在尝试插入新步骤时显示“插入步骤”警告，而新步骤会更改在插入步骤之后的任何步骤中使用的字段（如列名）。

2. 在“查询设置”窗格的“已应用步骤”下，选择要紧接在新步骤之前的步骤及其相应的公式。

3. 选择编辑栏左侧的“添加步骤
   ”图标。 或者，右键单击某个步骤，然后选择“在之后插入步骤”。新公式的格式为 ：
   = ，例如=Production.WorkOrder。

4. 使用格式键入新公式：
   =Class.Function(ReferenceStep[,otherparameters])

例如，假设你有一个包含“性别”列的表，并且你希望添加一个值“Ms.”或“Mr.”的列，具体取决于用户的性别。 公式为：
=Table.AddColumn(, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

对步骤重新排序

• 在“已应用的步骤”下的“查询设置”窗格中，右键单击该步骤，然后选择“上移”或“下移”。

删除步骤

• 选择步骤左侧的“删除
  ”图标，或右键单击该步骤，然后选择“删除”或“删除直到结束”。 编辑栏左侧还提供了“删除
  ”图标。

创建高级公式

在此示例中，让我们使用高级编辑器中的公式组合将列中的文本转换为正确的大小写。

例如，你有一个名为 Orders 的 Excel 表，其中包含要转换为正确大小写的 ProductName 列。

之前:

之后：

创建高级查询时，基于let表达式创建一系列查询公式步骤。 使用let表达式分配名称并计算由定义 Step 的in子句引用的值。 此示例返回的结果与“在编辑栏中创建公式”部分中的结果相同。

let
Source = Text.Proper("hello world")
in
Source

你将看到，每个步骤都通过引用按名称分步在上一步的基础上构建。 提醒一下，公式语言Power Query区分大小写。

阶段 1：打开高级编辑器

2. 在 Excel 中，选择“数据>获取数据>其他源>空白查询”。 有关详细信息，请参阅在 Excel 中创建、加载或编辑查询。

3. 在Power Query 编辑器中，选择“开始>高级编辑器”，这将打开，其中包含 let表达式的模板。

阶段 2：定义数据源

2. 使用Excel.CurrentWorkbook函数创建let表达式，如下所示：

let
Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
in
Source

4. 若要将查询加载到工作表，请选择“完成”，然后选择“开始>关闭 & 加载>关闭 & 加载”。

结果:

阶段 3：将第一行提升为标题

2. 若要打开查询，请从工作表中选择数据中的单元格，然后选择“查询>编辑”。 有关详细信息，请参阅在 Excel (Power Query) 中创建、加载或编辑查询。

3. 在Power Query 编辑器中，选择“开始>高级编辑器”，此时会打开你在阶段 2：定义数据源中创建的语句。

4. 在let表达式中，添加 #“First Row as Header”和Table.PromoteHeaders函数，如下所示：

let
Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
#"First Row as Header" = Table.PromoteHeaders(Source)
in
#"First Row as Header"

8. 若要将查询加载到工作表，请选择“完成”，然后选择“开始>关闭 & 加载>关闭 & 加载”。

结果:

阶段 4：将列中的每个值更改为适当的大小写

2. 若要打开查询，请从工作表中选择数据中的单元格，然后选择“查询>编辑”。 有关详细信息，请参阅在 Excel 中创建、加载或编辑查询。

3. 在Power Query 编辑器中，选择“开始>高级编辑器”，此时会打开你在“阶段 3：将第一行提升为标题”中创建的语句。

4. 在let表达式中，使用Table.TransformColumns函数将每个 ProductName 列值转换为适当的文本，引用前面的“第一行作为标头”查询公式步骤，将 #“Capitalized Each Word”添加到数据源，然后将 #“Capitalized Each Word”分配给结果。

let
Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
#"First Row as Header" = Table.PromoteHeaders(Source),
#"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
in
#"Capitalized Each Word"

8. 若要将查询加载到工作表，请选择“完成”，然后选择“开始>关闭 & 加载>关闭 & 加载”。

结果:

公式的全局设置

您可以控制所有工作簿Power Query 编辑器中编辑栏的行为。

显示或隐藏编辑栏

2. 选择“文件>选项和设置”>“查询选项”。

3. 在左窗格中的“全局”下，选择“Power Query 编辑器”。

4. 在右窗格中的“布局”下，选择或清除“显示编辑栏”。

打开或关闭 M Intellisense

2. 选择“文件”>“选项”和“设置”>“查询选项”。

3. 在左窗格中的“全局”下，选择“Power Query 编辑器”。

4. 在右窗格中的“公式”下，在编辑栏、高级编辑器和自定义列对话框中选择或清除“启用 M Intellisense”。

备注 更改此设置将在下次打开Power Query 编辑器窗口时生效。