使用自訂函式

如果你遇到需要對不同查詢或值套用相同轉換組合的情況，建立一個可以無限次重複使用的 Power Query 自訂函式會很有幫助。 Power Query 自訂函式是將一組輸入值映射到單一輸出值，並由原生的 M 函數與運算子建立。

你可以手動使用 Power Query M 公式語言手動建立自己的Power Query自訂函式，或是Power Query的使用者介面提供加速、簡化並強化自訂函式創建與管理流程的功能。

首先，我們將討論 在UI中使用程式代碼建立自定義函式的基本步驟，然後我們將著重於使用介面 將複雜的動作轉換成可重複使用的函式。

重要

本文說明如何利用Power Query使用者介面中常見的變換，建立帶有Power Query的自訂函式。 它著重於建立自訂函式的核心概念，並附有連結至Power Query文件中其他文章，以獲得本文所引用的特定轉換的更多資訊。

從 UI 中的程式代碼建立自訂函式

注意

以下步驟可在 Power BI Desktop 或使用 Excel for Windows 中的 Power Query 體驗中完成。

1. 使用連接器體驗來連線到數據所在的位置。 選取數據後，請選取 [轉換數據] 按鈕或 [編輯] 按鈕。 這會帶你進入 Power Query 體驗。

2. 以滑鼠右鍵點擊左側 查詢 窗格中的空白位置。

3. 選擇 [空白查詢]。

4. 在新的空白查詢視窗中，選取 [首頁] 功能表，然後 [進階編輯器]。

5. 將模板替換為您的自訂函式。 例如：

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. 選擇 完成。

欲了解更多使用 Power Query M 公式語言開發自訂函式的資訊，請參閱本文：Understanding Power Query M Functions。 接下來的章節有教學，說明如何利用 Power Query 使用者介面開發自訂函式而無需寫程式碼，並說明如何在查詢中調用自訂函式。

從表格參考的教學中建立自訂函式

注意

以下範例是利用 Power BI Desktop 中的桌面體驗建立的，也可以透過 Excel for Windows 中的 Power Query 體驗來操作。

您可以從下列 下載連結下載本文中使用的範例檔案，並遵循此範例。 為了簡單起見，本文使用資料夾連接器。 若要深入瞭解資料夾連接器，請移至 資料夾。 此範例的目標是建立可套用至該資料夾中所有檔案的自定義函式，再將所有檔案中的所有資料合併成單一數據表。

從使用資料夾連接器體驗開始，瀏覽至檔案所在的資料夾，然後選取 [轉換資料] 或 [編輯]。 這些步驟將帶你進入 Power Query 的體驗。 在 [內容] 字段中，以滑鼠右鍵按下您選擇的 [Binary] 值，然後選取 [新增為新查詢] 選項。 在此範例中，已針對清單中的第一個檔案進行選取，這恰好是檔案 4 月 2019.csv。

此選項可有效地建立新的查詢，並將導覽步驟直接指向該檔案做為二進位檔，而這個新查詢的名稱是所選檔案的檔案路徑。 將此查詢重新命名為 範例檔案。

建立一個名稱為 File Parameter 且類型為 Binary的新參數。 使用 範例檔案 查詢作為 預設值，目前值。

注意

我們建議您閱讀 Using parameters 的文章，以更好地了解如何在Power Query中建立和管理參數。

您可以使用任何參數類型來建立自定義函式。 任何自定義函式都不需要有二進位做為參數。

當您有評估為二進位的查詢時，二進位參數類型只會顯示在 [參數] 對話框 [類型] 下拉功能表中。

不需參數即可建立自定義函式。 在可從叫用函式的環境推斷輸入的情況中，通常會看到這種情況。 例如，使用環境目前的日期和時間的函式，從這些值建立特定的文字字串。

在 查詢 窗格中，以滑鼠右鍵單擊 檔案參數。 選取 [參考] 選項。

將新建立的查詢從 File Parameter （2） 重新命名為 轉換範例檔案。

以滑鼠右鍵按一下這個新的 轉換範例檔案 查詢，然後選取 建立函式 選項。

這項作業會有效地建立新的函式，與 轉換範例檔案 查詢連結。 您對 轉換範例檔案 查詢所做的任何變更都會自動複製到您的自訂函式。 在創建這個新功能時，請使用 轉換檔案 作為功能名稱 。

建立函式之後，請注意，系統會使用函式的名稱為您建立新的群組。 這個新群組包含：

• 轉換範例檔案 查詢中參考的所有參數。
• 您的 轉換範例檔案 查詢，通常稱為 範例查詢。
• 您新建立的函式，在此案例中 轉換檔案。

將轉換套用至範例查詢

建立新的函式之後，選取名稱為 的查詢轉換範例檔案。 此查詢現在會與 轉換檔案 函式連結，因此對此查詢所做的任何變更會反映在函式中。 此連接即所謂的範例查詢與函式相關的概念。

此查詢需要發生的第一個轉換是解譯二進位檔的轉換。 您可以從預覽窗格中以滑鼠右鍵按兩下二進位檔，然後選取 [CSV] 選項，將二進位檔解譯為 CSV 檔案。

資料夾中所有 CSV 檔案的格式都相同。 它們都有橫跨前四行的標頭。 欄位標頭位於第 5 列，資料從第 6 列開始往下看，如下張圖片所示。

需要套用至 轉換範例檔案 的下一組轉換步驟如下：

1. 移除前四個數據列— 此動作會移除視為檔案標頭區段一部分的數據列。

   

   注意

   想了解更多如何移除列或依列位置篩選表格，請前往 「以列位置篩選表格」。

