Excel power 教程‌ 查询（获取和转换）

如果您是经常使用数据的人之一，您可以是任何人（会计师、HR、数据分析师等），PowerQuery 可以成为您的强大工具。

让我开门见山，Power Query 是您需要学习的高级 Excel 技能之一，在本教程中，您将详细探索 PowerQuery 并学习如何使用它转换数据。

让我们开始吧。

什么是 Excel Power Query

Power Query 是一个可用于 ETL 的 Excel 加载项。这意味着您可以从不同来源提取数据，对其进行转换，然后将其加载到电子表格中。您可以说 POWER QUERY 是一台数据清理机，因为它具有转换数据的所有选项。它是实时的并记录您采取的每一步。

为什么应该使用 Power Query（优点）？

如果您有这个问题，那么我的回答是：

• 不同的数据源：您可以从不同的数据源（例如CSV 、TXT、JSON 等）将数据加载到功能强大的查询编辑器中。
• 轻松转换数据：通常，您使用公式和数据透视表进行数据转换，但使用 POWER QUERY，只需单击几下即可完成很多操作。
• 它是实时的：编写一次查询，每次数据发生变化时都可以刷新它，并且它将转换您更新的新数据。

让我分享一个例子：

想象一下，您有 100 个 Excel 文件，其中包含来自 100 个城市的数据，现在您的老板希望您使用这 100 个文件中的所有数据创建一个报告。好吧，如果您决定手动打开每个文件并复制并粘贴这些文件中的数据，那么您至少需要一个小时。

但使用 Power Query，您可以在几分钟内完成。你感到兴奋吗？好的。

在本教程的后面部分，您将通过许多示例学习如何使用 Power Query，但首先您需要了解其概念。

电力需求的概念

要学习 power query，您需要了解其概念，分为 3 个步骤：

1. 获取数据

Power query 允许您从不同的来源获取数据，例如 Web、CSV、文本文件、一个文件夹中的多个工作簿以及我们可以存储数据的许多其他来源。

2. 转换数据

将数据输入到 Power 查询后，您可以使用一大堆选项来转换和清理数据。它会为您执行的所有步骤创建查询（按照一步一步的顺序）。

3. 加载数据

从高级查询编辑器中，您可以将转换后的数据加载到工作表中，也可以直接创建数据透视表或数据透视图或创建纯数据连接。

Power Query 在哪里（如何安装）？

下面您可以了解如何在不同版本的 Microsoft Excel 中安装 Power Query Access。

Excel 2007

如果您使用的是 Excel 2007，很抱歉，PQ 不适用于该版本，因此您需要升级到latest version的 Excel（Excel for Office 365、Excel 2019、Excel 2016、Excel 2013、Excel 2010）。

Excel 2010 和 Excel 2013

对于 2010 和 2013 年，您需要单独安装一个加载项，您可以从此链接下载该加载项，安装后您将在 Excel 功能区中看到一个新选项卡，如下所示：

• 首先，从这里（微软官方网站）下载插件。
• 下载文件后，将其打开并按照说明进行操作。
• 之后，您将自动在 Excel 功能区上看到“Power Query”选项卡。

如果不知何故没有出现这个“POWER QUERY”选项卡，则无需担心。您可以使用 COM 加载项选项添加它。

• 转到文件选项卡 ➜ 选项 ➜ 加载项。

• 在“加载项”选项中，选择“COM 加载项”并单击“开始”。
• 之后，选中“Microsoft Power Query for Excel”框。

• 最后，单击“确定”。

Excel 2016、2019、Office 365

如果您使用的是 Excel 2016、Excel 2019 或拥有 OFFICE 365 订阅，它已经在“数据”选项卡上，作为一个名为“GET & TRANSFORM”的组（我喜欢这个名字，你呢？）。

苹果电脑

如果您在 Mac 上使用 Excel，恐怕没有为此提供的强大查询插件，您只能刷新现有查询，但无法创建新查询，甚至无法编辑查询（ 链接）。

电源查询编辑器

Power Query 有自己的编辑器，您可以在其中获取数据、执行创建查询的所有步骤，然后将其加载到电子表格中。要打开 Power Query 编辑器，您需要转到“数据”选项卡，然后在“获取和转换”➜“获取数据”➜“启动 Power Query 编辑器”中。

以下是打开编辑器时将看到的第一个预览。

现在，让我们详细探讨每个部分：

1.丝带

让我们看看所有可用的选项卡：

