获取数据Getting data

数据是 Power BI 的核心。Data is at the heart of Power BI. 引导式学习部分介绍如何获取和使用 Power BI 中的数据。In this Guided Learning section, you learn how to get and work with data in Power BI.

你将了解操作方式...

Power BI Desktop 概述Overview of Power BI Desktop

欢迎来到 Power BI 引导学习课程的第二部分:获取数据Welcome to the second section in this Guided Learning course for Power BI, called Getting Data. 本部分介绍 Power BI 中许多以数据为中心的功能和工具,其中重点介绍 Power BI Desktop。This section looks at the many data-centric features and tools of Power BI, focusing on Power BI Desktop. 其中许多工具也适用于 Power BI 服务,所以你对于本部分的学习可以达到事半功倍的效果。Many of these tools also apply to the Power BI service, so you're doing double duty with your learning in this section.

当你获得数据时,有时数据并非你所希望的那样,它的格式不完全正确或者说不是 干净 的。When you get data, sometimes it's not quite as well-formed, or clean, as you want it to be. 在本部分中,你会学习到如何获取数据,如何让它变为正确的格式(有时称为 清洗转换 数据),并且还将学习一些高级的技巧,可使数据的获取更轻松。So in this section you learn how to get data, how to clean it up (sometimes called cleaning or transforming data), and also learn some advanced tricks that can make your data-getting life easier.

和往常一样,本课程将按照 Power BI 中的工作流引导你进入学习之旅。As always in this course, your learning journey follows the same path as the flow of work in Power BI. 那么,我们就来看看 Power BI Desktop,这通常是开始的位置。As such, let's check out Power BI Desktop, where it often begins.

Power BI Desktop 概述An overview of Power BI Desktop

Power BI Desktop 是用于连接、清除和可视化数据的工具。Power BI Desktop is a tool to connect to, clean, and visualize your data. 使用 Power BI Desktop,可以连接到数据,然后以不同的方式将数据建模和可视化数据。With Power BI Desktop, you can connect to data and then model and visualize it in different ways. 大多处理商业智能项目的用户会将大部分时间用在 Power BI Desktop 的使用上。Most users who are working on Business Intelligence projects will spend the majority of their time using Power BI Desktop.

可以从 Web 下载 Power BI Desktop,也可以从 Windows 应用商店Power BI Desktop 安装为应用,还可以从 Power BI 服务下载它。You can download Power BI Desktop from the web, you can also install Power BI Desktop as an app from the Windows Store, or you can download it from the Power BI service. 在该服务中,若要获取 Power BI Desktop,只需选择 Power BI 右上侧的向下箭头按钮,然后选择“Power BI Desktop”。In the service, to get Power BI Desktop you just select the down arrow button in the upper right side of Power BI, then select Power BI Desktop.

Power BI Desktop 将作为应用程序安装到你的 Windows 计算机上。Power BI Desktop installs as an application on your Windows computer.

因此下载 Power BI Desktop 后,你将安装它并像运行其他应用程序那样在 Windows 上运行它。So once you download it, you'll install Power BI Desktop and run it like other applications on Windows. 以下图像显示了 Power BI Desktop 的启动屏幕,当你启动该应用程序时会出现此屏幕。The following image shows the Start Screen of Power BI Desktop, which appears when you start the application.

Power BI Desktop 可连接到种类广泛的多种数据源,包括本地数据库、Excel 工作表和云服务等。Power BI Desktop connects to a wide variety of data sources, from local on-premises databases to Excel worksheets to cloud services. 它可帮助清理数据和设置数据格式,以使数据更为有用,包括拆分和重命名列、更改数据类型和处理日期。It helps you clean and format your data to make it more usable, including splitting and renaming columns, changing data types, and working with dates. 还可创建列之间的关系,以便可以更轻松地将数据建模和分析数据。You can also create relationships between columns so that it's easier to model and analyze your data.

Power BI Desktop 入门Getting started with Power BI Desktop

