Power BI Desktop 中的常见查询任务Common query tasks in Power BI Desktop

使用 Power BI Desktop 的查询编辑器窗口时,可执行很多常用任务。When working in the Query Editor window of Power BI Desktop, there are a handful of commonly used tasks. 本文档演示了这些常见的任务,并提供其他信息的链接。This document demonstrates those common tasks, and provides links for additional information.

本文展示了下列常见查询任务:The common query tasks demonstrated here are the following:

  • 连接到数据Connect to data
  • 调整和合并数据Shape and combine data
  • 行分组Group rows
  • 列透视Pivot columns
  • 创建自定义列Create custom columns
  • 查询公式Query formulas

我们将使用几个数据连接来完成这些任务。We’ll use a few data connections to complete these tasks. 如果你想要自己逐步完成这些任务,可下载或连接到数据。The data is available for you to download or connect to, in case you want to step through these tasks yourself.

第一个数据连接是 Excel 工作簿。The first data connection is an Excel workbook. 另一个是可从此处进行访问的 Web 资源(还用于其他 Power BI Desktop 帮助内容):The other is a Web resource (which is also used in other Power BI Desktop help content) which can be accessed from here:


常见查询任务首先就是连接到这两个数据源所必需的步骤。The steps necessary to connect to both of those data sources is where the common Query tasks begin.

连接到数据Connect to data

若要连接到 Power BI Desktop 中的数据,请从功能区上的开始选项卡选择获取数据按钮。To connect to data in Power BI Desktop, select the Get Data button from the Home tab on the ribbon. Power BI Desktop 将显示一个含有最常见数据源的菜单。Power BI Desktop presents a menu with the most common data sources. 对于 Power BI Desktop 可连接到的数据源的完整列表,请选择菜单底部的更多...按钮。For a complete list of data sources to which Power BI Desktop can connect, select the More... button at the bottom of the menu. 有关详细信息,请参阅 Power BI Desktop 中的数据源For more information, see Data Sources in Power BI Desktop.

首先,选择 Excel 并导航到该工作簿,然后将其选中。To start with, select Excel and navigate to the workbook, then select it. 查询将检查该工作簿,然后呈现在导航器窗口中找到的数据。Query inspects the workbook, then presents the data it found in the Navigator window.

在将数据加载到 Power BI Desktop 中之前,可选择 编辑调整 来调整数据。You can select Edit to adjust, or shape, the data before loading it into Power BI Desktop. 在处理想要在加载前进行削减的大型数据集时,先编辑查询再加载尤其有用。Editing a query before loading is especially useful when working with large data sets that you intend to pare down before loading. 我们想要执行此操作,因此选择编辑We want to do that, so we select Edit.

连接到不同类型的数据同样简单。Connecting to different types of data is just as easy. 我们还想要连接到 Web 资源。We also want to connect to a Web resource. 选择获取数据 >更多...,然后选择其他 > WebSelect Get Data > More... and then select Other > Web.

自网站窗口随即出现,可在其中键入网页的 URL。The From Web window appears, where you can type in the URL of the Web page.

选择确定;和以前一样,Power BI Desktop 将检查工作簿,并呈现其在导航器窗口中找到的数据。Select OK, and like before, Power BI Desktop inspects the workbook and presents the data it finds in the Navigator window.

其他数据连接与此类似。Other data connections are similar. 如果需要身份验证才能建立数据连接,Power BI Desktop 将提示你提供相应凭据。If authentication is required to make a data connection, Power BI Desktop prompts you for the appropriate credentials.

有关连接到 Power BI Desktop 中数据的分步演示,请参阅连接到 Power BI Desktop 中的数据For a step-by-step demonstration of connecting to data in Power BI Desktop, see Connect to Data in Power BI Desktop.

调整和合并数据Shape and combine data

