了解如何将多个数据源组合 (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 创建，该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 源导入订单数据

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

步骤 1：连接到 OData 源

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

步骤 2：展开Order_Details表

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

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

展开 Order_Details 表后，三个新列和附加行将添加到 Orders 表中，嵌套表或相关表中的每一行各有一行。

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

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

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

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

    2. 选择 “ProductID”、“ UnitPrice”和 “数量”。

    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 年份列

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

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

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

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

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

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

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

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

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

  4. 选择“确定”。
    聚合操作的“分组依据”对话框

步骤 7：重命名查询

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

  • 在 “查询设置” 窗格中的“ 名称 ”框中，输入 “总销售额”。

结果：任务 2 的最终查询

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

总销售额

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

在 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”、“ShipDate”、“ShipVia”、“Freight”、“ShipName”、“ShipAddress”、“ShipCity”、“ShipRegion”、“ShipPostalCode”、“ShipCountry”、“Customer”、“Employee”、“Shipper”})
计算每个“订单详情”行的行合计 已添加的自定义 = Table.AddColumn (RemovedColumns，“Custom”，每个 [Order_Details.UnitPrice] * [Order_Details.Quantity])
= Table.AddColumn (#“Expanded Order_Details”、“Line Total”，每个 [Order_Details.UnitPrice] * [Order_Details.Quantity])
更改为更有意义的名称 Lne Total 重命名的列 = Table.RenameColumns (InsertedCustom，{{“Custom”， “Line Total”}})
转换“订单日期”列，呈现年份 提取年份 = Table.TransformColumns (#“Grouped Rows”，{{“year”， Date.Year， Int64.Type}})
更改为
更有意义的名称、OrderDate 和 Year		 重命名的列 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) 中的所有列，以及相关表 (总销售额) 的单个表结构化列。 选择“ 展开 ”图标，从辅助表或相关表向主表添加新列。

合并最终结果

步骤 2：展开合并列

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

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

  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 帮助