安美资源网

这个全面的 Power Query 教程是您掌握 Excel 中高级数据转换的切入点。您将学习如何从不同来源导入数据、根据您的要求转换和合并数据、用 M 语言编写公式、自动查询刷新等等。

在不断发展的世界中，数据对于我们所做的几乎所有事情都至关重要，从做出关键的业务决策到解决有关最佳披萨配料的友好辩论。然而，信息通常来自不同的来源，以不同的格式，有时看起来就像幼儿的玩具箱一样井井有条。那么，难题来了 - 我们如何在 Excel 中导入、转换和分析数据，而无需花费大量时间进行手动操作或编写复杂的公式？解决方案是 Power Query，它对于任何数据分析师来说都是一个非常宝贵的工具，可以节省数小时的时间，减少手动错误，并将难以驾驭的信息混乱转化为和谐的见解交响乐。

目录

Excel 中的 Power Query 是什么？

Power Query 有何用途？

如何将 Power Query 添加到 Excel

电源查询编辑器

高级编辑器

如何使用电源查询

获取数据

转换

合并和组合

写公式

加载到 Excel

电源查询刷新

更新数据预览

刷新 Power Query 结果

自动刷新电源查询

Excel 中的 Power Query 是什么？

Power Query也称为Get & Transform，是 Microsoft Excel 和 Power BI 程序中的多功能工具，可让您从各种来源导入数据并转换该数据以满足您的特定需求。

它最初作为 Excel 2010 的加载项引入，后来成为 Excel 2016 及后续版本中的本机功能。Power Query (PQ) 提供了一个用户友好的界面，用于连接到各种数据源，调整、合并和清理数据，然后将其加载到 Excel 中，以数据透视表、图表、仪表板的形式进行深入分析和精美报告， 和更多。

从本质上讲，Power Query 使 Excel 用户能够轻松地从广泛的来源中提取、操作和组织数据，使其成为处理复杂数据集的专业人员的游戏规则改变者。

Power Query 可以用来做什么？

Power Query 是一款具有多种应用程序的多功能工具。以下是一些常见用途：

数据提取。连接到各种数据源，包括数据库、Excel 文件、文本文件和在线服务。

数据转换。多种数据转换功能可将您的数据重塑为更适合分析的结构，例如过滤、排序、分组、合并、将列拆分为多个字段、创建计算列等。

数据清洗。数据质量对于准确分析至关重要。PQ 提供了识别和纠正错误、删除重复项、修剪多余空格、填充缺失值和标准化格式的工具，确保您的数据干净可靠。

结合数据。PQ 可以轻松地将来自不同位置的数据组合并合并为一个有凝聚力的数据集，从而促进全面分析。

自动化工作流程。Power Query 记录您采取的每一步，创建一个可以在以后轻松修改或重复使用的查询。当处理重复数据源或当您希望以最小的努力使分析保持最新时，这特别有用。此外，您还可以随时刷新查询以根据需要或按计划从源获取最新数据。

现在我们已经了解了 Power Query 是什么及其潜在应用，让我们看看如何在 Excel 中获得这一有用的功能。

如何在 Excel 中获取 Power Query

Power Query 工具适用于从 Excel 2010 到 Excel 365 的所有现代版本。根据您使用的具体版本，您可能需要将其安装为加载项或直接从功能区访问它。

笔记。。在 Excel 2007 及更早版本中，不支持 Power Query。

如何在 Excel 2016 中打开 Power Query – 365

在较新版本的 Excel 中，Power Query 是一项内置功能，无需任何额外安装即可轻松访问。它可以在“数据”选项卡的“获取和转换数据”组下找到。要开始使用其功能，只需单击该组中的任何命令即可。

如何将 Power Query 添加到 Excel 2013 和 2010

在较旧的 Excel 版本中，默认情况下不包含 Power Query，但您可以从Microsoft 网站下载并安装它作为加载项。

安装加载项并重新启动 Excel 后，您应该会在功能区中看到一个标有Power Query的新选项卡。要开始从各种来源导入数据，请单击以下按钮之一：从文件、从数据库、从 Web或从其他来源。

提示。请务必下载适合您的 Excel 版本和操作系统的正确包。要检查您的版本和位数（32 位或 64 位），请转到“文件” > “帐户” > “关于 Excel”。

Excel 中的 Power Query 编辑器

所有数据转换的核心和灵魂都在 Power Query 编辑器中。它是 Excel 中的一个单独的图形界面，可以托管您的查询。它是主要工作区，您将花费大部分时间来优化和重塑数据。

