使用自訂函式

如果您需要將相同的轉換集應用到不同的查詢或值，建立一個可以重複使用的 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 公式語言開發自定義函式的詳細資訊，請參閱這篇文章：瞭解 Power Query M 函式。 在下列各節中，有一些教學課程說明如何使用Power Query使用者介面來開發自定義函式，而不需撰寫程序代碼，以及如何在查詢中叫用自定義函式的指示。

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

注意

下列範例是使用Power BI Desktop 中找到的桌面體驗所建立，也可以使用在Excel for Windows 中找到的Power Query 體驗進行追蹤。

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

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

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

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

注意

建議您閱讀 Parameters 一文，以進一步瞭解如何在 Power Query 中建立和管理參數。

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

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

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

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

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

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

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

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

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

將轉換套用至範例查詢

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

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

資料夾中所有 CSV 檔案的格式都相同。 它們都有橫跨前四行的標頭。 數據行標頭位於第 5 列，而數據會從第 6 列向下開始，如下圖所示。

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

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

   

   注意

   若要深入瞭解如何移除數據列或依數據列位置篩選數據表，請移至 依數據列位置篩選。

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

   

升級欄位標頭之後，Power Query 預設會自動新增新的 已變更類型 步驟，以自動偵測每個欄位的數據類型。 您的 轉換範例檔案 查詢看起來會像下一個影像。

注意

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

謹慎

您的 轉換檔案 函式仰賴 轉換範例檔案 查詢中執行的步驟。 不過，如果您嘗試手動修改 轉換文件的 函式程式碼，您將會收到顯示 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 檔案）。 在功能區的 [新增欄] 索引標籤內，從 [一般] 群組中選取 [呼叫自定義函數]。 在 [叫用自定義函式] 視窗中，輸入 [輸出數據表] 做為 [新增數據行名稱] 。 從 函數查詢 下拉式清單中選取函式的名稱，轉換檔案。 從下拉功能表中選取函式之後，就會顯示函式的參數，而且您可以從數據表中選取要用來作為此函式自變數的數據行。 選擇「Content」欄作為傳遞給 File Parameter的值或參數。

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

注意

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

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

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

注意

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

我們也建議您閱讀 合併檔案概觀一文， 和 合併 CSV 檔案，進一步瞭解合併檔案體驗如何在 Power Query 中運作，以及自定義函式所扮演的角色。

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

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

若要進行這項需求，請使用文字數據類型建立名為 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
• FlightID = 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 公式語言。

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

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