可使用查询编辑器轻松地调整和合并数据。You can easily shape and combine data with Query Editor. 本部分包括几个有关数据调整方式的示例。This section includes a few examples of how you can shape data. 有关如何调整和合并数据的更完整演示,请参阅使用 Power BI Desktop 调整和合并数据For a more complete demonstration of shaping and combining data, see Shape and Combine Data with Power BI Desktop.

上一节中,我们连接到两组数据 – Excel 工作簿和 Web 资源。In the previous section we connected to two sets of data – an Excel workbook, and a Web resource. 在查询编辑器中加载后,我们将看到以下结果,其中选中了网页中的查询(位于查询编辑器窗口左侧,查询窗格内列出的可用查询中)。Once loaded in Query Editor we see the following, with the query from the Web page selected (from the available queries listed in the Queries pane, on the left side of the Query Editor window).

在调整数据时,可将数据源转换为满足你需求的形式和格式。When you shape data, you transform a data source into the form and format that meets your needs. 此情况下不需要名为 标题 的第一列,因此将其删除。In this case, we don’t need that first column, titled Header, so we’ll remove it.

查询编辑器中,可在功能区和上下文相关的右键单击菜单中找到许多命令。In Query Editor, many commands can be found in the ribbon, and in a context-sensitive right-click menu. 例如,在右键单击 标题 列时,显示的菜单将允许删除此列。For example, when I right-click on the Header column, the menu that appears lets me remove the column. 还可选中此列,然后选择功能区中的删除列按钮。I could also select the column and then select the Remove Columns button from the ribbon.

还有多种方式可用于调整此查询中的数据;可从顶部或底部删除任意数量的行;可添加列、拆分列、替换值,并执行其他调整任务以指示查询编辑器按所需方式获取数据。There are many other ways I could shape the data in this query; I could remove any number of rows from the top, or from the bottom; I could add columns, split columns, replace values, and perform other shaping tasks to direct Query Editor to get the data how I want it.

行分组Group rows

在查询编辑器中,可将多个行中的值聚集为单个值。In Query Editor, you can group the values in multiple rows into a single value. 在汇总所提供的产品数、总销售额或学生计数时,这会很有用。This can be useful when summarizing the number of products offered, the total sales, or the count of students.

在此示例中,我们对教育注册数据集中的行进行分组。In this example, we group rows in an education enrollment data set. 数据来自 Excel 工作簿,并已在查询编辑器中进行调整以仅获取所需的列、重命名表格并执行一些其他转换。The data is from an Excel workbook, and has been shaped in Query Editor to get just the columns we need, renamed the table, and performed a few other transforms.

我们来了解一下每个州有多少机构(这包括学区和其他教育机构,如区域服务学区等)。Let’s find out how many Agencies (this includes school districts, and other education agencies such as regional service districts, and so on) each state has. 选择 州缩写 列,然后选择 转换 选项卡中的 分组依据 按钮或功能区的 开始 选项卡(这两个选项卡中都有 分组依据 )。We select the State Abbr column then select the Group By button in the Transform tab or the Home tab of the ribbon (Group By is available in both tabs).

分组依据…The Group By… 窗口随即出现。window appears. 当查询编辑器对行进行分组时,它会创建一个新列,将分组依据结果置于其中。When Query Editor groups rows, it creates a new column into which it places the Group By results. 可按照以下方式调整分组依据操作:You can adjust the Group By operation in the following ways:

  1. 分组依据 – 这是要进行分组的列;查询编辑器将选择所选列,但可在此窗口中将其更改为表中的任意列。Group by – this is the column to be grouped; Query Editor chooses the selected column, but you can change that in this window to any column in the table.
  2. 新列名 – 查询编辑器基于它对要进行分组的列所应用的操作,为新列建议一个名称;但也可将新列命名为所需的任何名称。New column name – Query Editor suggests a name for the new column, based on the operation it applies to the column being grouped, but you can name the new column anything you want.
  3. 操作 – 在此处指定查询编辑器将应用的操作。Operation – here you specify the operation that Query Editor applies.
  4. +/- 符号 – 可对多个列执行聚合运算(分组依据操作)和执行多个聚合,全在分组依据窗口中一次性完成。The +/- signs – you can perform aggregation operations (Group By actions) on multiple columns, and perform multiple aggregations, all within the Group By window, and all in one operation. 查询编辑器将创建在多个列上操作的新列(基于此窗口内的所选内容)。Query Editor creates a new column (based on your selections in this window) that operate on multiple columns. 选择+ 按钮以向分组依据操作添加更多列或聚合。Select the + button to add more columns or aggregations to a Group By operation. 可选择“–”图标来删除列或聚合,因此继续尝试操作一下,看看它如何显示。You can remove a column or aggregation by selecting the – icon, so go ahead and try it, and see what it looks like.