您可以通过两种方式访问编辑器：

• 在“数据”选项卡的“获取和转换”组中，单击“获取数据”并选择“启动 Power Query 编辑器”。
• 或者，在“数据”选项卡上，单击“查询和连接”按钮以打开此窗格。然后，双击窗格中的查询以启动编辑器。

当您连接到数据源或创建新查询时，编辑器会自动打开。

以下是 Power Query 编辑器的六个主要元素：

1. 丝带。在编辑器的顶部，您将找到功能区，它通过五个主要选项卡组织各种命令和选项：文件、主页、转换、添加列和视图。每个选项卡都有不同的命令组，这些命令与您要执行的特定任务相关。
2. 查询。这是编辑器窗口左侧的导航窗格，显示工作簿中的所有查询。您可以使用此窗格重命名、复制、引用、删除、重新排序或将查询组织到组中。只需右键单击查询即可查看可用选项。
3. 数据预览。数据预览窗格位于前面和中间，在实时应用查询中的每个步骤后提供数据集的快照。虽然其主要目的是促进数据检查，但它还允许您执行一些基本转换，例如对列进行排序和过滤、更改数据类型等。
4. 属性。在编辑器窗口右侧的“查询设置”窗格中，您将找到“属性”部分。它显示所选查询的属性，例如其名称、描述和加载设置。单击“所有属性”链接可根据需要查看和修改这些参数。
5. 应用步骤：在“属性”部分下方，有一个“应用步骤”区域，其中提供了您在数据转换期间所采取的所有操作的按时间顺序排列的列表。您可以使用此列表来查看、编辑、重新排序或删除查询中的任何步骤。您还可以单击特定步骤以查看它如何影响数据预览区域中的数据。
6. 方程式吧。这是数据预览区域顶部的栏，显示查询中所选步骤的公式或表达式。您可以使用此栏使用 Power Query M 语言编辑或编写自己的公式。

高级 Power Query 编辑器

高级编辑器是一个基于文本的界面，允许您编写和编辑 M 代码（Power Query 背后的语言）。将其视为通往 Power Query 转换后台的 VIP 通行证。标准 PQ 界面使您能够通过点击操作来重塑数据，而高级编辑器则可以让您精确控制数据转换。

您在 Power Query 中执行的每个操作都会使用 M 语言在后台生成相应的代码。您可以在高级编辑器中查看和编辑此代码。要打开它，请单击“主页”选项卡上“查询”组中的“高级编辑器”按钮。

如何在 Excel 中使用 Power Query

本节概述如何使用 Power Query 进行数据分析。您将了解导入、转换、组合和导出数据所涉及的主要步骤。这些步骤是：

• 获取数据– 连接到不同的数据源并将其加载到 Power Query 中。
• 转换– 清理和重塑数据以适应您的特定要求。
• 合并——将来自不同来源的数据合并到一个有组织的数据集中。
• 编写公式– 使用 Power Query 公式语言编写自定义公式。
• 加载– 将最终数据表导出到 Excel 并手动或自动刷新。

获取数据

要开始在 Power Query 中处理数据，您需要从源导入数据。PQ 支持许多不同的数据源，例如文件、数据库、在线服务等。

可以直接从 Excel 功能区访问三个最常见的源 - 在“数据”选项卡上的“获取和转换数据”组中。这些主要来源是：

• 来自文本/CSV - 如果您的数据存储在文本或 CSV 文件中，请使用此选项。
• 从 Web - 直接从网页导入数据的理想选择。
• 来自表/范围- 适用于 Excel 中已有的、在表或范围内组织的数据。

如果您需要从其他来源导入数据，请单击“获取数据”按钮，然后探索更多选项：

• 从文件- 导入各种文件类型，例如 Excel 工作表、TXT/CSV、XML、JSON 和 PDF。
• 从数据库- 连接到 SQL Server 或 Microsoft Access 等数据库。
• 从 Azure - 访问来自 Azure SQL 数据库、Azure Data Lake Storage 和其他 Azure 服务的数据。
• 从 Power Platforms - 从 Microsoft Power BI 服务导入数据。
• 来自其他来源- 此类别提供更多选项，包括来自 Excel 表/范围、网络、OData 源、ODBC、OLEDB 等的数据。

例如，要使用 CSV 文件作为数据源，您可以单击功能区上的“来自 文本/CSV”按钮，或者采取更长的方式：获取数据>从文件>从文本/CSV。如果您有多个要合并的 CSV 文件，请将它们保存在一个文件夹中，然后选择“来自文件夹”选项。

