什么是 Power Query？

 

Power Query 是一个数据转换和数据准备引擎。 Power Query 有一个用于从源获取数据的图形界面和用于应用转换的 Power Query 编辑器。 由于该引擎可用于许多产品和服务，因此存储数据的目标位置取决于 Power Query 的使用位置。 使用 Power Query，可以对数据进行提取、转换和加载 (ETL) 处理。

 

企业用户将多达 80% 的时间花在数据准备上，这会延误分析和决策工作。 造成这种情况的挑战有很多，Power Query 可帮助解决其中许多难题。

Power Query 用户体验通过 Power Query 编辑器用户界面提供。 此界面的目的是通过与一组用户友好的功能区、菜单、按钮和其他交互式组件进行交互，帮助你应用所需的转换。

Power Query 编辑器是主要的数据准备体验，你可以在其中连接到各种数据源，并通过预览数据和从 UI 中选择转换来应用数百个不同的数据转换。 无论基础数据源有什么限制，这些数据转换功能在所有数据源中都是通用的。

通过与 Power Query 界面的组件交互创建新转换步骤时，Power Query 会自动创建完成转换所需的 M 代码，因此无需编写任何代码。

目前，有两种 Power Query 体验可用：

• Power Query Online - 可在 Power BI 数据流、Microsoft Power Platform 数据流、Azure 数据工厂处理数据流等集成中找到，通过在线网页提供体验。
• Power Query for Desktop - 存在于 Power Query for Excel 和 Power BI Desktop 等集成中。

 

 

Power Query 中的转换引擎包含许多预生成的转换函数，这些函数可通过 Power Query 编辑器的图形界面使用。 这些转换可以像删除列或筛选行一样简单，也可以是常见的将第一行用作表头。 还有高级转换选项，例如合并、追加、分组、透视和逆透视。

所有这些转换都可以通过选择菜单中的转换选项，然后应用该转换所需的选项来实现。 下图显示了 Power Query 编辑器中可用的一些转换。

有关详细信息，请参阅：快速入门：使用 Power BI Desktop 中的 Power Query。

Power Query 可在 Power BI 和 Excel 等许多产品中使用。 但是，在某个产品中使用 Power Query 会将其用途限制在该特定产品中。 数据流是 Power Query 体验的产品无关服务版本，可在云中运行。 使用数据流，你可以以相同的方式获取数据和转换数据，但不能将输出发送到 Power BI 或 Excel，而是将输出存储在其他存储选项（如 Dataverse 或 Azure Data Lake Storage）中。 这样，就可以在其他产品和服务中使用数据流输出。

详情请见：什么是数据流？

在任何数据转换方案中，都有一些转换无法通过图形编辑器以最佳方式完成。 其中一些转换可能需要图形界面目前不支持的特殊配置和设置。 Power Query 引擎在所有 Power Query 转换中使用后台脚本语言：Power Query M 公式语言，也称为 M 语言。

M 语言是 Power Query 的数据转换语言。 查询中发生的任何操作最终都以 M 语言编写。如果要使用 Power Query 引擎执行高级转换，可以使用高级编辑器访问查询的脚本，并根据需要对其进行修改。 如果发现用户界面函数和转换无法准确执行所需的更改，可以使用高级编辑器和 M 语言对函数和转换进行微调。

let
    Source = Exchange.Contents("xyz@contoso.com"),
    Mail1 = Source{[Name="Mail"]}[Data],
    #"Expanded Sender" = Table.ExpandRecordColumn(Mail1, "Sender", {"Name"}, {"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Sender", each ([HasAttachments] = true)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Subject] = "sample files for email PQ test") and ([Folder Path] = "\Inbox\")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Attachments"}),
    #"Expanded Attachments" = Table.ExpandTableColumn(#"Removed Other Columns", "Attachments", {"Name", "AttachmentContent"}, {"Name", "AttachmentContent"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Expanded Attachments", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Mail", each #"Transform File from Mail"([AttachmentContent])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Mail"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Mail", Table.ColumnNames(#"Transform File from Mail"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}})
