组合多个数据源中的数据 (Power Query)

重要:  本文是由机器翻译的,请参阅免责声明。请在 此处 中查找本文的英文版本以便参考。

注意: Power Query 在 Excel 2016 中称为“获取和转换”。在此处提供的信息适用于两者。 若要了解详细信息,请参阅在 Excel 2016 中获取和转换

在本教程中,您将使用 Power Query 查询编辑器以从本地 Excel 文件包含产品信息,并从 OData 源的包含产品订单信息导入数据。您执行转换和聚合的步骤,并进行组合以生成每种产品和年份的总销售额报表这两个来源的数据。

为执行此教程,你需要“产品和订单”工作簿。 在“另存为”对话框中,将文件命名为“产品和订 单.xlsx”。

在本教程中

任务 1:将产品导入到 Excel 工作簿

步骤 1:连接到 Excel 工作簿

步骤 2:将第一行升级为表列标题

步骤 3:删除其他列,只显示感兴趣的列

创建 Power Query 步骤

步骤 4:导入一个产品查询

任务 2:从 OData 源导入订单数据

步骤 1:连接到 OData 源

步骤 2:展开订单详情表

展开“订单详情”表链接

步骤 3:删除其他列,只显示感兴趣的列

删除所选列

步骤 4:计算每个“订单详情”行的行合计

计算每个“订单详情”行的行合计

步骤 5:转换“订单日期”年份列

步骤 6:按“产品 ID”和“年份”对行进行分组

步骤 7:重命名查询

最终的查询结果

创建 Power Query 步骤

步骤 8:禁用将查询下载到 Excel 工作簿

禁用查询下载

任务 3:合并“产品”和“总销售额”查询

步骤 1:将“产品 ID”合并到“总销售额”查询

步骤 2:展开合并列

展开“新列”表链接

创建 Power Query 步骤

步骤 3:将每种产品总销售额查询加载到 Excel 数据模型

将“每种产品总销售额”查询加载到 Excel 数据模型

“每种产品总销售额”最终查询

任务 1:将产品导入到 Excel 工作簿

在本任务中,你将产品从“产品和订单.xlsx”文件导入到 Excel 工作簿。

步骤 1:连接到 Excel 工作簿

  1. 创建 Excel 工作簿。

  2. 在“POWER QUERY”功能区选项卡上,单击“从文件”>“从 Excel”。

  3. 在“Excel”浏览对话框中,浏览找到或键入要导入或链接到文件的产品和订单 .xlsx 路径。

  4. 在“导航器”窗格中,双击“产品”工作表或单击“产品”,然后单击“编辑查询”。 当你编辑查询或连接到新的数据源时,将显示“查询编辑器”窗口。

    注意: 有关如何显示查询编辑器的快速入门视频,请查看本文末尾。

步骤 2:将第一行升级为表列标题

在“查询预览”网格中,表的第一行不包含表列名称。 如要第一行升级为表列标题:

  1. 单击数据预览左上角的表图标 ( 表图标 )。

  2. 单击“将第一行用作标题”。

将第一行升级为表列标题:

步骤 3:删除其他列,只显示感兴趣的列

在此步骤中,删除除“产品 ID”、“产品名称”、“类别 ID”和“单位数量”以外的所有列。

  1. 在“查询预览”网格中,选择“产品 ID”、“产品名称”、“类别 ID”和“单位数量”列(使用 Ctrl+Click 或 Shift+Click)。

  2. 在“查询编辑器”功能区中,单击“删除列”>“删除其他列”或右键单击一个列标题,然后单击“删除其他列”。

    隐藏其他列

创建 Power Query 步骤

在“Power Query”中执行查询活动时,在“应用的步骤”列表的“查询设置”窗格中创建并列出查询步骤。 每个查询步骤有相应的 Power Query 公式,也称为“M”语言。 有关 Power Query 公式语言的详细信息,请参阅了解 Power Query 公式

任务

查询步骤

公式

连接到 Excel 工作簿

Source{[Name="产品"]}[Data]

将第一行升级为表列标题:

FirstRowAsHeader

Table.PromoteHeaders

(产品)