在下一个屏幕中，您将看到导入数据的预览以及以下选项：

• 合并（仅适用于多个文件）- 允许您合并来自不同源和文件类型的不同数据集。
• 加载- 允许您将数据作为表、数据透视表或连接直接加载到 Excel 工作表中。
• 转换数据- 打开 Power Query 编辑器，您可以在其中进行各种转换，例如过滤、拆分、分组、旋转等。这是您最常使用的选项。
• 取消- 允许您取消导入过程并关闭对话框。

如果您的目标是重塑数据，那么“转换数据”选项是合乎逻辑的下一步。

转换数据

将数据加载到 Power Query 后，您就可以根据自己的需求来优化数据。所有数据转换都在名为Power Query Editor 的单独界面中完成，其中有四个主要选项卡可供探索。您可以将数据转换视为雕刻。您从原材料开始，将其塑造成适合您目的的形式，并允许您创建有吸引力的报告和仪表板。

以下是您可以使用 Power Query 执行的一些常见转换：

• 排序和过滤。按升序或降序排列数据并过滤掉不需要的行以关注最重要的信息。
• 添加、删除或复制列。通过添加新列、删除不必要的列或在需要时复制现有列来修改数据集。
• 更改数据类型。您可以指定每列中的数据类型，例如文本、数字、日期、时间等。
• 将行转置为列，反之亦然。更改数据的方向，将行转换为列，以便更好地分析和可视化。
• 删除重复的行。您可以消除所有列中具有相同值的任何行，以减少数据大小并避免重复计算。
• 修剪并清洁。从文本值中删除前导或尾随空格，或不属于数据的任何额外字符，以提高数据集的质量和可读性。
• 提取值。根据分隔符、位置或模式提取部分文本值。例如，您可以从全名列中提取名字，或从电子邮件地址列中提取域名。
• 拆分列。根据分隔符或特定模式将一列分为多列。例如，您可以将日期列拆分为年、月和日列。
• 替换或删除值或错误。您可以将数据中的任何值或错误替换为其他值，例如空白、零或自定义值。您还可以删除任何包含错误或缺失值的行。
• 添加条件列。根据预定义条件创建新列，从而实现动态数据分类。例如，您可以创建一个根据数值范围分配类别的列。
• 添加示例中的列。利用示例指导 Power Query 创建具有所需数据模式的新列。PQ 将尝试推断示例背后的逻辑并将其应用于其余行。
• 对数据进行分组和汇总。按组汇总数据，计算总和、平均值或其他统计指标，以获得更深入的见解。

这些转换使您能够将数据雕刻成艺术品，不仅实用，而且优雅且富有洞察力。详细说明请参阅这篇文章：如何在 Excel 中使用 Power Query – 实际示例。

笔记。您在编辑器中所做的所有修改共同形成一个查询，构成原始数据的新表示。刷新查询时，每个步骤都会自动运行，无需手动重复相同的操作。但是，要在 Excel 中使用精炼数据，您必须将其加载到工作簿中。

合并和组合

Power Query 最有用的功能之一是能够将不同来源的数据组合到单个表中。有两种主要方法可以做到这一点：追加和合并。

• 追加会将一个表中的行添加到另一个表中，只要它们具有相同的列即可。例如，您可以将不同地区或月份的销售数据附加到单个表中。
• 合并基于一个或多个公共列（唯一标识）连接两个表，就像 SQL 连接一样。例如，您可以根据学生姓名或 ID 列合并来自不同课程的学生数据。

要在 Power Query 编辑器中组合数据，请使用“主页”选项卡上“组合”组中的“合并查询”或“追加查询”命令。

要组合 Power Query 中尚未包含的数据，请单击Excel 功能区“数据”选项卡上的“获取数据” > “组合查询” ，然后选择“合并”或“追加”。

有关通过公共列合并两个表的详细端到端示例，请参阅如何使用 Power Query 连接表。

在 Power Query 中编写公式

与在 Excel 工作表中使用函数和公式类似，PQ 也有自己的一组函数来执行各种计算。这些公式是用 Power Query 公式语言（也称为 M）编写的。现在，这里有个好消息：您无需学习复杂的 M 语言语法即可构建自定义公式。与传统编码不同，Power Query 使用可视化界面使其变得非常简单且用户友好。

让我们举个例子。假设您想要创建一个列，根据“捐赠”和“税率”列中的值计算扣税后的净捐赠金额。步骤是：

