使用自訂函式

如果您在需要將相同轉換集套用至不同查詢或值的情況中，請建立 Power Query 自定義函式，以重複使用您所需的次數可能很有説明。 Power Query 自定義函式是從一組輸入值對應到單一輸出值，並從原生 M 函式和運算子建立。

雖然您可以使用瞭解 Power Query M 函式中所述的程式代碼手動建立自己的 Power Query 自定義函式，但 Power Query 使用者介面提供您功能來加速、簡化及增強建立和管理自定義函式的程式。

本文著重於此體驗，僅透過Power Query使用者介面提供，以及如何充分利用它。

重要

本文概述如何使用 Power Query 使用者介面中可存取的一般轉換，使用 Power Query 建立自定義函式。 其著重於建立自定義函式的核心概念，以及 Power Query 檔中其他文章的連結，以取得本文所參考之特定轉換的詳細資訊。

從數據表參考建立自定義函式

注意

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

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

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

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

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

注意

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

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

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

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

以滑鼠右鍵按兩下 [查詢] 窗格中的 [檔案參數]。 選取 [ 參考] 選項。

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

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

這項作業會有效地建立與轉換範例檔案查詢連結的新函式。 您對轉換範例檔案查詢所做的任何變更都會自動複寫至您的自定義函式。 在建立這個新函式期間，請使用 轉換檔案 作為函 式名稱。

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

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

將轉換套用至範例查詢

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

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

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

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

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

   

   注意

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

2. 升級標頭— 您最後一個資料表的標頭現在位於資料表的第一個資料列中。 您可以升級它們，如下一個影像所示。

   

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

注意

若要深入瞭解如何升級和降級標頭，請移至 升級或降級數據行標頭。

警告

您的轉換檔案函式依賴轉換範例檔案查詢中執行的步驟。 不過，如果您嘗試手動修改 Transform 檔案函式的程式代碼，您將會看到讀取的警告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 檔案 ）。 在功能區的 [新增數據行] 索引標籤內，從 [一般] 群組中選取 [叫用自定義函式]。 在 [ 叫用自定義函數 ] 視窗中，輸入 [輸出數據表 ] 作為 [新增數據行名稱]。 從 [函式查詢] 下拉式清單中選取函式的名稱 [轉換檔案]。 從下拉功能表中選取函式之後，就會顯示函式的參數，而且您可以從數據表中選取要用來作為此函式自變數的數據行。 選取 [內容] 資料行作為要針對 [檔案參數] 傳遞的值/自變數。

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

注意

若要深入瞭解如何從數據表選擇或移除數據行，請移至 [ 選擇] 或 [移除數據行]。

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

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

注意

到目前為止，您所閱讀的內容基本上與在合併檔案體驗期間發生的程式相同，但手動完成。

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

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

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

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

使用這個新參數，選取 [轉換範例檔案查詢]，並使用 Market 參數中的值來篩選 Country 字段。

注意

若要深入瞭解如何依值篩選數據行，請移至 [篩選值]。

將這個新步驟套用至您的查詢會自動更新轉換檔案函式，此函式現在需要兩個參數，以轉換範例檔案所使用的兩個參數為基礎。

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

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

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

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

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

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

code
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

首先，您有一個參數具有做為範例的值。 在此情況下，它是 PTY-CM1090-LAX 值。

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

• Origin = 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 公式語言。

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

再進行一些轉換之後，您可以看到您已達到所需的輸出，並針對自定義函式的這類轉換套用邏輯。