在本主题中,我们将深入探讨一下 Power BI 的前两个部分如何结合在一起:In this topic, we take a closer look at how the first two parts of Power BI fit together:

  • Power BI Desktop 中创建一个报表。Create a report in Power BI Desktop
  • Power BI 服务中发布该报表。Publish the report in the Power BI service

我们将在 Power BI Desktop 中启动报表并选择 获取数据We’ll start in Power BI Desktop, and select Get Data. 此时将显示数据源集合,允许你选择数据源。The collection of data sources appears, allowing you to choose a data source. 下图显示了选择网页作为上述视频中的源,将选择 Excel 工作簿。The following image shows selecting a Web page as the source, in the video above, Will selected an Excel workbook.

无论选择的数据源是什么,Power BI 都会连接到该数据源,并显示来自源的可用数据。Regardless of which data source you choose, Power BI connects to that data source, and shows you the data available from that source. 下图是另一个示例,该示例来自于一个网页,该网页分析了不同的国家和一些有趣的退休统计数据。The following image is another example, this one is from a Web page that analyzes different states and some interesting retirement statistics.

在 Power BI Desktop 报表 视图中,你可以开始生成报表。In Power BI Desktop Report view, you can begin to build reports.

报表视图具有五个主要区域:The Report view has five main areas:

  1. 功能区,用于显示与报表和可视化效果相关联的常见任务The ribbon, which displays common tasks associated with reports and visualizations
  2. 报表视图或画布,可在其中创建和排列可视化效果The Report view, or canvas, where visualizations are created and arranged
  3. 底部的页面选项卡,用于选择或添加报表页The Pages tab area along the bottom, which lets you select or add a report page
  4. 可视化效果窗格,你可以在其中更改可视化效果、自定义颜色或轴、应用筛选器、拖动字段等The Visualizations pane, where you can change visualizations, customize colors or axes, apply filters, drag fields, and more
  5. 字段窗格,可在其中将查询元素和筛选器拖到报表视图,或拖到可视化效果筛选器窗格The Fields pane, where query elements and filters can be dragged onto the Report view, or dragged to the Filters area of the Visualizations pane

通过选择边缘的小箭头,可以折叠可视化效果字段窗格,以便在报表视图中提供更多空间以生成炫酷的可视化效果。The Visualizations and Fields pane can be collapsed by selecting the small arrow along the edge, providing more space in the Report view to build cool visualizations. 修改可视化效果时,你会看到这些箭头朝上或朝下,这意味着你可以相应展开或折叠该部分。When modifying visualizations, you'll also see these arrows pointing up or down, which means you can expand or collapse that section, accordingly.

若要创建可视化效果,只需将字段从字段列表拖到报表视图即可。To create a visualization, just drag a field from the Fields list onto the Report view. 此示例中,我们来拖动 RetirementStats 中的“状态”字段,看看会发生什么。In this case, let’s drag the State field from RetirementStats, and see what happens.

看一下...Power BI Desktop 自动创建了基于地图的可视化效果,因为它识别到“状态”字段包含地理位置数据。Look at that... Power BI Desktop automatically created a map-based visualization, because it recognized that the State field contained geolocation data.

快进一点,现在我们看到,创建具有几个可视化效果的报表之后,我们就可以将该报表发布到 Power BI 服务了。Now let’s fast-forward a bit, and after creating a report with a few visualizations, we’re ready to publish this to the Power BI service. 在 Power BI Desktop 的开始功能区,选择发布On the Home ribbon in Power BI Desktop, select Publish.

系统将提示你登录到 Power BI。You’ll be prompted to sign in to Power BI.

当你登录并完成此发布过程后后,你将看到以下对话框。When you've signed in and the publish process is complete, you see the following dialog. 选择成功!下面的链接以进入 Power BI 服务,你可以在此处看到你刚刚发布的报表。You can select the link (below Success!) to be taken to the Power BI service, where you can see the report you just published.