in
    #"Changed Type"

详情请见：Power Query M 公式语言

下表列出了可在使用 Power Query 的 Microsoft 产品和服务。

 

 

Power Query 用户界面

使用 Power Query，你可以连接到许多不同的数据源，并将数据转换为所需的形状。

在本文中，你将了解如何通过 Power Query 创建查询：

• Power Query 中“获取数据”的工作原理。
• 如何使用和利用 Power Query 用户界面。
• 如何执行分组和合并数据等常见转换。

如果你是 Power Query 新用户，可以在开始之前注册免费试用 Power BI。 可以使用 Power BI 数据流试用本文中介绍的 Power Query Online 体验。

还可以免费下载 Power BI Desktop。

本文中的示例连接并使用 Northwind OData 源。

https://services.odata.org/V4/Northwind/Northwind.svc/

首先，请从“获取数据”体验中找到 OData 源连接器。 可以从顶部选择“其他”类别，或在右上角的搜索栏中搜索 OData。

选择此连接器后，屏幕会显示连接设置和凭据。

• 对于 URL，请输入上一节中显示的 Northwind OData 源的 URL。
• 对于本地数据网关，请保持为无。
• 对于“身份验证类型”，请保留为“匿名”。

选择下一步按钮。

导航器现在打开，可在其中选择要从数据源连接到的表。 选择“客户”表加载数据预览，然后选择“转换数据”。

然后，对话框会将“客户”表中的数据加载到 Power Query 编辑器中。

上述连接到数据、指定身份验证方法以及选择要连接到的特定对象或表的过程称为“获取数据体验”，更多详细信息请参阅获取数据文章。

Power Query 编辑器表示 Power Query 用户界面，可在其中添加或修改查询、通过对查询步骤进行分组或添加说明来管理查询，或使用不同视图来直观显示查询及其结构。 Power Query 用户界面具有五个不同的组件。

1. 功能区：功能区导航体验，提供多个选项卡来添加转换、选择查询选项，以及访问不同的功能区按钮来完成各种任务。
2. “查询”窗格：所有可用查询的视图。
3. 当前视图：主工作视图，默认情况下显示查询数据的预览。 你还可以启用关系图视图以及数据预览视图。 还可以在架构视图和数据预览视图之间切换，同时保留关系图视图。
4. 查询设置：当前所选查询的视图，其中查询名称、查询步骤和各种指标等相关信息。
5. 状态栏：显示查询相关重要信息的栏，例如执行时间、总列数和行数以及处理状态。 此栏还包含更改当前视图的按钮。

在本部分中，你将开始使用 Power Query 转换数据。 但在开始转换数据之前，我们将讨论一些 UI 窗格，这些窗格可以根据上下文展开或折叠。 选择适当的窗格可让你专注于最重要的视图。 我们还将讨论 Power Query UI 中可用的不同视图。

可以在功能区中找到 Power Query 编辑器中的大部分转换和操作。 它有多个选项卡，其值取决于产品集成。 每个选项卡都提供特定的按钮和选项，其中一些按钮和选项可能在整个 Power Query 体验中都是多余的。 通过这些按钮和选项，可轻松访问可能需要的转换和操作。

Power Query 界面响应迅速，会尝试调整屏幕分辨率，为你提供最佳体验。 如果想使用紧凑型功能区，功能区右下角还有一个折叠按钮，可以帮助你切换到紧凑型功能区。

只需单击功能区右下角的展开图标，即可切换回标准功能区视图

你会发现在 Power Query 用户界面中，有一些图标可帮助你折叠或展开某些视图或部分。 例如，“查询”窗格右上角有一个图标，选中后会折叠查询窗格，再次选中后会展开窗格。

除了可以折叠 Power Query 用户界面中的某些窗格和部分外，还可以切换显示的视图。 若要切换视图，请转到功能区中的“视图”选项卡，然后找到“预览”和“布局”组，这两个组可以控制 Power Query 用户界面的外观。

我们鼓励你尝试所有这些选项，找到最适合你的视图和布局。 例如，从功能区中选择“架构”视图。