在选择确定时,查询将执行分组依据操作并返回结果。When we select OK, Query performs the Group By operation, and returns the results. 哟,看看这个 – 俄亥俄州、德克萨斯州、伊利诺伊州和加利福尼亚州,每个都有 1,000 多个机构!Whew, look at that – Ohio, Texas, Illinois, and California each have over a thousand agencies!

此外,借助查询编辑器,可通过选择刚刚完成的步骤旁边的 X 随时删除最后一次调整操作。And with Query Editor, you can always remove the last shaping operation by selecting the X next to the step just completed. 因此前去试一下,如果结果不合你意,可恢复此步骤,直到查询编辑器恰好按所需的方式调整数据。So go ahead and experiment, redo the step if you don’t like the results, until Query Editor shapes your data just the way you want it.

列透视Pivot columns

通过 Power BI Desktop,可对列进行透视,并创建包含某列中每个唯一值的聚合值的表格。With Power BI Desktop, you can pivot columns, and create a table that contains aggregated values for each unique value in a column. 例如,如果需要知道在每个产品类别中具有多少种不同的产品,可快速创建一个表来精确执行此操作。For example, if you need to know how many different products you have in each product category, you can quickly create a table the does precisely that.

我们来看一个示例。Let’s look at an example. 以下产品表已调整为仅显示每个唯一产品(按名称)以及每种产品所属的类别。The following Products table has been shaped to only show each unique product (by name), and which category each product falls under. 若要新建一个表格来显示每个类别的产品计数(基于 CategoryName 列),请选中该列,然后在功能区上选择转换选项卡的透视列To create a new table that shows a count of products for each category (based on the CategoryName column), select the column, then select Pivot Column from the Transform tab on the ribbon.

透视列窗口随即出现,显示哪一列的值将被用于创建新列 (1),并且在展开高级选项 (2) 时,可选择将应用于聚合值 (3) 的函数。The Pivot Column window appears, letting you know which column’s values will be used to create new columns (1), and when you expand Advanced option (2), you can select the function that will be applied to the aggregated values (3).

当选择确定时,查询将根据透视列窗口中提供的转换说明显示表。When you select OK, Query displays the table according to the transform instructions provided in the Pivot Column window.

创建自定义列Create custom columns

在查询编辑器中,可创建对表中多个列进行操作的自定义公式,然后将此类公式的计算结果放入新的(自定义)列中。In Query Editor you can create custom formulas that operate on multiple columns in your table, then place the results of such formulas into a new (custom) column. 查询编辑器可轻松创建自定义列。Query Editor makes it easy to create custom columns.

在查询编辑器的功能区上,选择添加列选项卡的添加自定义列In Query Editor, select Add Custom Column from the Add Column tab on the ribbon.

将显示以下窗口。The following window appears. 在下例中,我们创建名为 Percent ELL 的自定义列,该列计算为英语学习者 (ELL) 的学生总数的百分比。In the following example, we create a custom column called Percent ELL that calculates the percentage of total students that are English Language Learners (ELL).