当登录到 Power BI 时,你将看到刚才你在该服务上发布的 Power BI Desktop 文件。When you sign in to Power BI, you'll see Power BI Desktop file you just published in the service. 在下图中,在 Power BI Desktop 中创建的报表显示在报表部分。In the image below, the report created in Power BI Desktop is shown in the Reports section.

在该报表中,我可以选择大头针图标将该视觉对象固定到仪表板。In that report, I can choose the Pin icon to pin that visual to a dashboard. 下图显示了用一个亮框突出显示的大头针图标和箭头。The following image shows the pin icon highlighted with a bright box and arrow.

选择该图标时,将显示以下对话框,让我将视觉对象固定到现有仪表板,或创建新仪表板。When I select that, the following dialog appears, letting me pin the visual to an existing dashboard, or to create a new dashboard.

当我们从报告中固定几个视觉对象时,我们可以在仪表板中看到这些视觉对象。When we pin a couple of visuals from our report, we can see them in the dashboard.

当然,使用 Power BI 还可以执行更多操作,例如,共享你创建的仪表板。There’s a lot more you can do with Power BI, of course, such as sharing the dashboards you create. 稍后,我们将在本课程讨论共享。We'll discuss sharing later on in this course.

接下来,我们了解一下可以自动创建仪表板的功能,只需连接到云服务(如 Facebook、Salesforce)等服务即可。Next, we look at a feature that can automatically create dashboards for you, just by connecting to a cloud service like Facebook, Salesforce, and many others.

连接到 Power BI Desktop 中的数据源Connect to data sources in Power BI Desktop

Power BI Desktop 可以连接一系列数据源,包括本地数据库、Excel 工作簿和云服务。Power BI Desktop can connect to a whole range of data sources, including on-premises databases, Excel workbooks, and cloud services. 目前,超过 59 种不同的云服务(例如 GitHub 和 Marketo)具有特定的连接器,并且你可以通过 XML、CSV、文本和 ODBC 连接到通用源。Currently, over 59 different cloud services such as GitHub and Marketo have specific connectors, and you can connect to generic sources through XML, CSV, text, and ODBC. Power BI 甚至将直接从网站 URL 擦除表格数据!Power BI will even scrape tabular data directly from a website URL! 但让我们从头开始,打开 Power BI Desktop,然后连接数据。But let's start from the beginning, with opening Power BI Desktop and connecting to data.

启动 Power BI Desktop 并移至“开始屏幕”时,你可以在功能区“开始”选项卡上选择“获取数据”。When you start Power BI Desktop and move past the Start Screen, you can choose Get Data from the ribbon on the Home tab.

Power BI Desktop 每月均有更新,通过每个更新,Power BI Desktop 新增功能页将使用有关更新、到博客的链接以及下载链接的信息来获得更新。There are monthly updates to Power BI Desktop, and with each update, the Power BI Desktop What's New page gets updated with information about the updates, links to the blog, and a download link.

在 Power BI Desktop 中,存在各种不同的可用数据源。In Power BI Desktop, there are all sorts of different data sources available. 选择一个源以建立一个连接。Select a source to establish a connection. 根据你的选择,将要求你在计算机或网络上查找相应源,或者系统会提示你登录某个服务,以对你的请求进行验证。Depending on your selection, you will be asked to find the source on your computer or network, or be prompted to log in to a service to authenticate your request.

连接后,你将看到的第一个窗口是导航器After connecting, the first window you'll see is the Navigator. 导航器显示你的数据源的表或实体,单击其中一个将提供其内容的预览。The Navigator displays the tables or entities of your data source, and clicking on one gives you a preview of its contents. 然后,可以立即导入所选的表或实体,或在导入数据前,选择编辑以转换并清理数据。You can then import your selected tables or entities immediately, or select Edit to transform and clean your data before importing.