状态栏右侧还包含关系图、数据和架构视图的图标。 可以使用这些图标在视图之间切换。 还可以使用这些图标启用或禁用所选视图。

架构视图提供了一种快速直接的方式，仅与表的架构组件（如列名称和数据类型）进行交互。 如果要执行与架构相关的操作，例如删除列、重命名列、更改列数据类型、重新排序列或复制列，建议使用架构视图。

例如，在架构视图中，选择“订单”和“客户人口统计”列旁边的复选标记，然后从功能区中选择“删除列”操作。 此选择应用转换，从数据中删除这些列。

现在可以切换回数据预览视图并启用关系图视图，以更直观的视角查看数据和查询。

关系图视图可帮助你直观地了解查询的结构，以及它与项目中其他查询交互的方式。 查询中的每个步骤都有一个独特的图标，可帮助你识别使用的转换。 此外，还有连接步骤来说明依赖关系。 由于启用了数据预览视图和关系图视图，关系图视图会显示在数据预览顶部。

启用关系图视图后，选择加号。 可以搜索新的转换，将其添加到查询中。 搜索“分组依据”并选择转换。

然后会出现“分组依据”对话框。 可以将“分组依据”操作设置为按国家/地区分组，并按国家/地区统计客户行数。

1. 保持选中“基本”单选按钮。
2. 选择要按国家/地区分组。
3. 分别选择“客户”和“将行计数”作为列名称和操作。

选择“确定”执行操作。 数据预览会刷新，显示按国家/地区分组的客户总数。

启动“分组依据”对话框的另一种方法是使用功能区中的“按分组”按钮或右键单击“国家/地区”列。

为方便起见，Power Query 中的转换通常可从多个位置访问，以便用户可以选择使用自己喜欢的体验。

现在，你已经有提供每个国家/地区客户数的查询，可以通过查找每个地区的供应商总数来为此数据添加上下文。

首先，需要添加供应商数据。 从下拉菜单中选择“获取数据”，然后选择“OData”。

OData 连接体验再次出现。 输入“连接到 OData 源”中所述的连接设置，以连接到 Northwind OData 源。 在导航器体验中，搜索并选择“供应商”表。

选择“创建”，将新查询添加到 Power Query 编辑器中。 现在，“查询”窗格应同时显示“客户”和“供应商”查询。

再次打开“分组依据”对话框，这次要选择功能区中“转换”选项卡下“分组依据”按钮。

在“分组依据”对话框中，将“分组依据”操作设置为按国家/地区分组，并计算每个国家/地区的供应商行数。

1. 保持选中“基本”单选按钮。
2. 选择要按国家/地区分组。
3. 分别选择“供应商”和“将行计数”作为列名称和操作。

现在，有了客户查询和供应商查询后，下一个目标是将这些查询合并成一个查询。 实现这一目标的方法有很多，包括使用“客户”表中的“合并”选项、复制查询或引用查询。 在本例中，你将右键单击“客户”表并选择“引用”，从而创建一个引用“客户”查询的新查询。

创建新查询后，将查询的名称更改为“国家/地区分析”，并取消“供应商”查询中的“启用加载”选项，从而禁用“客户”表的加载。

合并查询操作根据一列或多列的匹配值将两个现有表联接在一起。 在本例中，目标是将“客户”和“供应商”表合并为一个表，只针对同时具有“客户”和“供应商”表的国家/地区。

在“国家/地区分析”查询中，从功能区中的“主页”选项卡中选择“合并查询”选项。

此时会显示新的合并操作对话框。 然后，可以选择要与当前查询合并的查询。 选择“供应商”查询，并从两个查询中选择“国家/地区”字段。 最后，选择“内部”联接类型，因为你只需要在客户和供应商的国家/地区进行分析。

选择“确定”按钮后，“国家/地区分析”查询中将新增一列，其中包含来自供应商查询的数据。 选择“供应商”字段旁边的图标，会出现一个菜单，可在其中选择要展开的字段。 仅选择“供应商”字段，然后选择“确定”按钮。