• 文件：从“文件”选项卡中，您可以加载数据、删除编辑器并打开查询参数。
• 主页：在“主页”选项卡中，您可以选择管理加载的数据，例如删除和移动列和行。
• 转换：此选项卡包含转换和清理数据所需的所有选项，例如合并列、转置等。
• 添加列：在这里您可以选择向高级编辑器中的数据添加新列。
• 视图：从此选项卡中，您可以更改高级查询编辑器视图和加载的数据。

2. 应用步骤

在编辑器的右侧，您有一个查询设置窗格，其中包括查询名称和按顺序应用的所有步骤。

当您右键单击某个步骤时，您会看到一个可以执行的选项列表，例如重命名、删除、编辑、上移或下移等。当您单击某个步骤时，编辑器将带您进入该步骤完成的转换。

请看下面，您总共应用了所有五个步骤，当我单击第四步时，它会将我带到第四步中的转换，其中列名称未更改。

3. 问题

左侧的查询窗格列出了工作簿中当前的所有查询。它基本上是一个可以管理所有查询的地方。

右键单击查询名称时，您可以看到可以使用的所有选项（复制、删除、重复等）

您还可以通过简单地右键单击查询窗格中的空白区域，然后选择数据源选项来创建新查询。

4.公式栏

正如我所说，每次您在编辑器中应用一个步骤时，它都会为该步骤生成 M 代码，您可以在公式栏中看到此代码。您只需单击编辑栏即可编辑代码。

一旦您学会了如何使用 M 代码，您还可以通过编写代码来创建步骤，然后只需单击“FX”按钮即可输入自定义步骤。

5. 数据概览

数据预览区域看起来像 Excel 电子表格，但与普通电子表格略有不同，在普通电子表格中您可以直接编辑单元格或数据。当您将数据加载到编辑器中时（我们稍后会这样做），它会显示所有带有标题的列和列名称，然后显示带有数据的行。

在每列的顶部，您可以看到该列数据的数据类型。当您将数据加载到编辑器中时，高级查询会自动将正确的数据类型（几乎每次）应用于每列。

您可以单击列标题左上角的按钮来更改应用于该列的数据类型。它包含您可以从中提取的所有数据类型的列表。

在列标题的左侧，有一个过滤器按钮，可用于过滤列值。注意：当您过滤列的值时，高级查询会将其视为单个步骤并将其列在“应用的步骤”中。

如果右键单击列标题，您可以看到一个菜单，其中包含可用于转换数据的选项列表，并使用其中一个选项和它存储的 PQ 作为应用过程中的一个步骤。不是。

Power Query 的数据源

Power Query 最好的部分是您能够从多个来源获取数据并转换该数据，然后将其加载到电子表格中。当您单击 GET & TRANSFORM 中的获取数据时，您可以看到可加载到编辑器中的数据源的完整列表。

现在让我们看看一些数据源：

• 来自表/范围：使用此选项，您可以直接从活动工作表将数据加载到高级查询编辑器中。
• 来自工作簿：来自计算机上的另一个工作簿。您只需使用打开的对话框找到该文件，它就会自动从此文件中获取数据。
• 从文本/CSV ：从文本文件或逗号分隔文件获取数据，然后您可以将其加载到电子表格中。
• 来自文件夹：它从文件夹中获取所有文件并支持强大的查询编辑器中的数据。 （请参阅：合并一个文件夹中的 Excel 文件）。
• 来自网络：使用此选项，您可以从网址获取数据，假设您有一个存储在网络上的文件，或者您有一个需要从中获取数据的网页。

如何将数据加载到 Power Query 编辑器

现在让我们学习如何将数据加载到强大的查询编辑器中。这里有学生姓名及其分数的列表（ 链接）。

您将直接从电子表格加载数据，因此您需要先打开文件，然后按照以下步骤操作：

• 首先，将 Excel 表格应用于数据（即使您不这样做，Excel 也会在将数据加载到 PQ 编辑器之前为您完成此操作）。
• 现在，选择表中的一个单元格，然后单击“来自表/范围”（获取和转换数据选项卡）。

• 单击该按钮后，Excel 会确认数据范围以对其应用 Excel 表格。

• 此时，Power Query 编辑器中已包含数据，如下所示。