删除其他列,只显示感兴趣的列

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"产品 ID", "产品名称", "类别 ID", "单位数量"})

步骤 4:导入产品查询

在此步骤中,将“产品”查询导入到您的 Excel 工作簿中。

  1. 在“查询编辑器”功能区中,单击“应用和关闭”。 结果将显示在新的 Excel 工作表中。

返回页首

任务 2:从 OData 源导入订单数据

本任务中,你将数据从位于 http://services.odata.org/Northwind/Northwind.svc 的示例 Northwind OData 源导入到你的 Excel 工作簿。

步骤 1:连接到 OData 源

  1. 在“POWER QUERY”功能区选项卡上,单击“从其他文件”>“从 OData 源”。

  2. 在“OData 源”对话框中,输入 Northwind OData 源的 URL

  3. 单击“确定”。

  4. 在“导航器”窗格中,双击“订单”表或单击“订单”,然后单击“编辑”。

注意: 将鼠标悬停在某个表上时,您将看到飞出的表预览。

将鼠标悬停在数据源上

步骤 2:展开订单详情表

在此步骤中,展开与“订单”表相关的“订单详情”表,将“订单详情”中的“产品 ID”、“单击”和“数量”合并到“订单”表。 “展开”操作将相关表中的列合并到一个主题表。 在运行查询时,将相关表(“订单详情”)中的行合并到主题表(“订单”)的行。

在 Power Query 中,包含指向相关表的链接的列拥有“条目”链接或“”链接。 “条目”链接导航到单个相关记录,表示与主题表的一对一关系”链接导航到相关的表,表示与主题表的一对多关系。 链接表示关系模型中的数据源的导航属性。 对于 OData 源,导航属性表示使用外键关联的实体。 在数据库中,如 SQL Server 中,导航属性表示数据库中的外键关系。

展开“订单详情”表链接

展开“订单详情”表后,将三个新列和其他行添加到“订单”表中,每项对应嵌套表或相关表中的每行。

  1. 在“查询预览”窗格中,滚动到“订单详情”列。

  2. 在“订单详情”列,单击展开图标 ( 展开 )。

  3. 在“展开”下拉菜单中:

    1. 单击“(选择所有列)”清除所有列。

    2. 单击“产品 ID”、“单价”和“数量”。

    3. 单击“确定”

      展开“订单详情”表链接

      注意: 在 Power Query 中,您可以展开与某个列链接的表,还能够在主题表中展开数据前,在链接表的列中执行聚合操作。 有关如何执行聚合操作的详细信息,请参阅聚合列数据

步骤 3:删除其他列,只显示感兴趣的列

在此步骤中,删除除“订单日期”、“产品 ID”、“单价”和“数量”列以外的所有列。 在前一个任务中,您使用了“删除其他列”。 此任务中,您删除所选的列。

删除所选列

  1. 在“查询预览”窗格中,选择所有列:

    1. 单击第一列(“订单 ID”)。

    2. Shift+单击最后一列(运货商)。

    3. Ctrl+单击“订单日期”、“订单详情.产品 ID”、“订单详情.单价”和“订单详情.数量”列。

  2. 右键单击所选列标题,然后单击“删除其他列”。

步骤 4:计算每个“订单详情”行的行合计

在此步骤中,创建“自定义列”,计算每个“订单详情”行的行合计。

计算每个“订单详情”行的行合计

  1. 在“查询预览”窗格中,单击预览左上角的表图标 ( 表图标 )。

  2. 单击“插入列”>“自定义”。

  3. 在“插入自定义列”对话框的“自定义列公式”文本框内,输入[订单详情.单价] * [订单详情.数量]

  4. 在“新建列名称”文本框内,输入“行合计”。

  5. 单击“确定”。

计算每个“订单详情”行的行合计

步骤 5:转换“订单日期”年份列

在此步骤中,转换“订单日期”列,以列呈现订单日期年份。

  1. 在“预览”网格中,右键单击“订单日期”列,然后单击“转换”>“年份”。

  2. 将“订单日期”列重命名为“年份”:

    1. 双击“订单日期”列,输入“年份”或

    2. 右键单击“订单日期”列、然后单击“重命名”,输入“年份”。