选定想要导入到 Power BI Desktop 中的表后,通过选择导航器右下角的加载按钮,可以将其加载到 Power BI Desktop 中。Once you've selected the tables you'd like to bring into Power BI Desktop, you can choose to load them into Power BI Desktop by selecting the Load button in the bottom right corner of Navigator. 但是,在将这些表加载到 Power BI Desktop 中之前,有时你可能想要先对它们进行更改。There are times, however, where you might want to make changes to those tables before you load them into Power BI Desktop. 你可能只想要客户的子集,或可能会筛选仅发生在特定国家/地区的销售数据。You might want only a subset of customers, or filter that data for sales that occurred only in a specific country. 在这些情况下,可以先选择“编辑”按钮,筛选或转换这些数据,再将其所有导入到 Power BI Desktop 中。In those cases, you can select the Edit button and filter or transform that data before bringing it all into Power BI Desktop.

下一部分中,我们将在该位置提取并编辑数据。We'll pick up there, and edit our data, in the next section.

使用查询编辑器清除并转换数据Clean and transform your data with the Query Editor

Power BI Desktop 包括查询编辑器,这是一款强大的工具,可将数据具体化并进行转换,以使其可供你的模型和可视化效果使用。Power BI Desktop includes Query Editor, a powerful tool for shaping and transforming data so it's ready for your models and visualizations. 当你在“导航器”中选择“编辑”时,此时将启动“查询编辑器”并使用你从数据源中选择的表或其他实体进行填充。When you select Edit from Navigator, Query Editor launches and is populated with the tables or other entities you selected from your data source.

你还可以使用开始功能区中的编辑查询按钮直接从 Power BI Desktop 启动查询编辑器You can also launch Query Editor directly from Power BI Desktop, using the Edit Queries button on the Home ribbon.

一旦查询编辑器与可供你定型的数据进行加载后,你将看到以下几个分区:Once Query Editor is loaded with data that's ready for you to shape, you see a handful of sections:

  1. 在功能区中,许多按钮当前处于活动状态,以与查询中的数据进行交互In the ribbon, many buttons are now active to interact with the data in the query
  2. 在左窗格中,列出了所有查询(每个查询各对应一个表或一个实体)并可供选择、查看和定型In the left pane, queries (one for each table, or entity) are listed and available for selection, viewing, and shaping
  3. 在中央窗格中,将显示已选择查询中的数据,可供你调整In the center pane, data from the selected query is displayed and available for shaping
  4. 显示的查询设置窗口列出了查询的属性和应用步骤The Query Settings window appears, listing the query’s properties and applied steps

在中央窗格中,右键单击某一列将显示多种不同的可用转换,如从表中删除该列、以新名称复制该列并替换值。In the center pane, right-clicking on a column displays a number of different available transformations, such as removing the column from the table, duplicating the column under a new name, and replacing values. 根据此菜单,你还可以通过常用分隔符将文本列拆分成多列。From this menu you can also split text columns into multiples by common delimiters.

查询编辑器功能区包含其他工具,如更改列的数据类型、添加科学记数法或根据日期(如一周中的某天)提取元素。The Query Editor ribbon contains additional tools, such as changing the data type of columns, adding scientific notation, or extracting elements from dates, such as day of the week.

应用转换时,每个步骤都将出现在查询编辑器右侧的查询设置窗格中的应用步骤列表中。As you apply transformations, each step appears in the Applied Steps list in the Query Settings pane on the right side of Query Editor. 可以使用此列表撤消或查看特定更改,甚至更改步骤的名称。You can use this list to undo or review specific changes, or even change the name of a step. 若要保存你的转换,请选择开始选项卡上的关闭并应用To save your transformations, select Close & Apply on the Home tab.

选择关闭并应用后,查询编辑器将应用所做的查询更改,并将其应用到 Power BI Desktop。Once you select Close & Apply, Query Editor applies the query changes you made, and applies them to Power BI Desktop.

当在查询编辑器中转换数据时,你可以进行所有类型的操作,其中包括高级转换。There are all sorts of things you can do when transforming data in Query Editor, including advanced transformations. 在下一节,我们将看看其中几个高级转换,让你感受一下如何用查询编辑器以几乎不可估量的方式转换数据。In the next section, we take a look at a few of those advanced transformations, to give you a sense of the almost immeasurable ways you can transform your data with Query Editor.