如同查询编辑器中应用的任何其他步骤一样,如果新的自定义列不提供你要查找的数据,则只需通过选择已添加自定义步骤旁边的 X,从查询设置窗格的所应用步骤部分中删除该步骤。Like any other applied step in Query Editor, if the new custom column doesn’t provide the data you’re looking for, you can simply delete the step from the Applied Steps section of the Query Settings pane by selecting the X next to the Added Custom step.

查询公式Query formulas

可编辑查询编辑器生成的步骤,还可创建自定义公式,从而精确地控制到数据的连接和调整操作。You can edit the steps that Query Editor generates, and you can create custom formulas to get precise control over connecting to and shaping your data. 每当查询编辑器对数据执行操作时,公式栏中都会显示与操作关联的公式。Whenever Query Editor performs an action on data, the formula associated with the action is displayed in the Formula Bar. 若要查看公式栏,请在功能区的查看选项卡中选择公式栏旁边的复选框。To view the Formula Bar, select the checkbox next to Formula Bar in the View tab of the ribbon.

查询编辑器将每个查询的所有已应用步骤保存为可查看或修改的文本。Query Editor keeps all applied steps for each query as text that you can view or modify. 可使用高级编辑器查看或修改任何查询的文本;在功能区的查看选项卡中选择高级编辑器时,即会显示该编辑器。You can view or modify the text for any query using the Advanced Editor, which is displayed when you select Advanced Editor from the View tab of the ribbon.

下面来看看高级编辑器,它显示了与 USA_StudentEnrollment 查询关联的查询步骤。Here's a look at the Advanced Editor, with the query steps associated with the USA_StudentEnrollment query displayed. 这些步骤是使用 Power Query 公式语言(通常称为 M)进行创建的。相关信息,请参阅了解 Power 查询公式These steps are created in the Power Query Formula Language, often referred to as M. For information, see Learn about Power Query formulas. 若要查看语言规范本身,请下载 Microsoft Power Query for Excel 公式语言规范To view the language specification itself, download the Microsoft Power Query for Excel Formula Language Specification.

Power BI Desktop 提供了一组全面的公式类别。Power BI Desktop provides an extensive set of formula categories. 有关详细信息和所有查询编辑器公式的完整参考,请访问 Power Query 公式类别For more information, and a complete reference of all Query Editor formulas, visit Power Query Formula Categories.

查询编辑器的公式类别如下所示:The formula categories for Query Editor are the following:

  • 数字Number
    • 常量Constants
    • 信息Information
    • 转换和格式设置Conversion and formatting
    • 格式Format
    • 舍入Rounding
    • 运算Operations
    • 随机Random
    • 三角函数Trigonometry
    • 字节数Bytes
  • 文本Text
    • 信息Information
    • 文本比较Text comparisons
    • 提取Extraction
    • 修改Modification
    • 成员资格Membership
    • 转换Transformations
  • 逻辑Logical
  • 日期Date
  • 时间Time
  • 日期时间DateTime
  • 时区DateTimeZone
  • 持续时间Duration
  • 记录Record
    • 信息Information
    • 转换Transformations
    • 所选内容Selection
    • 序列化Serialization
  • 列表List
    • 信息Information
    • 所选内容Selection
    • 转换Transformation
    • 成员资格Membership
    • Set 运算Set operations
    • 排序Ordering
    • 平均值Averages
    • 相加Addition
    • 数值Numerics
    • 生成器Generators
  • Table
    • 构造表Table construction
    • 转换Conversions
    • 信息Information
    • 行操作Row operations
    • 列操作Column operations
    • 成员资格Membership
  • Values
  • 算术运算Arithmetic operations
  • 参数类型Parameter Types
  • 元数据Metadata
  • 访问数据Accessing data
  • 二进制格式Binary formats
    • 读取数字Reading numbers
  • 二进制Binary
  • Lines
  • 表达式Expression
  • 函数Function
  • 错误Error
  • 比较器Comparer
  • 拆分器Splitter
  • 组合器Combiner
  • 替换器Replacer
  • 类型Type

后续步骤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: