使用 Power BI Desktop,你可以连接到各种类型的不同数据源,然后以形成有趣和令人信服的数据分析和可视化效果的方式对它们进行合并和调整。 在本教程中,你将了解如何合并来自两个数据源的数据。

数据遍布于多个数据源是很常见的,例如产品信息可能位于某个数据库,而销售信息则位于另一个数据库。 你将在本文档中了解的技术包括 Excel 工作簿和 OData 源,但这些技术也可以应用于其他数据源,如 SQL Server 查询、CSV 文件或 Power BI Desktop 中的任何数据源。

在本教程中,你将从 Excel(包含产品信息)和 OData 源(包含订单数据)导入数据。 你将执行转换和聚合步骤,以及合并来自这两个源的数据以生成呈现交互式可视化效果的 Total Sales per Product and Year 报表。

下面是最终报表的外观:

若要按照本教程中的步骤,你需要 Products 工作簿,可通过以下方式下载单击此处下载Products.xlsx

另存为对话框中,将文件命名为Products.xlsx

任务 1:从 Excel 工作簿获取产品数据

在此任务中,需要将 Products.xlsx 文件内的产品导入 Power BI Desktop 中。

步骤 1:连接到 Excel 工作簿

  1. 启动 Power BI Desktop。

  2. 从“开始”功能区选择获取数据。 Excel 是最常用的数据连接之一,因此你可以直接从获取数据菜单中选择。

  3. 如果直接选择“获取数据”按钮,你还可以选择文件> Excel,然后选择连接。

  4. 打开文件对话框中,选择 Products.xlsx 文件。

  5. 导航器窗格中,选择 Products 表,然后选择编辑

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

在此步骤中,需要删除除ProductIDProductNameUnitsInStockQuantityPerUnit 之外的所有列。 在 Power BI Desktop 中,完成相同的任务往往有几种方法。 例如,通过在列或单元格上右键单击菜单也能获取位于功能区中的许多按钮。

Power BI Desktop 中包括查询编辑器,你可以在此处对数据连接进行调整和转换。 从导航器选择编辑时,查询编辑器会自动打开。 还可以通过从 Power BI Desktop 中的开始功能区选择编辑查询来打开查询编辑器。 在查询编辑器中执行以下步骤。

  1. 在查询编辑器中,选择 ProductIDProductNameQuantityPerUnitUnitsInStock 列(通过按住 Ctrl 并单击来选择多个列,或按住 Shift 并单击来选择相邻的列)。

  2. 从功能区选择删除列 > 删除其他列,或右键单击某个列标题,然后单击删除其他列

步骤 3:更改 UnitsInStock 列的数据类型

当查询编辑器连接到数据时,它会检查每个字段,并确定最佳的数据类型。 对于 Excel 工作簿,库存产品将始终为整数,因此在此步骤中需要确认 UnitsInStock 列的数据类型为整数。

  1. 选择 UnitsInStock 列。

  2. 选择开始功能区中的数据类型下拉列表按钮。

  3. 如果没有整数,请从下拉列表中选择整数数据类型(数据类型:按钮也会显示当前所选的数据类型)。

创建的 Power BI Desktop 步骤

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