步骤 6:按“产品 ID”和“年份”对行进行分组

  1. 在“查询预览”网格中,选择“年份”和“订单详情.产品 ID”。

  2. 右键单击其中一个标题,然后单击“分组依据”。

  3. 在“分组依据”对话框中:

    1. 在“新建列名称”文本框内,输入“总销售额”。

    2. 在“操作”下拉菜单中,选择“求和”。

    3. 在“”下拉菜单中,选择“行合计”。

  4. 单击“确定”。

    聚合操作的“分组依据”对话框

步骤 7:重命名查询

在将销售数据导入 Excel 之前,将查询命名为“总销售额”:

  1. 在“查询设置”窗格的“名称”文本框中,输入“总销售额”。

最终的查询结果

执行每个步骤后,您将拥有通过 Northwind OData 源进行的“总销售额”查询。

总销售额

创建 Power Query 步骤

在“Power Query”中执行查询活动时,在“应用的步骤”列表的“查询设置”窗格中创建并列出查询步骤。 每个查询步骤有相应的 Power Query 公式,也称为“M”语言。 有关 Power Query 公式语言的详细信息,请参阅了解 Power Query 公式

任务

查询步骤

公式

连接到 OData 源

Source{[Name="订单"]}[Data]

展开“订单详情”表

展开“订单详情”

Table.ExpandTableColumn

(订单, "订单详情", {"产品 ID", "单价", "数量"}, {"订单详情.产品 ID", "订单详情.单价", "订单详情.数量"})

删除其他列,只显示感兴趣的列

RemovedColumns

Table.RemoveColumns

(#"展开订单详情",{"订单 ID", "客户 ID", "员工 ID", "要求日期", "发货日期", "运货商", "运费", "船名", "发货地址", "发货城市", "发货地区", "发货方邮政编码", "发货国家/地区", "客户", "员工", "运货商"})

计算每个“订单详情”行的行合计

InsertedColumns

Table.AddColumn

(已删除列, "客户", 每个 [订单详情.单价] * [订单详情.数量])

转换“订单日期”列,呈现年份

RenamedColumns

Table.RenameColumns

(已插入列,{{"自定义", "行合计"}})

TransformedColumn

Table.TransformColumns

(重命名的列,{{"订单日期", 日期.年份}})

RenamedColumns1

Table.RenameColumns

(已转换的列,{{"订单日期", "年份"}})

按“产品 ID”和“年份”对行进行分组

GroupedRows

Table.Group
(已重命名的列1, {"年份", "订单详情.产品ID"}, {{"总销售额", 每个列表.求和(行合计]), 键入数字}})

步骤 8:禁用将查询下载到 Excel 工作簿

由于“总销售额”查询不代表最终“每年每种产品总销售额”报表,您禁用将查询下载到 Excel 工作簿。 当“查询设置”窗格中的“加载到工作表”选项为“关闭”时,则没有下载此查询的数据结果,但查询仍可以与其他查询结合使用以构建所需的结果。 您了解如何将此查询与下一个任务中的“产品”查询合并。

禁用查询下载

  1. 在“查询设置”窗格中,取消选中“加载到工作表”。

  2. 在“查询编辑器”功能区中,单击“应用和关闭”。 在“工作簿查询”窗格中,“总销售额”查询显示“禁用加载”。

    禁用查询下载

返回页首

任务 3:合并“产品”和“总销售额”查询

你可以通过合并或追加查询,使用 Power Query 合并多个查询。可以在任何表格形状的 Power Query 查询中执行“合并”操作,独立于数据来源的数据源。有关合并数据源的详细信息,请参阅合并多个查询

本任务中,使用“合并”和“展开”查询步骤,合并“产品总销售额”查询。

