在 Power BI Desktop 中通过示例添加列Add a column from an example in Power BI Desktop

自 2017 年 4 月发布的 Power BI Desktop 起,只需提供新列的一个或多个示例值,即可使用查询编辑器向模型添加新的数据列。Starting with the April 2017 release of Power BI Desktop, you can add new columns of data to your model using Query Editor by simply providing one or more sample values for your new column. 可以根据当前所选内容,或通过根据给定表中的所有(或选定)列提供输入数据,创建新列示例。You can create a new column example from a current selection, or from providing input based on all (or selected) columns in a given table.

这种方法有助于轻松快速地创建新列,非常适用于以下情形:This approach helps you quickly and easily create new columns, and is great for the following situations:

  • 你知道自己想要在新列中获得的数据结果,但不确定通过哪种转换(或一系列转换)可以实现目的。You know the data result you want in your new column, but you're not sure which transformation (or collection of transformations) will get you there.
  • 你已知道自己需要执行的转换,但不确定在 UI 中的什么位置进行单击或选择,从而执行这些转换。You already know which transformations you need, but you're not sure where to click or select in the UI to make them happen.
  • 你知道在“M”中使用“自定义列”表达式所需的转换的全部信息,但无法在 UI 中单击或添加其中一个(或多个)表达式。You know all about the transformations you need using a Custom Column expression in M, but one (or more) of those expressions aren't available to click or add in the UI.

通过示例添加列功能使用起来非常简单。Using the add column from example feature is easy and straightforward. 在接下来的几个部分中,我们就来看看如何轻松地使用这项功能。In the next few sections, we see just how easy it is.

使用查询编辑器通过示例添加新列Use Query Editor to add a new column from examples

若要通过示例创建新列,请启动“查询编辑器”。To create a new column from an example, launch Query Editor. 为此,可以在 Power BI Desktop 的“开始”功能区中选择“编辑查询”。You can do this by selecting Edit Queries from the Home ribbon in Power BI Desktop.

在本文中,我们将使用以下维基百科文章中的数据(可以单击下面的链接,自行获取数据,然后跟着我们一起操作):In this article, we'll use data from the following Wikipedia article (it's a link, so you can click it to get the data for yourself and follow along):

启动“查询编辑器”且已加载部分数据后,便可以开始通过示例添加列。Once Query Editor is launched and you have some data loaded, you can get started adding a column from examples. 若要添加新列,请在“查询编辑器”中依次选择功能区上的“添加列”选项卡和“通过示例添加列”。To add a new column, in Query Editor select the Add Column tab on the ribbon and select Column from Examples. 可以选择下拉列表中的“通过所有列”(如果只选择了按钮,而未选择下拉列表,此为默认值)或“通过所选内容”。If you choose the drop-down, you can select either From All Columns (the default, if you just select the button instead of the drop-down) or select From Selection. 在本文中,我们将一步步演示选择“通过所有列”的相关操作。In this article, we'll walk through selecting From All Columns.

“通过示例添加列”窗格The Add Column From Examples pane

选择通过示例添加新列后,便会看到一个新窗格,其中显示了当前表中的列(可能需要滚动才能查看全部列)。Once you make a selection to add a new column from examples, a new pane appears that shows the columns in the current table (you may need to scroll to see them all). 右侧还会显示新的“第 1 列”,这就是 Power BI Desktop 根据示例创建的列。The new Column1 is also shown to the right, which is the column that Power BI Desktop will create based on your examples. 新的“第 1 列”标题下方是空白单元格,可以在其中键入示例,以便 Power BI 能够用来创建与示例匹配的规则和转换。Below the new Column1 header are blank cells, where you can type in your examples that Power BI uses to create rules and transformations to match your example.

另请注意,这是“查询设置”窗格中的“已应用步骤”。Notice too that this is an Applied Step in the Query Settings pane. 和以往一样,查询编辑器会记录转换步骤,并依序向查询应用这些步骤。As always, Query Editor will record your transformation steps and apply them to the query, in order.

这就是“通过示例添加列”窗格,主要分为以下四个区域:This is called the Add Columns From Examples pane, and it consists of four primary areas:

  1. 包含功能或转换的简要说明的“命令栏”。The Command bar which includes a brief description of the feature, or the transformation.
  2. 有助于 Power BI 改进此功能的“发送反馈”选项。The Send Feedback option to help Power BI improve this feature.
  3. 用于提交转换并添加列或取消上述操作的“确定”和“取消”按钮。The OK and Cancel buttons, which lets you commit your transformations and add the column, or cancel.
  4. 新列区域,可以在任意一行中键入示例值(向 Power BI 提供示例),从而与相应行的其他列相关联。The new column area, where you can type your sample values in any of the rows (to provide Power BI with your example), relating to other columns in that row.