2. 升級標頭— 最終數據表的標頭現在位於數據表的第一列中。 您可以推廣它們，如下一個圖像所示。

   

升階欄位標頭後，Power Query 預設會自動新增一個新的 Changed Type 步驟，自動偵測每欄的資料類型。 您的 轉換範例檔案 查詢看起來會像下一個影像。

注意

若要深入了解如何提高或降低欄位標頭，請移至 提高或降低欄位標頭。

謹慎

您的 轉換檔案 函式仰賴 轉換範例檔案 查詢中執行的步驟。 不過，如果您嘗試手動修改 轉換文件的 函式程式碼，您將會收到顯示 The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'. 的警告。

調用自定義函式作為新欄位

現在已建立自定義函式並整合所有轉換步驟，您可以回到原始查詢，其中包括資料夾的檔案清單（此範例中為CSV 檔案）。 在功能區的 [新增欄] 索引標籤內，從 [一般] 群組中選取 [呼叫自定義函數]。 在 [叫用自定義函式] 視窗中，輸入 [輸出數據表] 做為 [新增數據行名稱] 。 從函式查詢下拉選單中選擇你的函式名稱Transform 檔案。 從下拉功能表中選取函式之後，就會顯示函式的參數，而且您可以從數據表中選取要用來作為此函式自變數的數據行。 選擇「Content」欄作為傳遞給 File Parameter的值或參數。

在選取 [確定]之後，會建立一個名稱為 Output Table 的新列。 此欄的儲存格中包含 Table 的值，如下圖所示。 為了簡單起見，除了 Name 和 Output Table以外，請移除此數據表中的所有數據行。

注意

若要深入瞭解如何從資料表選擇或移除資料列，請移至 選擇或移除資料列。

您的函式會使用來自 Content 數據行的值作為函式的自變數，套用至數據表中的每個單一數據列。 既然數據已轉換成您所尋找的形狀，您可以透過選取 [展開] 圖示來展開 [輸出表格] 列 欄。 請勿針對展開的數據行使用任何前置詞。

您可以檢查 [名稱] 或 [Date] 資料行中的值，以確認您有資料夾中所有檔案的數據。 在此情況下，您可以檢查日期 數據行 的值，因為每個檔案只包含指定年份的單一月份數據。 如果您看到多個檔案，這表示您已成功將數據從多個檔案合併成單一數據表。

注意

到目前為止您所看到的過程基本上與 合併檔案 體驗中所發生的過程相同，但這是手動完成的。

我們建議您也閱讀 Combine 檔案概覽 及 Combine CSV 檔案，以進一步了解 Power Query 中的 combine 檔案體驗及自訂函式所扮演的角色。

將新的參數新增至現有的自定義函式

假設在您目前建置的專案上面有新的需求。 新的需求要求，在合併檔案之前，您會篩選其中的數據，只取得 國家/地區 等於 巴拿馬的數據列。

若要進行這項需求，請使用文字數據類型建立名為 Market 的新參數。 針對 目前值，輸入值 巴拿馬。

使用這個新參數，選取 [轉換範本檔案] 查詢，並使用 Market 參數的值來篩選 Country 欄位。

注意

想了解更多如何依欄位值篩選，請前往 「以欄位中的值篩選」。

將這個新步驟套用至您的查詢會自動更新轉換檔案 函式，此函式現在會根據 轉換範例檔案 使用的兩個參數來要求兩個參數。

但是 CSV 檔案 查詢旁邊有警告符號。 現在您的函式已更新，它需要兩個參數。 因此，調用函式的步驟會產生錯誤值，因為在 調用自定義函式 步驟期間，只有其中一個參數傳遞至 轉換檔案 函式。

若要修正錯誤，請在 [套用的步驟] 中按兩下 叫用自定義函式，以開啟 [叫用自定義函式] 視窗。 在 Market 參數中，手動輸入 值 巴拿馬。

您現在可以返回 套用步驟中的 展開輸出數據表。 請檢查您的查詢，以確認只有 國家/地區 等於 巴拿馬 的數據列才會顯示在 CSV 檔案 查詢的最終結果集中。

從可重複使用的邏輯片段建立自定義函式

如果您有多個需要相同轉換集的查詢或值，您可以建立自定義函式，做為可重複使用的邏輯片段。 稍後，您可以針對您選擇的查詢或值叫用此自定義函式。 此自定義函式可以節省您的時間，並協助您在中央位置管理一組轉換，您可以隨時加以修改。

例如，假設有數個程式代碼作為文字字串的查詢，而您想要建立可解碼這些值的函式，如下列範例表所示：

字碼
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

首先，您有一個參數，該參數的值可以作為例子。 在此情況下，它的值是 PTY-CM1090-LAX。

從該參數，您會建立新的查詢，以套用所需的轉換。 在此情況下，您想要將程式代碼 PTY-CM1090-LAX 分割成多個元件：

• 原點 = PTY
• 目的地 = LAX
• 航空公司 = CM
• 飛行ID = 1090

下列 M 程式代碼示範該組轉換。

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

注意

想了解更多關於 Power Query M 公式語言的資訊，請前往 Power Query M 公式語言。

接著，您可以藉由在查詢上按下滑鼠右鍵，然後選取 [建立函式]來將該查詢轉換成函式。 最後，您可以將自定義函式叫用至任何查詢或值，如下圖所示。

在經過一些額外的轉換之後，您可以看到已達到所需的輸出，並透過自定義函式應用了這類轉換的邏輯。