步骤 1:将“产品 ID”合并到“总销售额”查询

  1. 在 Excel 工作簿中,导航到“Sheet2”中的“产品”查询。

  2. 在“查询”功能区选项卡中,单击“合并”。

  3. 在“合并”对话框中,选择“产品”作为主表,选择“总销售额”作为要合并的第二查询或相关查询。 “总销售额”将成为新的可展开列。

  4. 如要按“产品 ID”匹配“产品销售总额”和“产品”,从“产品”表选择“产品 ID”列,从“总销售额”表选择“订单详情.产品 ID”列。

  5. 在“隐私级别”对话框中:

    1. 选择用于两个数据源的隐私隔离级别的“组织”。

    2. 单击“保存”

  6. 单击“确定”。

    安全注释 : “隐私级别”防止用户意外合并多个数据源中的数据,可能是专用或组织数据源。 根据查询,用户可能意外将专用数据源中的数据发送到另一个可能恶意的数据源。 Power Query 分析每个数据源,并将其归类到已定义的隐私级别:公共、组织和私有。 有关“隐私级别”的详细信息,请参阅隐私级别

    “合并”对话框

单击“确定”后,“合并”操作将创建一个查询。 查询结果包含主表(“产品”)的所有列,以及包含指向相关表(总销售额)的导航链接的单个列。 “展开”操作将新列从相关表添加到主要或主题表。

合并最终结果

步骤 2:展开合并列

在此步骤中,您将展开名为“新列”的合并列,以便在“产品”查询中创建两个新列:“年份”和“总销售额”。

展开“新列”表链接

  1. 在“查询预览”网格中,单击“新列”展开图标 ( 展开 )。

  2. 在“展开”下拉菜单中:

    1. 单击“(选择所有列)”清除所有列。

    2. 单击“年份”和“总销售额”。

    3. 单击“确定”。

  3. 将这两列重命名为“年份”和“总销售额”。

  4. 按“总销售额降序排序,以了解哪些产品以及在哪些年产品获得最高销售额。

  5. 将查询“重命名”为“每种产品销售总额”。

展开表链接

创建 Power Query 步骤

在 Power Query 中执行“合并”查询活动时,在“查询设置”窗格的“应用的步骤”列表创建并列出查询步骤。 每个查询步骤有相应的 Power Query 公式,也称为“M”语言。 有关 Power Query 公式语言的详细信息,请参阅了解 Power Query 公式

任务

查询步骤

公式

将“产品 ID”合并到“总销售额”查询

源(用于“合并”操作的数据源)

Table.NestedJoin

(产品,{"产品 ID"},#"总销售额",{"产品详细信息.产品 ID"},"新列")

展开合并列

ExpandNewColumn

Table.ExpandTableColumn

(源, "新列", {"年份", "总销售额"}, {"新列.年份", "新列.总销售额"})

RenamedColumns

Table.RenameColumns

(#"展开新列",{{"新列.年份", "年份"}, {"新列.总销售额", "总销售额"}})

SortedRows

Table.Sort

(重命名的列,{{"总销售额", 订单.降序}})

步骤 3:将每种产品总销售额查询加载到 Excel 数据模型

在此步骤中,禁用“加载到工作表”选项,将查询加载到 Excel 数据模型,以构建连接到查询结果的报表。 除了将查询结果加载到 Excel 工作表以外,还可以通过 Power Query 将查询结果加载到 Excel 数据模型。 将数据加载到 Excel 数据模型后,您可以使用 Power Pivot 和 Power View 进一步分析数据。

将“每种产品总销售额”查询加载到 Excel 数据模型

  1. 在“查询设置”窗格中,取消选中“加载到工作表”,选中“加载到数据模型”。

  2. 如要将查询加载到 Excel 数据模型,单击“应用和关闭”。

加载 Excel 数据模型

“每种产品总销售额”最终查询

执行每个步骤之后,您将获得将产品和订单.xlsx 文件与 Northwind OData 源的数据结合起来的“每种产品总销售额”查询。 可以将该查询应用于 Power Pivot 模型。 此外,对 Power Query 中的查询进行更改将修改并刷新 Power Pivot 模型中的结果表。

返回页首

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时,“查询编辑器”才会显示。 以下视频显示在 Excel 工作簿中编辑查询后显示的“查询编辑器”窗口。 若要在不加载或编辑现有工作簿的情况下查看“查询编辑器”,请在 Power Query 功能区选项卡的获取外部数据部分中,选择自其他来源 > 空白查询。 以下视频介绍一种显示查询编辑器的方法。

如何在 Excel 中查看查询编辑器

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×