• 在这里你可以看到：
  • 在公式栏中，PQ 为您刚刚加载到编辑器中的表格生成了 M 代码。
  • 在编辑器的左侧有查询窗格，其中有查询列表。
  • 在右侧的查询设置中，您有一个名为“应用的步骤”的部分，其中列出了所有步骤。注意：你一定认为你没有做过任何“修改类型”，但是那里有一个叫做“修改类型”的步骤。让我告诉您POWER QUERY 的智能，当您将数据加载到编辑器中时，它会自动检查并为所有列应用正确的数据类型。

Power Query 示例（提示和技巧）

您可以学习如何执行通常在 Excel 中使用函数公式执行的一些基本任务，但使用 PowerQuery，您只需单击几下即可完成：

1. 替换值

您有一个值列表，并且想要用其他值替换一个或多个值。那么，在Power Query的帮助下，您可以立即创建查询并替换这些值。

在下面的列表中，您需要将我的名字“Puneet”替换为“Punit”。

• 首先，在高级查询编辑器中编辑列表。
• 之后，在 Power Query 编辑器中，转到“转换选项卡”并单击“替换值”。

• 现在，在“要查找的值”中输入“Puneet”，在“替换为”中输入“Punit”，然后单击“确定”。

• 单击“确定”后，所有值都将替换为新值，现在单击“关闭并加载”将数据加载到电子表格中。

2. 数据排序

就像正常排序一样，您可以使用 PowerQuery 对数据进行排序，我使用的名称列表与上面示例中使用的名称列表相同。

• 首先，将数据加载到强大的查询编辑器中。
• 在“主页”选项卡中，您有两个排序按钮（升序和降序）。
• 单击这些按钮之一进行排序。

3.删除列

假设您在某处有一些数据，并且需要从中删除一些列。关键是每次添加新数据时都必须删除这些列，对吧？但是，电源查询可以解决这个问题。

• 选择要删除的列或多列。

• 现在右键单击并选择“删除”。

快速提示：还有一个“删除其他列”选项，您可以在其中删除所有未选择的列。

4. 拆分列

就像文本到列选项一样，强大的查询中有“拆分列”。让我告诉你它是如何工作的。

• 选择列并转到“主页”选项卡 ➜ 转换 ➜ 拆分列 ➜ 按分隔符。

• 从下拉列表中选择自定义并在其中输入“-”。
• 现在，您可以使用三种不同的选项来拆分列。
  • 最左边的分隔符
  • 最右边的分隔符
  • 每次出现分隔符

如果单元格中只有一个分隔符，则所有三个分隔符的工作方式相同，但如果您有多个分隔符，则应进行相应选择。

5. 重命名列

您只需右键单击然后单击“重命名”即可重命名列。

快速提示：假设您有一个重命名列的查询，而其他人错误地重命名了它。您只需单击一下即可恢复该名称。

6. 重复列

在 Power Query 中，有一个简单的选项可以创建重复列。只需右键单击要为其创建重复列的列，然后单击“重复列”。

7. 转置列或行

在幂查询中，换位简直就是小孩子的游戏。是的，只需轻轻一按。

• 将数据加载到高级查询编辑器后，您所需要做的就是选择列或行。
• 转到变换选项卡 ➜ 表格 ➜ 转置。

8. 替换/删除错误

通常，要替换或删除 Excel 中的错误，可以使用“查找和替换”选项或 VBA 代码。但在 PowerQuery 中就容易得多。查看下面有错误的列，您可以删除并替换它们。

当您右键单击该列时，您将有两个选项。

• 替换错误
• 删除错误

9. 更改数据类型

您的列中有数据，但格式不正确。所以每次都需要改变它的格式。

• 首先，在强大的查询编辑器中编辑数据。
• 之后，选择该列并转到“转换”选项卡。
• 现在，从数据类型中选择“日期”作为类型。

10. 添加示例中的列

在高级查询中，有一个选项可以添加示例列，该示例列实际上不是与当前列相关的示例。

让我举一个例子：

假设您需要日期列中的日期名称。您可以使用“从示例添加列”，而不是使用公式或任何其他可以使用的选项。

操作方法如下：

• 右键单击某一列，然后单击“从示例添加列”。

• 在这里您将得到一个空列。单击列中的第一个单元格即可获取可以插入的值的列表。

• 选择“日期中的星期几的名称”，然后单击“确定”。

繁荣！您的新专栏就在这里。

11. 更换盒子

您可以使用以下选项来更改 PowerQuery 中的文本大小写。

• 微小的
• 大写
• 每个单词大写

