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

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

在本教程中，可以使用 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：删除其他列以仅显示感兴趣的列

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

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

步骤 4：加载产品查询

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

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

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

在 Power Query 中执行查询活动时，将创建查询步骤并将其列在“查询设置”窗格的“已应用步骤”列表中。 每个查询步骤有相应的 Power Query 公式，也称为“M”语言。 有关 Power Query 公式的详细信息，请参阅在 Excel 中创建 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. 选择“确定”。
      展开“订单详情”表链接

      注意

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

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

在此步骤中，删除除“订单日期”、“产品 ID”、“单价”和“数量”列以外的所有列。 

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

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

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

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

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

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

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

步骤 5：转换 OrderDate year 列

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

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

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

    1. 双击“订单日期”列，输入“年份”或
    2. Right-Click“ 订单日期 ”列，选择“ 重命名”，然后输入 “年份”。

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

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

  2. Right-Click 其中一个标头，然后选择“ 分组依据”。

  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 查询中执行“合并”操作，独立于数据来源的数据源。 有关合并数据源的详细信息，请参阅合并多个查询。

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

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

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

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

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

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

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

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

  6. 选择“确定”。

    注意

    “隐私级别”防止用户意外合并多个数据源中的数据，可能是专用或组织数据源。 根据查询，用户可能意外将专用数据源中的数据发送到另一个可能恶意的数据源。 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 帮助