教程:分析来自 Excel 和 OData 源的销售数据Tutorial: Analyzing sales data from Excel and an OData feed

使用 Power BI Desktop,你可以连接到各种类型的不同数据源,然后以形成有趣和令人信服的数据分析和可视化效果的方式对它们进行合并和调整。With Power BI Desktop, you can connect to all sorts of different data sources, then combine and shape them in ways that facilitate making interesting, compelling data analysis and visualizations. 在本教程中,你将了解如何合并来自两个数据源的数据。In this tutorial, you'll learn how to combine data from two data sources.

数据遍布于多个数据源是很常见的,例如产品信息可能位于某个数据库,而销售信息则位于另一个数据库。It's common to have data spread across multiple data sources, such as product information in one database, and sales information in another. 你将在本文档中了解的技术包括 Excel 工作簿和 OData 源,但这些技术也可以应用于其他数据源,如 SQL Server 查询、CSV 文件或 Power BI Desktop 中的任何数据源。The techniques you'll learn in this document include an Excel workbook and an OData feed, but these techniques can be applied to other data sources too, like SQL Server queries, CSV files, or any data source in Power BI Desktop.

在本教程中,你将从 Excel(包含产品信息)和 OData 源(包含订单数据)导入数据。In this tutorial, you import data from Excel (it includes product information) and from an OData feed (which contains orders data). 你将执行转换和聚合步骤,以及合并来自这两个源的数据以生成呈现交互式可视化效果的 Total Sales per Product and Year 报表。You'll perform transformation and aggregation steps, and combine data from both sources to produce a Total Sales per Product and Year report that includes interactive visualizations.

下面是最终报表的外观:Here's what the final report will look like:

若要按照本教程中的步骤,你需要 Products 工作簿,可通过以下方式下载单击此处下载Products.xlsxTo follow the steps in this tutorial you need the Products workbook, which you can download: clickhere to downloadProducts.xlsx.

另存为对话框中,将文件命名为Products.xlsxIn the Save As dialog box, name the file Products.xlsx.

任务 1:从 Excel 工作簿获取产品数据Task 1: Get product data from an Excel workbook

在此任务中,需要将 Products.xlsx 文件内的产品导入 Power BI Desktop 中。In this task, you import products from the Products.xlsx file into Power BI Desktop.

步骤 1:连接到 Excel 工作簿Step 1: Connect to an Excel workbook

  1. 启动 Power BI Desktop。Launch Power BI Desktop.
  2. 从“开始”功能区选择获取数据From the Home ribbon, select Get Data. Excel 是最常用的数据连接之一,因此你可以直接从获取数据菜单中选择。Excel is one of the Most Common data connections, so you can select it directly from the Get Data menu.

  3. 如果直接选择“获取数据”按钮,你还可以选择文件> Excel,然后选择连接。If you select the Get Data button directly, you can also select FIle > Excel and select Connect.
  4. 打开文件对话框中,选择 Products.xlsx 文件。In the Open File dialog box, select the Products.xlsx file.
  5. 导航器窗格中,选择 Products 表,然后选择编辑In the Navigator pane, select the Products table and then select Edit.

步骤 2:删除其他列,只显示感兴趣的列Step 2: Remove other columns to only display columns of interest

在此步骤中,需要删除除ProductIDProductNameUnitsInStockQuantityPerUnit 之外的所有列。In this step you remove all columns except ProductID, ProductName, UnitsInStock, and QuantityPerUnit. 在 Power BI Desktop 中,完成相同的任务往往有几种方法。In Power BI Desktop, there are often a few ways to accomplish the same task. 例如,通过在列或单元格上右键单击菜单也能获取位于功能区中的许多按钮。For example, many buttons in the ribbon can also be achieved by using the right-click menu on a column or a cell.

Power BI Desktop 中包括查询编辑器,你可以在此处对数据连接进行调整和转换。Power BI Desktop includes Query Editor, which is where you shape and transform your data connections. 导航器选择编辑时,查询编辑器会自动打开。Query Editor opens automatically when you select Edit from Navigator. 还可以通过从 Power BI Desktop 中的开始功能区选择编辑查询来打开查询编辑器。You can also open the Query Editor by selecting Edit Queries from the Home ribbon in Power BI Desktop. 在查询编辑器中执行以下步骤。The following steps are performed in Query Editor.

  1. 在查询编辑器中,选择 ProductIDProductNameQuantityPerUnitUnitsInStock 列(通过按住 Ctrl 并单击来选择多个列,或按住 Shift 并单击来选择相邻的列)。In Query Editor, select the ProductID, ProductName, QuantityPerUnit, and UnitsInStock columns (use Ctrl+Click to select more than one column, or Shift+Click to select columns that are beside each other).
  2. 从功能区选择删除列 > 删除其他列,或右键单击某个列标题,然后单击删除其他列Select Remove Columns > Remove Other Columns from the ribbon, or right-click on a column header and click Remove Other Columns.