更高级的数据源和转换More advanced data sources and transformation

在本文中,我们将讨论一些适用于 Power BI Desktop 的高级数据导入和清理方法 。In this article, we investigate some advanced data import and cleaning techniques for Power BI Desktop. 查询编辑器中塑造好你的数据并将其引入 Power BI Desktop中后,你可以以多种不同的方式进行查看。Once you've shaped your data in Query Editor and brought it into Power BI Desktop, you can look at it in a few different ways. Power BI Desktop 中有三种视图:报表视图、数据视图和关系视图。There are three views in Power BI Desktop: Report view, Data view, and Relationships view. 通过选择画布左上方的图标可以查看每个视图。You see each view by selecting its icon in the upper left side of the canvas. 下图中选择了报表视图。In the following image, Report view is selected. 图标旁的黄色条指示处于活动状态的视图。The yellow bar beside the icon indicates which view is active.

若要更改视图,只需选择两个图标中的任意一个。To change the view, just select either of other two icons. 图标旁的黄色条指示处于活动状态的视图。The yellow bar beside the icon indicates which view is active.

Power BI Desktop 可在建模过程中随时将来自多个源的数据合并为单一报表。Power BI Desktop can combine data from multiple sources into a single report, at any time during the modelling process. 若要将其他源添加到现有报表,请在开始功能区中选择编辑查询,然后在查询编辑器中选择新源To add additional sources to an existing report, select Edit Queries in the Home ribbon and then select New Source in Query Editor.

Power BI Desktop 中可以使用许多不同的可能性数据源,其中包括文件夹。There are many different possible data sources you can use in Power BI Desktop, including Folders. 通过连接到文件夹,你可以同时导入来自多个文件的数据,例如一系列 Excel 文件的 CSV 文件。By connecting to a folder, you can import data from multiple files at once, such as a series of Excel files of CSV files. 所选文件夹中包含的文件会以二进制内容显示在查询编辑器中,单击内容顶部的双箭头图标将会加载它们的值。The files contained within your selected folder appear in Query Editor as binary content, and clicking the double-arrow icon at the top of the Content column loads their values.

Power BI 最有用的工具之一就是筛选器。One of Power BI's most useful tools is its Filters. 例如,选择与列相邻的下拉箭头将打开文本筛选器清单,你可以使用它来删除模型中的值。For example, selecting the drop-down arrow next to a column opens a checklist of text filters that you can use to remove values from your model.

你还可以合并和追加查询,并将多个表(或来自文件夹中多个文件的数据)转变成仅包含你所需数据的单一表格。You can also merge and append queries, and turn multiple tables (or data from various files, in folders) into a single table that contains just the data you want. 你可以使用追加查询工具将数据从新表添加到现有查询。You can use the Append Queries tool to add the data from a new table to an existing query. Power BI Desktop 将尝试匹配查询中的列,你可以根据需要在查询编辑器中进行调整。Power BI Desktop attempt to match up the columns in your queries, which you can then adjust as necessary in Query Editor.

最后,添加自定义列工具为高级用户提供了使用功能强大的 M 语言从草稿编写查询表达式的选项。Finally, the Add Custom Column tool gives advanced users the option of writing query expressions from scratch using the powerful M language. 你可以添加基于 M 查询语言语句的自定义列,并按所需的方式获取你的数据。You can add a custom column based on M query language statements, and get your data just the way you want it.

清理格式不规则的数据Cleaning irregularly formatted data

Power BI 可以从几乎任何来源导入数据,其可视化效果和建模工具最适用于列式数据。While Power BI can import your data from almost any source, its visualization and modeling tools work best with columnar data. 有时数据不采用简单列格式,这种情况常出现在 Excel 电子表格中,适合肉眼查看的表格布局不一定是自动查询的最优选择。Sometimes your data will not be formatted in simple columns, which is often the case with Excel spreadsheets, where a table layout that looks good to the human eye is not necessarily optimal for automated queries. 例如,以下电子表格具有跨多个列的标题。For example, the following spreadsheet has headers that span multiple columns.