任务 查询步骤 公式
连接到 Excel 工作簿 Source{[Name="Products"]}[Data]
将第一行提升至表格列标题 FirstRowAsHeader Table.PromoteHeaders
(产品)
删除其他列,只显示感兴趣的列 RemovedOtherColumns Table.SelectColumns
(FirstRowAsHeader,{"ProductID", "ProductName", "QuantityPerUnit", "UnitsInStock"})
更改数据类型 更改的类型 Table.TransformColumnTypes(#"Removed Other Columns",{{"UnitsInStock", Int64.Type}})

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

在此任务中,你需要导入订单数据。 此步骤中表示连接到销售系统。 将位于下面的 URL 的示例 Northwind OData 的数据导入 Power BI Desktop,你可以从下面的步骤复制(并粘贴):http://services.odata.org/V3/Northwind/Northwind.svc/

步骤 1:连接到 OData 源

  1. 从查询编辑器中的开始功能区选项卡中选择获取数据。

  2. 浏览到 OData 源数据源。

  3. OData 源对话框中,粘贴 Northwind OData 源的 URL

  4. 选择确定

  5. 导航器窗格中,选择 Orders 表,然后选择编辑

注意单击表名即可查看预览,而不必选择复选框。

步骤 2:展开 Order_Details 表

Orders 表包含对 Details 表的引用,其中包含每个订单中的各个产品。 当你连接到多个表的数据源(如关系数据库)时,可以使用这些引用来构建你的查询。

在此步骤中,展开与 Orders 表相关的Order_Details 表,以将Order_Details 中的 ProductIDUnitPriceQuantity 列合并到 Orders 表。 这是数据在这些表中的表示形式:

展开操作会将相关表中的列合并到主体表。 当查询运行时,相关表 (Order_Details) 中的行将合并到主体表 (Orders) 中的行。

展开 Order_Details 表后,将会有三个新列和其他行添加到 Orders 表中,分别用于嵌套或相关表中的每一行。

  1. 查询视图中,滚动到 Order_Details 列。
  2. Order_Details 列中,选择展开图标 ( )。
  3. 展开下拉列表中:
    1. 选择(选择所有列)以清除所有列。
    2. 选择 ProductIDUnitPriceQuantity
    3. 单击确定

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

在此步骤中,将删除 OrderDate、ShipCityShipCountryOrder_Details.ProductIDOrder_Details.UnitPriceOrder_Details.Quantity 以外的所有列。 在上一任务中,你使用了删除其他列。 对于此任务中,你需要删除所选的列。

  1. 查询视图中选择所有列,方法是完成a. 和 b:
    1. 单击第一列 (OrderID)。
    2. 按住 Shift 并单击最后一列 (Shipper)。
    3. 现在已选中了所有列,按住 Ctrl 并单击来取消选择以下列:OrderDateShipCityShipCountryOrder_Details.ProductIDOrder_Details.UnitPrice 以及 Order_Details.Quantity
  2. 现在只选中了我们要删除的列,右键单击任何所选列的标题并单击删除列

步骤 4:计算每个 Order_Details 行的项目总计

Power BI Desktop 可让你创建针对正在导入的列的计算,因此你可以加强连接到的数据。 在此步骤中,你需要创建自定义列来计算每个 Order_Details 行的项目总计。

计算每个 Order_Details 行的项目总计:

  1. 添加列功能区选项卡上,单击添加 自定义列

  2. 添加自定义列对话框中的自定义列公式文本框中,输入 [Order_Details.UnitPrice] * [Order_Details.Quantity]

  3. 新的列名称文本框中输入 LineTotal

  4. 单击确定

步骤 5:设置 LineTotal 字段的数据类型

  1. 右键单击 LineTotal 列。

  2. 选择更改类型,然后选择 **十进制数。

步骤 6:对查询中的列进行重命名和重新排序

在此步骤中,你需要对最后的列进行重命名和改变顺序,从而在完成时使模板在创建报表时更易于使用。

  1. 查询编辑器中,将 LineTotal 向左拖动到 ShipCountry 之后。

  2. 通过双击每个列标题,并从列名称中删除文本,删除以下列的 Order_Details. 前缀:Order_Details.ProductIDOrder_Details.UnitPriceOrder_Details.Quantity

创建的 Power BI Desktop 步骤

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

任务 查询步骤 公式
连接到 OData 源 Source{[Name="Orders"]}[Data]
展开 Order_Details 表 展开 Order_Details 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"})
计算每个 Order_Details 行的项目总计: InsertedColumn Table.AddColumn
(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

任务 3:合并 Products 和 Total Sales 查询

Power BI Desktop 不需要合并查询来建立报表。 相反,你可以创建数据集之间的关系。 这些关系可以在数据集通用的任何列上创建。 有关详细信息,请参阅创建和管理关系

在本教程中,有共用通用“ProductID”字段的 Orders 和 Products 数据,因此我们需要确保它们在搭配 Power BI Desktop 使用的模型中具有某种关系。 只需在 Power BI Desktop 中指定这两个表中的列相关(即这些列具有相同的值)即可。 Power BI Desktop 会为你找出关系的的方向和基数。 在某些情况下,它甚至会自动检测关系。

在此任务中,你将确认 ProductsTotal Sales 查询在 Power BI Desktop 中建立了关系。

步骤 1:确认 Products 和 Total Sales 之间的关系

  1. 首先,我们需要将在查询编辑器中创建的模型加载到 Power BI Desktop。 从查询编辑器中的开始功能区选择关闭并加载

  2. Power BI Desktop 将从这两个查询加载数据。

  3. 加载数据后,选择开始功能区中的管理关系按钮。

  4. 选择新建... 按钮

  5. 当我们尝试创建关系时,我们看到已经存在一个关系! 如创建关系对话框(阴影列)中所示,每个查询中的 ProductsID 字段都有一个已建立的关系。

  6. 选择取消,然后选择 Power BI Desktop 中的关系视图。

  7. 如下图所示,查询之间的关系将以视觉化方式显示。

  8. 当双击连接到查询的线条上的箭头时,将会显示编辑关系对话框。

  9. 由于无需进行任何更改,因此我们只需选择取消来关闭编辑关系对话框。

任务 4:使用数据生成视觉效果

Power BI Desktop 使你可以创建多种可视化效果来深入探索你的数据。 你可以生成多页报表,而且每页可以有多个视觉效果。 你可以与可视化效果进行交互,以帮助分析和了解你的数据。 有关编辑报表的详细信息,请参阅编辑报表

在本任务中,你将基于以前加载的数据创建报表。 使用字段窗格选择要从中创建可视化效果的列。

步骤 1:创建显示产品的库存单位数量和年度总销售额的图表

UnitsInStock 从字段窗格(字段窗格位于屏幕最右侧)拖到画布上的空白区域。 这样便创建了表可视化效果。 接下来,将 ProductName 拖动到可视化效果窗格下半部分的“轴”框中。 然后使用可视化效果右上角的图示来选择排序依据 >UnitsInStock

OrderDate 拖动到第一个图表下方的画布上,然后将 LineTotal(再次从字段窗格)拖动到视觉效果,然后选择折线图。 这样便创建了如下所示的可视化效果。

接下来,将 ShipCountry 拖动到右上角画布上的空白处。 由于你已经选择了地理字段,因此会自动创建地图。 现在,将 LineTotal 拖动到字段;地图上代表每个国家/地区的圆圈将会根据运送至该国家/地区的订单的 LineTotal 呈现出相对应的大小。

步骤 2:与报表视觉效果进行交互以进一步分析

Power BI Desktop 使你可以与相互突出显示和筛选的视觉效果进行交互,从而发觉进一步的趋势。 有关详细信息,请参阅在报表中进行筛选和突出显示

  1. 单击位于加拿大中心的浅蓝色圆形。 请注意如何筛选其他视觉效果,才能只显示加拿大的库存 (ShipCountry) 和总订单数 (LineTotal)。

完成销售分析报表

执行所有这些步骤后,你将拥有一个合并了来自 Products.xlsx 文件和 Northwind OData 源的数据的销售报表。 此报表显示帮助分析来自不同国家/地区的销售信息的视觉效果。 你可以在此处下载本教程的完整 Power BI Desktop 文件。

我还可以在哪些位置获取详细信息?