此展开操作的结果是得到一个只有 12 行的表。 双击 Suppliers .Suppliers 字段名称并输入新名称，将其重命名为“供应商”。

应用于查询的每个转换都会作为一个步骤保存在查询设置窗格的“已应用步骤”部分。 如果需要检查查询如何从步骤转换到步骤，可以选择一个步骤并预览查询在该特定点的解析情况。

还可以右键单击查询并选择“属性”选项来更改查询名称或添加查询说明。 例如，右键单击“国家/地区分析”查询中的“合并查询”步骤，将查询名称更改为“与供应商合并”，以及将说明更改为“从供应商查询中获取按国家/地区分组的供应商数据”。

此更改会在步骤旁边添加一个新图标，将鼠标悬停在该图标上即可阅读其描述。

在进入下一部分之前，请禁用关系图视图，以便只使用数据预览。

将客户和供应商的数据放在一个表中，现在就可以计算每个国家/地区的客户与供应商的比例。 选择“国家/地区分析”查询的最后一步，然后选择“客户”和“供应商”列。 在功能区中的“添加列”选项卡和“从数字”组中，选择“标准”，然后从下拉列表中选择“除号（整数）”。

此更改将创建一个名为“整除”的新列，可以重命名为“比率”。 此更改是查询的最后一步，并为具有客户和供应商数据的国家/地区提供客户与供应商的比率。

数据分析是 Power Query 的另一项功能，可以帮助你更好地理解数据。 启用数据剖析功能后，你将获得有关查询字段内数据的反馈信息，如值分布、列质量等。

建议在整个查询开发过程中使用此功能，但也可以随时启用和禁用该功能。 下图显示了为国家/地区分析查询启用的所有数据分析工具。

如果要观察该 Power Query 编辑器在每个步骤中创建的代码，或者要创建自己的改编代码，可以使用高级编辑器。 若要打开高级编辑器，请选择功能区中选择“视图”选项卡，然后选择“高级编辑器”。 此时，将会显示包含现有查询代码的窗口。

可以直接编辑高级编辑器 窗口中的代码。 编辑器会显示代码是否没有语法错误。 若要关闭该窗口，请选择完成或取消按钮。

在使用 Power Query 的 Microsoft 应用中可以访问各种级别的帮助。 本部分将介绍这些帮助级别。

可通过几种方法访问专门适用于 Excel 中 Power Query 的帮助信息。 访问在线 Excel Power Query 文档的一种方法是在 Power Query 编辑器中选择“文件>帮助>帮助”。

若要访问 Excel 中的内联 Power Query 帮助信息，请选择 Excel 功能区上的“帮助”选项卡，然后在搜索文本框中输入 Power Query。

Power BI Desktop 上运行的 Power Query 编辑器中有一个“帮助”选项卡，但目前所有选项都转到提供 Power BI 信息的位置。 还可以通过选择“文件>帮助”，然后选择其中一个帮助选项，在 Power BI Desktop 的 Power Query 编辑器中访问相同的 Power BI 帮助信息。

Power BI 服务、Power Apps、Dynamics 365 Customer Insights 和 Azure 数据工厂中的 Power Query 帮助选项卡包含指向 Power Query 重要信息源的链接。

帮助选项卡中的选项包括以下联接：

• Power Query 文档：指向 Power Query 文档的链接
• 键盘快捷方式：Power Query 文档中键盘快捷方式文章的链接。
• 支持：指向 Power Query 当前正在运行的 Microsoft 产品支持网站的链接。
• Power Query Websight：指向 Power Query 网站的链接。
• Power Query 博客：指向 Power Query 博客的链接
• Power Query 社区：指向 Power Query 社区的链接。
• 提交想法：指向 Power Query 当前正在运行的 Microsoft 产品创意网站的链接。
• 发送反馈：在 Power Query 中打开一个窗口，要求你对 Power Query 的体验进行评分，并提供你希望提供的任何其他信息。

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需99元，即可下载本站文章涉及的文件和软件。