Power BI Desktop 中的查询概述Query overview in Power BI Desktop

借助 Power BI Desktop,可以连接到世界上的数据,创建具有吸引力的基础报表,与依赖你的工作、扩展自身商业智能工作的其他人共享你的工作。With Power BI Desktop you can connect to the world of data, create compelling and foundational reports, and share your efforts with others – who can then build on your work, and expand their business intelligence efforts.

Power BI Desktop 具有三种视图:Power BI Desktop has three views:

  • 报表视图 - 此处你可使用创建的查询来构建具有吸引力的可视化效果,它们按照你所希望的方式进行排列,具有多个页面,你可与他人共享Report view – where you use queries you create to build compelling visualizations, arranged as you want them to appear, and with multiple pages, that you can share with others
  • 数据视图 - 以数据模型格式查看报表中的数据,其中你可添加度量值、创建新列和管理关系Data view – see the data in your report in data model format, where you can add measures, create new columns, and manage relationships
  • 关系视图 - 获取已在数据模型中建立的关系的图形表示,并根据需要管理和修改它们。Relationships view – get a graphical representation of the relationships that have been established in your data model, and manage or modify them as needed.

选择 Power BI Desktop 左侧三个图标之一即可获取这些视图。These views are accessed by selecting one of the three icons along the left side of Power BI Desktop. 在下图中,选择了报表视图,因为图标旁显示黄色带区。In the following image, Report view is selected, indicated by the yellow band beside the icon.

Power BI Desktop 还附带有查询编辑器,其中你可连接到一个或多个数据源,调整和转换数据以满足自己的需要,然后将该模型加载到 Power BI Desktop。Power BI Desktop also comes with Query Editor, where you can connect to one or many data sources, shape and transform the data to meet your needs, then load that model into Power BI Desktop.

本文档概述了处理查询编辑器中数据的相关内容。This document provides an overview of the work with data in the Query Editor. 当然还有更多需要了解,因此在本文档末尾提供有链接,你可了解有关受支持数据类型、连接到数据、调整数据、创建关系以及如何入门的详细指导。There's more to learn, of course, so at the end of this document you’ll find links to detailed guidance about supported data types, connecting to data, shaping data, creating relationships, and how to get started.

但首先让我们了解查询编辑器But first, let’s see get acquainted with Query Editor.

查询编辑器The Query Editor

若要访问“查询编辑器”,请从 Power BI Desktop“开始”选项卡选择“编辑查询”。To get to Query Editor, select Edit Queries from the Home tab of Power BI Desktop.

没有数据连接时,“查询编辑器”显示为空白窗格,准备处理数据。With no data connections, Query Editor appears as a blank pane, ready for data.

加载查询后,“查询编辑器”视图将变得更加有趣。Once a query is loaded, Query Editor view becomes more interesting. 如果我们连接到以下 Web 数据源,“查询编辑器”将加载有关数据的信息,随后就可以开始调整数据。If we connect to the following Web data source, Query Editor loads information about the data, which you can then begin to shape.

http://www.bankrate.com/finance/retirement/best-places-retire-how-state-ranks.aspxhttp://www.bankrate.com/finance/retirement/best-places-retire-how-state-ranks.aspx

下面是建立数据连接后“查询编辑器”的显示方式:Here’s how Query Editor appears once a data connection is established:

  1. 在功能区中,许多按钮当前处于活动状态,以与查询中的数据进行交互In the ribbon, many buttons are now active to interact with the data in the query
  2. 在左窗格中,列出了查询,可供你选择、查看和调整In the left pane, queries 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

在以下章节中,我们将了解这四个区域中的每一个 - 功能区、查询窗格、数据视图和查询设置窗格。We’ll look at each of these four areas – the ribbon, the queries pane, the data view, and the Query Settings pane – in the following sections.

查询功能区The query ribbon

“查询编辑器”中的功能区包含四个选项卡 - “开始”、“转换”、“添加列”和“视图”。The ribbon in Query Editor consists of four tabs – Home, Transform, Add Column, and View.

开始选项卡提供了常见的查询任务,包括任何查询中的第一步,即获取数据The Home tab contains the common query tasks, including the first step in any query, which is Get Data. 下图显示开始功能区。The following image shows the Home ribbon.

若要连接到数据并开始查询构建过程,请选择获取数据按钮。To connect to data and begin the query building process, select the Get Data button. 将出现一个菜单,提供最常见的数据源。A menu appears, providing the most common data sources.

有关可用数据源的详细信息,请参阅数据源For more information about available data sources, see Data Sources. 有关连接到数据的信息,包括示例和步骤,请参阅连接到数据For information about connecting to data, including examples and steps, see Connect to Data.

转换选项卡提供了对常见数据转换任务的访问,如添加或删除列、更改数据类型、拆分列和其他数据驱动任务。The Transform tab provides access to common data transformation tasks, such as adding or removing columns, changing data types, splitting columns, and other data-driven tasks. 下图显示转换选项卡。The following image shows the Transform tab.

有关转换数据的详细信息,包括示例,请参阅合并和调整数据For more information about transforming data, including examples, see Combine and Shape Data.

添加列选项卡提供了与添加列、设置列数据格式和添加自定义列相关联的其他任务。The Add Column tab provides additional tasks associated with adding a column, formatting column data, and adding custom columns. 下图显示添加列选项卡。The following image shows the Add Column tab.