您可以通过右键单击列并选择上面三个选项之一来完成此操作。或者，转到转换选项卡 ➜ 文本列 ➜ 格式。

12.修剪和清洁

要清除数据或删除不需要的空格，您可以使用 PowerQuery 中的 TRIM 和 CLEAN 选项。步骤很简单：

• 右键单击一列或选择所有列（如果有多列）。
• 转到转换选项卡 ➜ 文本列 ➜ 格式。
  1. TRIM：删除单元格末尾和开头的空格。
  2. CLEAN：从单元格中删除非打印字符。

13.添加前缀/后缀

因此，您有一个值列表，并且您想在该列表中的每个单元格中添加前缀/后缀。在 Excel 中，您可以使用串联方法，但在 PowerQuery 中，这两种方法都有一个简单易用的选项。

• 首先，选择需要添加前缀/后缀的列。
• 然后转到转换选项卡➜文本列➜格式➜添加前缀/添加后缀。

• 单击任何选项后，您将看到一个用于输入文本的对话框。

• 输入文本后，单击“确定”。

14. 提取值

如果您是公式极客，那么我确信您同意我的观点，即从单元格中提取文本或数字需要组合不同的函数。但 PowerQuery 已经解决了其中许多问题。您有七种方法从单元格中提取值。

15.仅日期或时间

经常发生的情况是，日期和时间都在一个单元格中，但您需要其中之一。

• 选择包含组合日期和时间的列。
• 如果你想：
  • 日期：右键单击➜变换➜仅日期。
  • 时间：右键单击➜变换➜仅时间。

16.合并日期和时间

现在您知道如何分隔日期和时间了。但接下来，您需要知道如何将它们结合起来。

• 首先，选择日期列，然后单击“仅日期”选项。
• 之后，选择两列（日期和时间）并转到“转换”选项卡，然后从“日期和时间列”组中转到“日期”并单击“合并日期和时间”。

17. 四舍五入的数字

以下是用于舍入数字的以下选项。

• 向下舍入：将数字向下舍入。
• 四舍五入：对数字进行四舍五入。
• 四舍五入：您可以选择小数点后的四舍五入程度。

步骤如下：

• 选择该列并右键单击 ➜ 变换 ➜ 圆形。
  1. 向下舍入：将数字向下舍入。
  2. 四舍五入：对数字进行四舍五入。
  3. 四舍五入：您可以选择小数点后的四舍五入程度。

注意：当您选择“#3 Round”选项时，您必须输入要四舍五入的小数位数。

18. 计算

您可以使用一些选项来执行计算（很多）。您可以在“转换”选项卡（“数字列”组中）中找到所有这些选项。

• 基本的
• 统计数据
• 科学家
• 三角学
• 圆形的
• 信息

要执行任何这些计算，您必须选择该列，然后选择该选项。

19. 分组依据

假设您有一个大型数据集并想要创建一个汇总表。您需要执行以下操作：

• 在“变换”选项卡中，单击“分组依据”按钮，您将看到一个对话框。

• 现在，从此对话框中选择要分组的列，然后添加名称，选择操作和包含值的列。

• 最后，单击“确定”。

注意： “分组依据”选项中还有一些高级选项，可用于创建多级分组表。

20. 删除负值

在我的一篇博客文章中，我列出了七种消除负号的方法，强力查询就是其中之一。右键单击一列并转到“变换”选项，然后单击“绝对值”。

这会立即删除值中的所有负号。

如何将数据加载到电子表格中

转换数据后，您可以将其加载到电子表格中并用于进一步分析。在主页选项卡上有一个名为“关闭并加载”的按钮，当您单击它时，您会看到一个提供其他选项的下拉列表：

• 关闭并加载
• 关闭并加载

• 单击该按钮后，它将显示以下选项：

• 选择您希望如何在电子表格中显示此数据。
  • 绘画
  • 数据透视表报告：
  • 工作台枢轴
  • 只创建一个连接
  • 您想将数据放在哪里？
    • 现有工作表
    • 新工作表。
  • 将此数据添加到数据模型中。
• 只需选择表选项和新工作表，不选中数据模型，然后单击“确定”。

• 单击“确定”后，它会添加一个包含数据的新工作表。

更多示例供学习

自动刷新查询

在我在这里引用的所有例子中，这是最重要的。创建查询时，可以自动刷新它（可以设置计时器）。

步骤如下：

• 在“数据”选项卡中，单击“查询和连接”，您将在窗口右侧看到“查询和连接”窗格。