步骤 3:更改 UnitsInStock 列的数据类型Step 3: Change the data type of the UnitsInStock column

当查询编辑器连接到数据时,它会检查每个字段,并确定最佳的数据类型。When Query Editor connects to data, it reviews each field and to determine the best data type. 对于 Excel 工作簿,库存产品将始终为整数,因此在此步骤中需要确认 UnitsInStock 列的数据类型为整数。For the Excel workbook, products in stock will always be a whole number, so in this step you confirm the UnitsInStock column’s datatype is Whole Number.

  1. 选择 UnitsInStock 列。Select the UnitsInStock column.
  2. 选择开始功能区中的数据类型下拉列表按钮。Select the Data Type drop-down button in the Home ribbon.
  3. 如果没有整数,请从下拉列表中选择整数数据类型(数据类型:按钮也会显示当前所选的数据类型)。If not already a Whole Number, select Whole Number for data type from the drop down (the Data Type: button also displays the data type for the current selection).

创建的 Power BI Desktop 步骤Power BI Desktop steps created

在查询编辑器中执行查询活动时,将创建查询步骤并在应用步骤列表中的查询设置窗格中列出。As you perform query activities in Query Editor, query steps are created and listed in the Query Settings pane, in the Applied Steps list. 每个查询的步骤都有相应的公式,也称为“M”语言。Each query step has a corresponding formula, also known as the "M" language. 有关“M”公式语言的详细信息,请参阅了解 Power BI 公式For more information about the “M” formula language, see Learn about Power BI formulas.

任务Task 查询步骤Query step 公式Formula
连接到 Excel 工作簿Connect to an Excel workbook Source Source{[Name="Products"]}[Data]Source{[Name="Products"]}[Data]
将第一行提升至表格列标题Promote the first row to table column headers FirstRowAsHeaderFirstRowAsHeader Table.PromoteHeadersTable.PromoteHeaders
(产品)(Products)
删除其他列,只显示感兴趣的列Remove other columns to only display columns of interest RemovedOtherColumnsRemovedOtherColumns Table.SelectColumnsTable.SelectColumns
(FirstRowAsHeader,{"ProductID", "ProductName", "QuantityPerUnit", "UnitsInStock"})(FirstRowAsHeader,{"ProductID", "ProductName", "QuantityPerUnit", "UnitsInStock"})
更改数据类型Change datatype 更改的类型Changed Type Table.TransformColumnTypes(#"Removed Other Columns",{{"UnitsInStock", Int64.Type}})Table.TransformColumnTypes(#"Removed Other Columns",{{"UnitsInStock", Int64.Type}})

任务 2:从 OData 源导入订单数据Task 2: Import order data from an OData feed

在此任务中,你需要导入订单数据。In this task, you'll bring in order data. 此步骤中表示连接到销售系统。This step represents connecting to a sales system. 将位于下面的 URL 的示例 Northwind OData 的数据导入 Power BI Desktop,你可以从下面的步骤复制(并粘贴):http://services.odata.org/V3/Northwind/Northwind.svc/You import data into Power BI Desktop from the sample Northwind OData feed at the following URL, which you can copy (and then paste) in the steps below: http://services.odata.org/V3/Northwind/Northwind.svc/

步骤 1:连接到 OData 源Step 1: Connect to an OData feed

  1. 从查询编辑器中的开始功能区选项卡中选择获取数据。From the Home ribbon tab in Query Editor, select Get Data.
  2. 浏览到 OData 源数据源。Browse to the OData Feed data source.
  3. OData 源对话框中,粘贴 Northwind OData 源的 URLIn the OData Feed dialog box, paste the URL for the Northwind OData feed.
  4. 选择确定Select OK.
  5. 导航器窗格中,选择 Orders 表,然后选择编辑In the Navigator pane, select the Orders table, and then select Edit.

备注

单击表名即可查看预览,而不必选择复选框。You can click a table name, without selecting the checkbox, to see a preview.

步骤 2:展开 Order_Details 表Step 2: Expand the Order_Details table

Orders 表包含对 Details 表的引用,其中包含每个订单中的各个产品。The Orders table contains a reference to a Details table, which contains the individual products that were included in each Order. 当你连接到多个表的数据源(如关系数据库)时,可以使用这些引用来构建你的查询。When you connect to data sources with multiples tables (such as a relational database) you can use these references to build up your query.

在此步骤中,展开与 Orders 表相关的Order_Details 表,以将Order_Details 中的 ProductIDUnitPriceQuantity 列合并到 Orders 表。In this step, you expand the Order_Details table that is related to the Orders table, to combine the ProductID, UnitPrice, and Quantity columns from Order_Details into the Orders table. 这是数据在这些表中的表示形式:This is a representation of the data in these tables:

展开操作会将相关表中的列合并到主体表。The Expand operation combines columns from a related table into a subject table. 当查询运行时,相关表 (Order_Details) 中的行将合并到主体表 (Orders) 中的行。When the query runs, rows from the related table (Order_Details) are combined into rows from the subject table (Orders).

展开 Order_Details 表后,将会有三个新列和其他行添加到 Orders 表中,分别用于嵌套或相关表中的每一行。After you expand the Order_Details table, three new columns and additional rows are added to the Orders table, one for each row in the nested or related table.

  1. 查询视图中,滚动到 Order_Details 列。In the Query View, scroll to the Order_Details column.
  2. Order_Details 列中,选择展开图标 ()。In the Order_Details column, select the expand icon ().
  3. 展开下拉列表中:In the Expand drop-down:
    1. 选择(选择所有列)以清除所有列。Select (Select All Columns) to clear all columns.
    2. 选择 ProductIDUnitPriceQuantitySelect ProductID, UnitPrice, and Quantity.
    3. 单击确定Click OK.

步骤 3:删除其他列,只显示感兴趣的列Step 3: Remove other columns to only display columns of interest

在此步骤中,将删除 OrderDate、ShipCityShipCountryOrder_Details.ProductIDOrder_Details.UnitPriceOrder_Details.Quantity 以外的所有列。In this step you remove all columns except OrderDate, ShipCity, ShipCountry, Order_Details.ProductID, Order_Details.UnitPrice, and Order_Details.Quantity columns. 在上一任务中,你使用了删除其他列In the previous task, you used Remove Other Columns. 对于此任务中,你需要删除所选的列。For this task, you remove selected columns.

  1. 查询视图中选择所有列,方法是完成a.In the Query View, select all columns by completing a. 和 b:and b.:
    1. 单击第一列 (OrderID)。Click the first column (OrderID).
    2. 按住 Shift 并单击最后一列 (Shipper)。Shift+Click the last column (Shipper).
    3. 现在已选中了所有列,按住 Ctrl 并单击来取消选择以下列:OrderDateShipCityShipCountryOrder_Details.ProductIDOrder_Details.UnitPrice 以及 Order_Details.QuantityNow that all columns are selected, use Ctrl+Click to unselect the following columns: OrderDate, ShipCity, ShipCountry, Order_Details.ProductID, Order_Details.UnitPrice, and Order_Details.Quantity.
  2. 现在只选中了我们要删除的列,右键单击任何所选列的标题并单击删除列Now that only the columns we want to remove are selected, right-click on any selected column header and click Remove Columns.

步骤 4:计算每个 Order_Details 行的项目总计Step 4: Calculate the line total for each Order_Details row

Power BI Desktop 可让你创建针对正在导入的列的计算,因此你可以加强连接到的数据。Power BI Desktop lets you to create calculations based on the columns you are importing, so you can enrich the data that you connect to. 在此步骤中,你需要创建自定义列来计算每个 Order_Details 行的项目总计。In this step, you create a Custom Column to calculate the line total for each Order_Details row.

计算每个 Order_Details 行的项目总计:Calculate the line total for each Order_Details row:

  1. 添加列功能区选项卡上,单击添加 自定义列In the Add Column ribbon tab, click Add Custom Column.

  2. 添加自定义列对话框中的自定义列公式文本框中,输入 [Order_Details.UnitPrice] * [Order_Details.Quantity]In the Add Custom Column dialog box, in the Custom Column Formula textbox, enter [Order_Details.UnitPrice] * [Order_Details.Quantity].
  3. 新的列名称文本框中输入 LineTotalIn the New column name textbox, enter LineTotal.

  4. 单击确定Click OK.

步骤 5:设置 LineTotal 字段的数据类型Step 5: Set the datatype of the LineTotal field

  1. 右键单击 LineTotal 列。Right click the LineTotal column.
  2. 选择“更改类型”,然后选择“十进制数”。Select Change Type and choose Decimal Number.

步骤 6:对查询中的列进行重命名和重新排序Step 6: Rename and reorder columns in the query

在此步骤中,你需要对最后的列进行重命名和改变顺序,从而在完成时使模板在创建报表时更易于使用。In this step you finish making the model easy to work with when creating reports, by renaming the final columns and changing their order.

  1. 查询编辑器中,将 LineTotal 向左拖动到 ShipCountry 之后。In Query Editor, drag the LineTotal column to the left, after ShipCountry.

  2. 通过双击每个列标题,并从列名称中删除文本,删除以下列的 Order_Details.Remove the Order_Details. 前缀:Order_Details.ProductIDOrder_Details.UnitPriceOrder_Details.Quantityprefix from the Order_Details.ProductID, Order_Details.UnitPrice and Order_Details.Quantity columns, by double-clicking on each column header, and then deleting that text from the column name.

创建的 Power BI Desktop 步骤Power BI Desktop steps created

在查询编辑器中执行查询活动时,将创建查询步骤并在应用步骤列表中的查询设置窗格中列出。As you perform query activities in Query Editor, query steps are created and listed in the Query Settings pane, in the Applied Steps list. 每个查询的步骤都有对应的 Power Query 公式,也称为“M”语言。Each query step has a corresponding Power Query formula, also known as the "M" language. 有关此公式语言的详细信息,请参阅了解 Power BI 公式For more information about this formula language, see Learn about Power BI formulas.

任务Task 查询步骤Query step 公式Formula
连接到 OData 源Connect to an OData feed Source Source{[Name="Orders"]}[Data]Source{[Name="Orders"]}[Data]
展开 Order_Details 表Expand the Order_Details table 展开 Order_DetailsExpand Order_Details Table.ExpandTableColumnTable.ExpandTableColumn
(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
删除其他列,只显示感兴趣的列Remove other columns to only display columns of interest RemovedColumnsRemovedColumns Table.RemoveColumnsTable.RemoveColumns
(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
计算每个 Order_Details 行的项目总计:Calculate the line total for each Order_Details row InsertedColumnInsertedColumn Table.AddColumnTable.AddColumn
(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

任务 3:合并 Products 和 Total Sales 查询Task 3: Combine the Products and Total Sales queries

Power BI Desktop 不需要合并查询来建立报表。Power BI Desktop does not require you to combine queries to report on them. 相反,你可以创建数据集之间的关系Instead, you can create Relationships between datasets. 这些关系可以在数据集通用的任何列上创建。These relationships can be created on any column that is common to your datasets. 有关详细信息,请参阅创建和管理关系For more information see Create and manage relationships.

在本教程中,有共用通用“ProductID”字段的 Orders 和 Products 数据,因此我们需要确保它们在搭配 Power BI Desktop 使用的模型中具有某种关系。In this tutorial, we have Orders and Products data that share a common 'ProductID' field, so we need to ensure there's a relationship between them in the model we're using with Power BI Desktop. 只需在 Power BI Desktop 中指定这两个表中的列相关(即这些列具有相同的值)即可。Simply specify in Power BI Desktop that the columns from each table are related (i.e. columns that have the same values). Power BI Desktop 会为你找出关系的的方向和基数。Power BI Desktop works out the direction and cardinality of the relationship for you. 在某些情况下,它甚至会自动检测关系。In some cases, it will even detect the relationships automatically.

在此任务中,你将确认 ProductsTotal Sales 查询在 Power BI Desktop 中建立了关系。In this task, you confirm that a relationship is established in Power BI Desktop between the Products and Total Sales queries.

步骤 1:确认 Products 和 Total Sales 之间的关系Step 1: Confirm the relationship between Products and Total Sales

  1. 首先,我们需要将在查询编辑器中创建的模型加载到 Power BI Desktop。First, we need to load the model that we created in Query Editor into Power BI Desktop. 从查询编辑器中的开始功能区选择关闭并加载From the Home ribbon of Query Editor, select Close & Load.

  2. Power BI Desktop 将从这两个查询加载数据。Power BI Desktop loads the data from the two queries.

  3. 加载数据后,选择开始功能区中的管理关系按钮。Once the data is loaded, select the Manage Relationships button Home ribbon.

  4. 选择新建...Select the New… 按钮button

  5. 当我们尝试创建关系时,我们看到已经存在一个关系!When we attempt to create the relationship, we see that one already exists! 创建关系对话框(阴影列)中所示,每个查询中的 ProductsID 字段都有一个已建立的关系。As shown in the Create Relationship dialog (by the shaded columns), the ProductsID fields in each query already have an established relationship.

  6. 选择取消,然后选择 Power BI Desktop 中的关系视图。Select Cancel, and then select Relationship view in Power BI Desktop.

  7. 如下图所示,查询之间的关系将以视觉化方式显示。We see the following, which visualizes the relationship between the queries.

  8. 当双击连接到查询的线条上的箭头时,将会显示编辑关系对话框。When you double-click the arrow on the line that connects the to queries, an Edit Relationship dialog appears.

  9. 由于无需进行任何更改,因此我们只需选择取消来关闭编辑关系对话框。No need to make any changes, so we'll just select Cancel to close the Edit Relationship dialog.

任务 4:使用数据生成视觉效果Task 4: Build visuals using your data

Power BI Desktop 使你可以创建多种可视化效果来深入探索你的数据。Power BI Desktop lets you create a variety of visualizations to gain insights from your data. 你可以生成多页报表,而且每页可以有多个视觉效果。You can build reports with multiple pages and each page can have multiple visuals. 你可以与可视化效果进行交互,以帮助分析和了解你的数据。You can interact with your visualizations to help analyze and understand your data. 有关编辑报表的详细信息,请参阅编辑报表For more information about editing reports, see Edit a Report.

在本任务中,你将基于以前加载的数据创建报表。In this task, you create a report based on the data previously loaded. 使用字段窗格选择要从中创建可视化效果的列。You use the Fields pane to select the columns from which you create the visualizations.

步骤 1:创建显示产品的库存单位数量和年度总销售额的图表Step 1: Create charts showing Units in Stock by Product and Total Sales by Year

UnitsInStock 从字段窗格(字段窗格位于屏幕最右侧)拖到画布上的空白区域。Drag UnitsInStock from the Field pane (the Fields pane is along the right of the screen) onto a blank space on the canvas. 这样便创建了表可视化效果。A Table visualization is created. 接下来,将 ProductName 拖动到可视化效果窗格下半部分的“轴”框中。Next, drag ProductName to the Axis box, found in the bottom half of the Visualizations pane. 然后使用可视化效果右上角的图示来选择排序依据 >UnitsInStockThen we then select Sort By > UnitsInStock using the skittles in the top right corer of the visualization.

OrderDate 拖动到第一个图表下方的画布上,然后将 LineTotal(再次从字段窗格)拖动到视觉效果,然后选择折线图。Drag OrderDate to the canvas beneath the first chart, then drag LineTotal (again, from the Fields pane) onto the visual, then select Line Chart. 这样便创建了如下所示的可视化效果。The following visualization is created.

接下来,将 ShipCountry 拖动到右上角画布上的空白处。Next, drag ShipCountry to a space on the canvas in the top right. 由于你已经选择了地理字段,因此会自动创建地图。Because you selected a geographic field, a map was created automatically. 现在,将 LineTotal 拖动到字段;地图上代表每个国家/地区的圆圈将会根据运送至该国家/地区的订单的 LineTotal 呈现出相对应的大小。Now drag LineTotal to the Values field; the circles on the map for each country are now relative in size to the LineTotal for orders shipped to that country.

步骤 2:与报表视觉效果进行交互以进一步分析Step 2: Interact with your report visuals to analyze further

Power BI Desktop 使你可以与相互突出显示和筛选的视觉效果进行交互,从而发觉进一步的趋势。Power BI Desktop lets you interact with visuals that cross-highlight and filter each other to uncover further trends. 有关详细信息,请参阅在报表中进行筛选和突出显示For more detail see Filtering and Highlighting in Reports

  1. 单击位于加拿大中心的浅蓝色圆形。Click on the light blue circle centered in Canada. 请注意如何筛选其他视觉效果,才能只显示加拿大的库存 (ShipCountry) 和总订单数 (LineTotal)。Note how the other visuals are filtered to show Stock (ShipCountry) and Total Orders (LineTotal) just for Canada.

完成销售分析报表Complete Sales Analysis Report

执行所有这些步骤后,你将拥有一个合并了来自 Products.xlsx 文件和 Northwind OData 源的数据的销售报表。After you perform all these steps, you will have a Sales Report that combines data from Products.xlsx file and Northwind OData feed. 此报表显示帮助分析来自不同国家/地区的销售信息的视觉效果。The report shows visuals that help analyze sales information from different countries. 你可以在此处下载本教程的完整 Power BI Desktop 文件。You can download a completed Power BI Desktop file for this tutorial here.

后续步骤Next steps