1. 转到“添加列”选项卡并单击“自定义列”。
2. 在“自定义列”对话框中，输入“Net Donation”或任何您想要的新列名称
3. 您可以在“自定义列公式”框中编写公式本身。因此，在其中输入以下公式：

=[Donation] * (1 - [Tax Rate])

要引用公式中的特定列，请在对话框右侧的“可用列”下选择其名称，然后单击“插入”，或者只需双击列名称。

1. 当您输入公式时，Power Query 会对其进行检查，如果没有发现错误，则显示消息“未检测到语法错误”。如果公式中有错误，它将显示一条错误消息，指示错误类型。
2. 单击“确定”，公式将应用于新列中的所有行。

就是这样！您已在 Power Query 中成功创建了带有公式的自定义列。公式栏显示其语法：

将数据加载到 Excel

您应用于数据连接的所有转换共同构成一个查询，它是原始（且未更改）数据源的新表示。当您刷新查询时，每个步骤都会自动运行。查询取代了在 Excel 中手动连接和调整数据的需要。

在 Power Query 中执行必要的数据转换后，最后一步是将结果数据加载到 Excel 工作簿中。您有两个选择：

• 在 Power Query 编辑器中 - 使用“主页”选项卡上的“关闭”和“加载”命令。
• 在 Excel 工作表的“查询和连接”窗格中- 右键单击查询并选择“加载到”。

以下步骤向您展示如何从 Power Query 编辑器执行此操作：

1. 在左侧的查询窗格中，选择要加载的查询。
2. 在“主页”选项卡上的“关闭”组中，单击“关闭并加载”旁边的箭头，然后选择以下选项之一：

• 关闭并加载– 将查询作为表加载到新工作表中（默认设置）。关闭并加载到... – 允许您指定输出的格式和位置。

如果您选择第一个选项，则加载过程已完成。如果您选择第二个选项，请继续执行下一步。

1. 在“导入数据”对话框中，选择导入数据的方式（表、数据透视表或数据透视图）以及数据的放置位置（在新工作表或现有工作表中）。然后单击“确定”完成该过程。

Excel Power Query 刷新

使用 Power Query 时，您可能需要不时刷新查询才能获取最新数据。以下是一些方法：

刷新 Power Query 预览

要更新 Power Query 预览中显示的数据，只需单击编辑器“主页”选项卡上的“刷新预览”按钮即可：

• 刷新预览– 更新当前查询的预览结果。
• 全部刷新– 更新编辑器中所有查询的预览结果。

这将从源重新加载数据以反映对原始数据所做的任何更改。您对查询步骤所做的所有更改都将自动应用于新数据。

在 Excel 中刷新 Power Query 结果

要刷新已加载到 Excel 工作簿中的 Power Query 结果，请执行以下操作之一：

• 要更新特定查询，请右键单击“查询和连接”窗格中的查询名称，然后选择“刷新”。
• 要刷新工作簿中的所有查询，请按Ctrl + Alt + F5捷径。或者，单击“数据”选项卡上“查询和连接”中的“全部刷新”按钮

如何在不打开文件的情况下自动刷新Power Query

如果需要 Excel 中的 Power Query 自动刷新而无需打开源文件，则可以按指定的时间间隔安排这些刷新。设置方法如下：

1. 在“数据”选项卡上，单击“查询和连接”。这将打开Excel 窗口右侧的“查询和连接”窗格。
2. 在窗格中，右键单击要计划自动刷新的查询，然后选择“属性”。
3. 在“查询属性”对话框中，转到“使用情况”选项卡，选中标有“每隔…分钟刷新一次”的框，然后输入分钟数。
4. 要在打开 Excel 文件时刷新查询，请选中打开文件时刷新数据复选框（默认情况下未选中）。
5. 单击“确定”保存更改。

就是这样！从现在开始，您的查询将在您每次打开工作簿时或在指定的时间间隔（以先到者为准）自动刷新。这样，您就可以始终在 Excel 文件中获得最新数据，而无需手动刷新。

8 涪

这些是 Excel Power Query 的基本要素 - 让数据为您服务的秘密武器。现在，您可以轻松连接到各种数据源，转换、组合和清理数据，然后将其加载到 Excel 或 Power BI 中。并且不要忘记最重要的一点 - 自动刷新可以让您的数据始终保持最新，即使您没有查看:)

%……Zop0-`12345`1`1234567890,,,,,,cdxseklj ……//*-