在新列中键入示例时,Power BI 会根据检测到的转换让你预览要创建的列。As you type your example in the new column, Power BI gives you a preview of how the column it's creating will appear, based on the transformations it detects. 例如,我们在第一行中键入了“Alabama”,对应于表中第一列的“Alabama”值。For example, we typed Alabama in the first row, corresponding to the Alabama value in the first column of the table. Enter 键后,Power BI 便会立即根据此值填充列。As soon as we hit Enter Power BI fills in the column based on that value.

不过,接着我们转到包含“Massachusetts[E]”的行,并删除了最后的“[E]”部分(因为我们不需要这一部分),Power BI 检测到了这一更改,并使用示例创建了转换。But then we went to the row that included Massachusetts[E] and deleted that last [E] portion (because we didn't want it) and Power BI detected the change, and used the example to create a transformation. 请注意,窗格的中上方对转换进行了说明。Notice the explanation of the transform in the upper middle pane.

如果继续提供示例,查询编辑器会将其添加到转换中。As you continue to provide examples, Query Editor adds to the transformations. 如果感到满意,可以选择“确定”提交更改。When you're satisfied, you can select OK to commit your changes.

观看“通过示例添加列”的实际操作See Add Column from Examples in action

想看看此功能的实际效果?Want to see this working? 下面的视频展示了这项功能的实际运用(使用此示例前面提供的数据源)。The following video shows this feature being put to use, using the data source provided earlier in this example. 请仔细观看,自己跟着视频操作一下!Take a look, and follow along for yourself!

注意事项和限制Considerations and limitations

虽然使用“通过示例添加列”时有许多转换可用,但并不是所有转换都包含在内。There are many transformations that are available when using Add column from Examples, but not every transformation is included. 下面列出了支持的全部转换。The following list provides all the transformations that are supported.

  • 引用Reference

    • 引用特定列(包含修整、清理和大小写转换)Reference to a specific column (including trim, clean, and case transformations)
  • 文本转换Text transformations

    • 合并(支持合并文本字符串和整个列值)Combine (supports combination of literal strings and entire column values)
    • 替换Replace
    • 长度Length
    • 提取Extract
      • 第一个字符First Characters
      • 最后一个字符Last Characters
      • 范围Range
      • 分隔符前的文本Text before Delimiter
      • 分隔符后的文本Text after Delimiter
      • 分隔符之间的文本Text between Delimiters
      • 长度Length
  • 自 2017 年 11 月版 Power BI Desktop 起,支持下列文本转换:The following supported text transformations are available beginning with the November 2017 release of Power BI Desktop:

    • 删除字符Remove Characters
    • 保留字符Keep Characters


对于所有文本转换,都要考虑是否需要进行修整、清理或对列值应用大小写转换。All Text transformations take into account the potential need to trim, clean, or apply a case transformation to the column value.

  • 日期转换Date transformations

    • Day
    • 每周的某一日Day of Week
    • 周几名称Day of Week Name
    • 每年的某一日Day of Year
    • 月份Month
    • 月份名称Month Name
    • 每年的某一季度Quarter of Year
    • 每月的某一周Week of Month
    • 每年的某一周Week of Year
    • 年份Year
    • 年限Age
    • 年份开始值Start of Year
    • 年份结束值End of Year
    • 月份开始值Start of Month
    • 月份结束值End of Month
    • 季度开始值Start of Quarter
    • 一个月的某些日Days in Month
    • 季度结束值End of Quarter
    • 星期开始值Start of Week
    • 星期结束值End of Week
    • 每月的某一日Day of Month
    • 一天开始值Start of Day
    • 一天结束值End of Day
  • 时间转换Time transformations

    • 小时Hour
    • 分钟Minute
    • Second
    • 本地时间To Local Time


对于所有“日期”和“时间”转换,都要考虑是否可能需要将列值转换成“日期”或“时间”或“日期和时间”。All Date and Time transformations take into account the potential need to convert the column value to Date or Time or DateTime.

  • 数字转换Number transformations

    • 绝对值Absolute Value
    • 反余弦Arccosine
    • 反正弦Arcsine
    • 反正切Arctangent
    • 转换为数字Convert to Number
    • 余弦Cosine
    • 多维数据集Cube
    • Divide
    • 求幂Exponent
    • 阶乘Factorial
    • 整除Integer Divide
    • 为偶数Is Even
    • 为奇数Is Odd
    • 自然对数Ln
    • 以 10 为底数的对数Base-10 Logarithm
    • 取模Modulo
    • Multiply
    • 向下舍入Round Down
    • 向上舍入Round Up
    • 符号Sign
    • 正弦Sin
    • 平方根Square Root
    • 平方Square
    • Subtract
    • 求和Sum
    • 正切Tangent
  • 自 2017 年 11 月版 Power BI Desktop 起,支持下列数字转换:The following supported number transformation is available beginning with the November 2017 release of Power BI Desktop:

    • Bucket/范围Bucketing/Ranges
  • 常规General

    • 条件列Conditional Column