• 现在右键单击查询并选中“刷新间隔”并输入分钟。

如何在 Power Query 中使用公式和函数

就像您可以在 Excel 电子表格中使用函数和公式一样，高级查询也有自己可以使用的函数列表。 PowerQuery 中函数和公式的基础知识与 Excel 的电子表格函数相同。

在 PQ 中，您需要添加新的自定义列来添加函数或公式。

让我们举个例子：在下面的数据中（已经在 PQ 编辑器中），您有名字和姓氏（下载链接） 。

想象一下，您需要合并两个名称并为全名创建一个列。在这种情况下，您可以输入一个简单的公式来连接两列的名称。

• 首先，转到“添加列”选项卡，然后单击“自定义列”。

• 现在，在自定义列对话框中，输入新列的名称“全名”或您想要为新列命名的任何名称。

• • 自定义列公式是您需要输入公式的地方。所以输入下面的公式：

 [First Name]&" "&[Last Name] 

• 当您在“自定义列公式”中输入公式时，PQ 会检查您输入的公式并显示一条消息“未检测到语法错误”，如果存在错误，它将根据错误类型显示一条错误消息。
• 输入公式并且没有错误后，只需按“确定”即可。
• 现在，数据末尾有一个新列，其中包含两列（名字和姓氏）的值。

如何使用 Power Query 中的函数

同样，您也可以在添加自定义列时使用函数，并且 Power Query 有大量可供您使用的函数。

让我们通过一个简单的示例来了解如何使用函数。我继续上面的示例，其中我们添加了一个结合名字和姓氏的新列。

但现在您需要将此列中的全名文本转换为大写。您可以使用的函数是“ Text.Upper ” 。顾名思义，它将文本转换为大写文本。

• 首先，转到“添加列”选项卡并单击自定义列。

• • 现在，在自定义列对话框中，在自定义列公式框中输入列名称和以下公式：

 Text.Upper([Full Name]) 

• 当您单击“确定”时，它会创建一个新列，其中所有名称均为大写。
• 接下来是删除旧列并重命名新列。因此，右键单击第一列并选择删除。

• 最后，将新列重命名为“Full Name”。

在添加新列时，您可以在 power query 中使用总共 700 个函数，这里是Microsoft 为这些函数提供的完整列表，请查看它们。

如何修改 PQ 中的查询

如果您想要对工作簿中已有的查询进行更改，只需对其进行编辑，然后进行这些更改即可。在“数据”选项卡中，有一个名为“查询和连接”的按钮。

单击此按钮时，它将在右侧打开一个窗格，其中列出了当前工作簿中的所有查询。

您可以右键单击查询名称并选择编辑，您将在强大的查询编辑器中对其进行编辑。

编辑查询时，您可以看到之前执行的所有步骤都列在“应用的步骤”中，您也可以编辑这些步骤，也可以执行新的步骤。

完成编辑后，您只需单击“关闭并加载”按钮即可。

导出和导入连接

如果您有一个用于查询的连接，现在想要与其他人共享该连接，则可以将该连接导出为 odc 文件。

在查询表上有一个名为“导出连接”的按钮，当您单击它时，您可以将该查询的连接保存到系统中。

如果您想导入由其他人共享的连接，您只需转到“数据”选项卡，然后在“获取和转换”中单击“现有连接” 。

然后单击“浏览更多”按钮，您可以在其中找到与您共享的连接文件并将其导入到您的工作簿中。

PowerQuery语言（M代码）

正如我之前提到的，对于您在 PowerQuery 中执行的每一步，它都会生成一个代码（在后端），称为M Code 。在“主页”选项卡中，有一个名为“高级编辑器”的按钮，您可以使用它来查看代码。

当您单击高级编辑器时，它将显示代码编辑器，代码如下所示：

M 是一种区分大小写的语言，与所有其他语言一样，它使用变量和表达式。代码的基本结构如下所示，其中代码以 LET 表达式开头。

在此代码中，我们有两个变量以及为它们设置的值。最后，为了获取值，使用了IN表达式。现在，当您单击“确定”时，它将返回结果中分配给变量“Variablename”的值。

查看此资源以了解有关Power Query 语言的更多信息。

在最后

您无法避免 POWER QUERY。如果您这样认为，我们使用Excel 函数或 VBA 代码所做的很多事情都可以使用它自动化，我相信本教程将激励您越来越多地使用它。