在 Power BI Desktop 中從範例新增資料行Add a column from an example in Power BI Desktop

Power BI Desktop 的 2017 年 4 月版本起,您只要為新資料行提供一或個範例值,就可以使用查詢編輯器將資料的新資料行新增到模型。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 中使用 Custom Column 運算式所需要的轉換,但其中一個 (或多個) 運算式在 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.

在本文中,我們使用來自下列 Wikipedia 文章的資料 (您可以按一下這個連結,自行取得資料並跟著進行):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). 新的 Column1 也會顯示在右側,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. 在下方,新的 Column1 標頭為空白儲存格,您可以在其中鍵入範例,讓 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 值,在第一列鍵入 AlabamaFor 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
  • Power BI Desktop 的 2017 年 11 月版本開始支援下列文字轉換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
    • CubeCube
    • 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
  • Power BI Desktop 2017 年 11 月版本開始支援下列數字轉換The following supported number transformation is available beginning with the November 2017 release of Power BI Desktop:

    • 建立貯體/範圍Bucketing/Ranges
  • 一般General

    • 條件資料行Conditional Column