幸运的是,Power BI 中的工具能将多列表格快速转化为数据集供你使用。Fortunately, Power BI has tools to quickly transform multi-column tables into datasets that you can use.

转置数据Transpose data

例如,使用查询编辑器中的转置,你可以对数据进行翻转(即将列变为行,将行变为列),从而将数据分解为可操作的格式。For example, using Transpose in Query Editor, you can flip data (turn columns to rows, and rows into columns) so you can break data down into formats that you can manipulate.

进行数次转置后,如视频所述,表格将开始转换为 Power BI 更容易处理的格式。Once you do that a few times, as described in the video, your table begins to shape into something that Power BI can more easily work with.

设置数据格式Format data

你可能还需要设置数据格式,以便 Power BI 在导入数据后对其进行适当分类和标识。You also may need to format data, so Power BI can properly categorize and identify that data once it's imported.

通过几种转换(包括 将行提升为标题 以分解标题、使用 填充null 值变为给定列中上方或下方行内找到的值,以及 逆透视列 ),即可将数据清理为可在 Power BI 中使用的数据集。With a handful of transformations, including promoting rows into headers into to break headers, using Fill to turn null values into the values found above or below in a given column, and Unpivot Columns, you can cleanse that data into a dataset that you can use in Power BI.

通过 Power BI,你可以在你的数据上对这些转换进行试验,确定可将数据转换为 Power BI 可处理的列格式的转换类型。With Power BI, you can experiment with these transformations on your data, and determine which types get your data into the columnar format that lets Power BI work with it. 请记住,你采取的所有操作都记录在“查询编辑器”中的“应用的步骤”部分中,因此如果转换未达到预期,只需单击该步骤旁的 x 撤消操作即可。And remember, all actions you take are recorded in the Applied Steps section of Query Editor, so if a transformation doesn't work the way you intended, you can simply click the x next to the step, and undo it.

创建视觉对象Create visuals

数据 Power BI 可用格式后,即可通过转换和清理数据开始创建视觉对象。Once your data is in a format that Power BI can use, by transforming and cleansing the data, you can begin to create visuals.

后续步骤Next steps

祝贺你!Congratulations! 你已经完成了本部分的 Power BI 引导学习课程。You've completed this section of the Guided Learning course for Power BI. 你现已了解如何 将数据导入 Power BI Desktop,以及如何 调整转换 这些数据,因此你可以创建具引人注目的视觉对象。You now know how to get data into Power BI Desktop, and how to shape or transform that data, so you can create compelling visuals.

了解 Power BI 的工作原理以及如何使其 为你 服务的下一步,是了解 建模 包含的内容。The next step in learning how Power BI works, and how to make it work for you, is to understand what goes into modeling. 你已经了解,数据集是 Power BI 的基本构建块,但某些数据集可能比较复杂并基于众多不同的数据源。As you learned, a dataset is a basic building block of Power BI, but some datasets can be complex and based on many different sources of data. 有时,你需要为所创建数据集添加自己的特殊亮点(或 字段 )。And sometimes, you need to add your own special touch (or field) to the dataset you create.

在下一个部分中,你将了解如何建模以及更多内容。You'll learn about modeling, and a whole lot more, in the next section. 不见不散!See you there!

恭喜!

我们继续!Way to go! 现已完成 Microsoft Power BI 引导式学习教程的“获取数据”部分。You've completed the Getting data section of Microsoft Power BI Guided Learning. 接下来请了解建模,即下一篇教程的主题。The next step is to learn about Modeling, which is the subject of the next tutorial.

你已了解操作方式...

下一个教程

建模Modeling

作者

  • Davidiseminger
  • olprod
  • Alisha-Acharya