功能区上的视图选项卡用于切换显示的窗格或窗口。The View tab on the ribbon is used to toggle whether certain panes or windows are displayed. 它还用于显示高级编辑器。It’s also used to display the Advanced Editor. 下图显示视图选项卡。The following image shows the View tab.

很多从功能区上进行使用的任务还可通过在中央窗格右键单击列或其他数据进行使用,知道这点很有帮助。It’s useful to know that many of the tasks available from the ribbon are also available by right-clicking a column, or other data, in the center pane.

左窗格The left pane

左窗格显示处于活动状态的查询数以及查询的名称。The left pane displays the number of active queries, as well as the name of the query. 当从左窗格选择查询时,其数据显示在中央窗格,可以在此调整并转换数据以满足需要。When you select a query from the left pane, its data is displayed in the center pane, where you can shape and transform the data to meet your needs. 下图显示含多个查询的左窗格。The following image shows the left pane with multiple queries.

中央(数据)窗格The center (data) pane

在中央窗格中,将显示已选择查询中的数据。In the center pane, or Data pane, date from the selected query is displayed. 大部分查询视图工作在此处完成。This is where much of the work of the Query view is accomplished.

下图中,会显示以前建立的 Web 数据连接,选择了总体评分列,且右键单击其标头可显示可用菜单项。In the following image, the Web data connection established earlier is displayed, the Overall score column is selected, and its header is right-clicked to show the available menu items. 请注意,其中许多用右击菜单项与功能区选项卡中的按钮相同。Notice that many of these right-click menu items are the same as buttons in the ribbon tabs.

当选择右击菜单项(或功能区按钮)时,查询将对数据应用该步骤,并将其保存为查询本身的一部分。When you select a right-click menu item (or a ribbon button), Query applies the step to the data, and saves it as part of the query itself. 这些步骤按顺序记录在查询设置窗格,如下一节中所述。The steps are recorded in the Query Settings pane in sequential order, as described in the next section.

查询设置窗格The query settings pane

查询设置窗格将显示与查询关联的所有步骤。The Query Settings pane is where all steps associated with a query are displayed. 例如,下图中,查询设置窗格的所应用步骤部分反映了我们刚刚更改了总体评分列的类型。For example, in the following image, the Applied Steps section of the Query Settings pane reflects the fact that we just changed the type of the Overall score column.

当其他调整步骤应用于该查询时,它们将在所应用步骤部分反映。As additional shaping steps are applied to the query, they are captured in the Applied Steps section.

务必了解基础数据不会更改;相反,查询编辑器调整其数据视图,并且与基础数据进行的任何交互将基于查询编辑器调整和修改的数据视图而发生。It’s important to know that the underlying data is not changed; rather, Query Editor adjusts and shapes its view of the data, and any interaction with the underlying data occurs based on Query Editor’s shaped and modified view of that data.

查询设置窗格中,可以根据你的需要重命名步骤、删除步骤,或对步骤重新排序。In the Query Settings pane, you can rename steps, delete steps, or reorder the steps as you see fit. 若要执行此操作,请右键单击所应用步骤部分中的步骤,然后从出现的菜单中选择。To do so, right-click the step in the Applied Steps section, and choose from the menu that appears. 所有查询步骤将按照“所应用步骤”窗格中显示的顺序执行。All query steps are carried out in the order they appear in the Applied Steps pane.

高级编辑器The Advanced Editor

如果想要查看查询编辑器正使用每个步骤创建的代码,或想要创建自己的调整代码,可以使用高级编辑器If you want to see the code that Query Editor is creating with each step, or want to create your own shaping code, you can use the Advanced Editor. 若要启动高级编辑器,从功能区中选择视图,然后选择高级编辑器To launch the advanced editor, select View from the ribbon, then select Advanced Editor. 此时,将会显示包含现有查询代码的窗口。A window appears, showing the existing query code.

可以直接编辑高级编辑器 窗口中的代码。You can directly edit the code in the Advanced Editor window. 若要关闭该窗口,请选择完成取消按钮。To close the window, select the Done or Cancel button.

保存工作Saving your work

当查询位于你希望的位置时,可使用查询编辑器将对数据模型的更改应用到 Power BI Desktop,并关闭查询编辑器。When your query is where you want it, you can have Query Editor apply the changes to the data model into Power BI Desktop, and close Query Editor. 若要执行此操作,请从查询编辑器的文件菜单选择关闭并应用To do that, select Close & Apply from Query Editor's File menu.

随着进度推进,Power BI Desktop 提供了一个对话框显示其状态。As progress is made, Power BI Desktop provides a dialog to display its status.

一旦查询位于你希望的位置,或者如果只是想确保工作是否保存,Power BI Desktop 可将你的工作保存为 .pbix 文件格式。Once you have your query where you want it, or if you just want to make sure your work is saved, Power BI Desktop can save your work in the form of a .pbix file.

若要保存工作,请选择文件>保存(或文件>另存为),如下图所示。To save your work, select File > Save (or File > Save As), as shown in the following image.

后续步骤Next steps

Power BI Desktop 可用于执行多种操作。There are all sorts of things you can do with Power BI Desktop. 有关其功能的详细信息,请参阅下列资源:For more information on its capabilities, check out the following resources: