了解如何组合多个数据源 (Power Query)

应用对象
Microsoft 365 专属 Excel Excel 2024 Excel 2021

在本教程中，使用 Power Query 的查询编辑器从包含产品信息的本地 Excel 文件和包含产品订单信息的 OData 源导入数据。 执行转换和聚合步骤，并合并来自两个来源的数据，以创建 “每个产品和年份的总销售额 ”报表。   

要完成本教程，你需要“ 产品” 工作簿。 在“另存为”对话框中，将文件命名为“产品和订 单.xlsx”。

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

在此任务中，你将产品从在上一节中) 下载并重命名 (“产品和 Orders.xlsx ”文件导入到 Excel 工作簿中。 然后，将行提升为列标题，删除一些列，然后将查询加载到工作表。

步骤 1：连接到 Excel 工作簿

  1. 创建 Excel 工作簿。
  2. 选择“数据>”，从工作簿中的文件>获取数据>。
  3. 在“ 导入数据 ”对话框中，浏览并找到下载的 Products.xlsx 文件，然后选择“ 打开”。
  4. 在 导航器 窗格中，双击 产品 表。 将显示 Power Query 编辑器。

步骤 2：检查查询步骤

默认情况下，为方便起见，Power Query 会自动添加几个步骤。 检查“查询设置”窗格中“已应用步骤”下的每个步骤以了解详细信息。

  1. 右键单击 源 步骤，然后选择编辑 设置。 此步骤是在导入工作簿时创建的。
  2. 右键单击 导航 步骤，然后选择编辑 设置。 此步骤是在从 “导航 ”对话框中选择表时创建的。
  3. 右键单击 “更改的类型 ”步骤，然后选择“ 编辑设置”。 此步骤由 Power Query 创建，它推断出每个列的数据类型。 选择编辑栏右侧的向下箭头以查看完整公式。

步骤 3：删除其他列以仅显示感兴趣的列

在此步骤中，将删除除 ProductID、 ProductName、 CategoryID 和 QuantityPerUnit 之外的所有列。

  1. 在 数据预览中，选择 ProductID、 ProductName、 CategoryID 和 QuantityPerUnit 列， (使用 Ctrl+单击或 Shift+单击) 。
  2. 选择“删除列>” “删除其他列”。
    显示“隐藏其他列”的屏幕截图。

步骤 4：加载产品查询

在此步骤中，将 “产品” 查询加载到 Excel 工作表中。

  • 选择“开始>”，关闭“& 加载”。 查询将显示在新的 Excel 工作表中。

摘要：在任务 1 中创建的 Power Query 步骤

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

任务 查询步骤 公式
导入 Excel 工作簿 源 = Excel.Workbook (File.Contents (“C：\Products and Orders.xlsx”) ， null， true)
选择“产品”表 导航 = Source{[Item=“Products”，Kind=“Table”]}[Data]
Power Query 自动检测列数据类型 已更改的类型 = Table.TransformColumnTypes ( Products_Table，{{“ProductID”， Int64.Type}， {“ProductName”， type text}， {“SupplierID”， Int64.Type}， {“CategoryID”， Int64.Type}， {“QuantityPerUnit”， type text}， {“UnitPrice”， type number}， {“UnitsInStock”， Int64.Type}， {“UnitsOnOrder”， Int64.Type}， {“ReorderLevel”， Int64.Type}， {“Discontinued”， type logical}})
删除其他列，只显示感兴趣的列 删除的其他列 = Table.SelectColumns (FirstRowAsHeader，{“ProductID”， “ProductName”， “CategoryID”， “QuantityPerUnit”})

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

在此任务中，你将数据从位于 的示例 Northwind OData 源 http://services.odata.org/Northwind/Northwind.svc导入 Excel 工作簿，展开Order_Details表、删除列、计算行总计、转换 OrderDate、按 ProductID 和 Year 对行进行分组、重命名查询，以及禁止查询下载到 Excel 工作簿。

步骤 1：连接到 OData 源

  1. 从OData 源选择“数据>”从其他源>获取数据>。
  2. 在“OData 源”对话框中，输入 Northwind OData 源的 URL。
  3. 选择“确定”。
  4. 在 导航器 窗格中，双击 “订单” 表。

步骤 2：展开Order_Details表

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

在 Power Query 中，包含相关表的列的单元格中的值为“记录”或“表”。 这些称为结构化列。 记录 表示单个相关记录，表示与当前数据或主表的一对一关系。 表 指示相关表，表示与当前表或主表的一对多关系。 结构化列表示具有关系模型的数据源中的关系。 例如，结构化列指示在 SQL Server 数据库中的 OData 源或外键关系中具有外键关联的实体。

展开 Order_Details 表之后，三列和其他行将添加到 “订单 ”表中，嵌套表或相关表中的每一行对应一行。

  1. 在 数据预览中，水平滚动到“ Order_Details ”列。

  2. 在“ Order_Details ”列中，选择展开图标 ( ) 。

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

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

    2. 选择 ProductID、 UnitPrice 和 Quantity。

    3. 选择“确定”。
      显示展开“Order_Details表”链接的屏幕截图。

      注意

      在 Power Query 中，可以展开从列链接的表，并在展开主题表中的数据之前聚合链接表的列。 有关如何执行聚合操作的更多信息，请参阅从列 (Power Query) 聚合数据。

步骤 3：删除其他列以仅显示感兴趣的列

在此步骤中，将删除除 OrderDate、 ProductID、 UnitPrice 和 Quantity 列之外的所有列。 

  1. 在 “数据预览”中，选择以下列：

    1. 选择第一列 OrderID。
    2. Shift+单击最后一列， 发货人。
    3. Ctrl+单击“订单日期”、“订单详情.产品 ID”、“订单详情.单价”和“订单详情.数量”列。

  2. 右键单击选定的列标题，然后选择删除 其他列。

第 4 步：计算每Order_Details行的行汇总

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

  1. 在 数据预览中，选择预览左上角 ( 表图标 ) 。
  2. 选择“ 添加自定义列”。
  3. 在“自定义列”对话框的“自定义列公式”框中，输入 [Order_Details.UnitPrice] * [Order_Details.Quantity]。
  4. 在 “新列名称 ”框中，输入 “行总计”。
  5. 选择“确定”。

显示计算每行Order_Details总计的屏幕截图。

步骤 5：转换 OrderDate year 列

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

  1. 在 数据预览中，右键单击 OrderDate 列 ，然后选择转换>年份。

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

    1. 双击 OrderDate 列，然后输入 “年份” 或
    2. 右键单击“ 订购日期 ”列，选择“ 重命名”，然后输入 “年份”。

步骤 6：按 ProductID 和年份对行进行分组

  1. 在 “数据预览”中，选择 “年份” 和 “Order_Details.ProductID”。

  2. 右键单击其中一个标头，然后选择“ 分组依据”。

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

    1. 在“新建列名称”文本框内，输入“总销售额”。
    2. 在“操作”下拉菜单中，选择“求和”。
    3. 在“列”下拉菜单中，选择“行合计”。

  4. 选择“确定”。
    显示聚合操作的“分组依据”对话框的屏幕截图。

步骤 7：重命名查询

将销售数据导入 Excel 之前，请重命名查询：

  • 在 “查询设置” 窗格的“ 名称 ”框中，输入 “Total Sales”。

结果：任务 2 的最终查询

执行每个步骤后，将对 Northwind OData 源进行“总销售额”查询。

显示总销售额的屏幕截图。

摘要：在任务 2 中创建的 Power Query 步骤

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

任务 查询步骤 公式
连接到 OData 源 源 = OData.Feed (“http://services.odata.org/Northwind/Northwind.svc”， null， [Implementation=“2.0”])
选择一个表 导航 = Source{[Name=“Orders”]}[Data]
展开“订单详情”表 展开“订单详情” = Table.ExpandTableColumn (Orders， “Order_Details”， {“ProductID”， “UnitPrice”， “Quantity”}， {“Order_Details.ProductID”， “Order_Details.UnitPrice”， “Order_Details.Quantity”})
删除其他列，只显示感兴趣的列 RemovedColumns = Table.RemoveColumns (#“Expand Order_Details”，{“OrderID”， “CustomerID”， “EmployeeID”， “RequiredDate”， “ShippedDate”， “ShipVia”， “Freight”， “ShipName”， “ShipAddress”， “ShipCity”， “ShipRegion”， “ShipPostalCode”， “ShipCountry”， “Customer”， “Employee”， “Shipper”})
计算每个“订单详情”行的行合计 已添加的自定义 = Table.AddColumn (RemovedColumns， “Custom”， each [Order_Details.UnitPrice] * [Order_Details.Quantity])
= Table.AddColumn (#“Expanded Order_Details”， “Line Total”， each [Order_Details.UnitPrice] * [Order_Details.Quantity])
更改为更有意义的名称 Lne Total 重命名的列 = Table.RenameColumns (InsertedCustom，{{“Custom”， “Line Total”}})
转换“订单日期”列，呈现年份 提取年份 = Table.TransformColumns (#“Grouped Rows”，{{“Year”， Date.Year， Int64.Type}})
更改为
更有意义的名称、OrderDate 和年份		 重命名列 1 Table.RenameColumns
(已转换的列,{{"订单日期", "年份"}})
按“产品 ID”和“年份”对行进行分组 GroupedRows = Table.Group (RenamedColumns1， {“Year”， “Order_Details.ProductID”}， {{“Total Sales”， each List.Sum ([Line Total]) ， type number}})

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

Power Query 使你能够通过合并或追加多个查询来组合它们。 无论数据源如何，都可以对任何具有表格形状的 Power Query 查询执行合并操作。 有关合并数据源的详细信息，请参阅合并多个查询 (Power Query) 。

在此任务中，使用合并查询和展开操作合并“产品”和“总销售额”查询，然后将“每个产品的总销售额”查询加载到 Excel 数据模型中。

步骤 1：将 ProductID 合并到总销售额查询中

  1. 在 Excel 工作簿中，转到“产品”工作表选项卡上的“产品”查询。

  2. 在查询中选择一个单元格，然后选择“ 查询>合并”。

  3. 在“ 合并” 对话框中，选择“ 产品 ”作为主表，然后选择“ 总销售额” 作为要合并的辅助或相关查询。 “总销售额”将成为带有展开图标的新结构化列。

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

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

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

  6. 选择“确定”。

    注意

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

    显示“合并”对话框的屏幕截图。

结果

合并操作将创建一个查询。 查询结果包含主表 (“Products) ”中的所有列，以及指向“Total Sales) ” (相关表的单个表结构化列。 选择“ 展开” 图标，将新列从辅助表或相关表添加到主表。

显示合并最终版的屏幕截图。

步骤 2：展开合并的列

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

  1. 在数据预览中，选择NewColumn 旁边 ( ) “展开”图标。

  2. 在 “展开 ”下拉列表中：

    1. 选择 “ (选择所有列) ”以清除所有列。
    2. 选择 年份 和 总销售额。
    3. 选择“确定”。

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

  4. 若要了解哪些产品在哪些年份获得了最高的销量，请选择按总销售额降序排序。

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

结果

显示“展开表格链接”的屏幕截图。

步骤 3：将“每个产品的总销售额”查询加载到 Excel 数据模型中

在此步骤中，将查询加载到 Excel 数据模型中，以便生成连接到查询结果的报表。 将数据加载到 Excel 数据模型后，可以使用 Power Pivot 进一步进行数据分析。

  1. 选择“开始>”，关闭“& 加载”。
  2. 在“ 导入数据 ”对话框中，确保选择“ 将此数据添加到数据模型”。 有关使用此对话框的更多信息，请选择问号 (?)。

结果

你有一个 “每个产品的总销售额” 查询，该查询结合了 Products.xlsx 文件和 Northwind OData 源中的数据。 此查询应用于 Power Pivot 模型。 此外，对查询的更改会修改和刷新数据模型中生成的表。

摘要：在任务 3 中创建的 Power Query 步骤

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

任务 查询步骤 公式
将“产品 ID”合并到“总销售额”查询 源（用于“合并”操作的数据源） = Table.NestedJoin (Products， {“ProductID”}， #“Total Sales”， {“Order_Details.ProductID”}， “Total Sales”， JoinKind.LeftOuter)
展开合并列 展开总销售额 = Table.ExpandTableColumn (Source， “Total Sales”， {“Year”， “Total Sales”}， {“Total Sales.Year”， “Total Sales.Total Sales”})
重命名两列 重命名的列 = Table.RenameColumns (#“Expanded Total Sales”，{{“Total Sales.Year”， “Year”}， {“Total Sales.Total Sales”， “Total Sales”}})
按升序对总销售额排序 排序的行 = Table.Sort (#“Renamed Columns”，{{“Total Sales”， Order.Ascending}})

另请参阅

Microsoft Power Query for Excel 帮助