使用 Power Query 時的最佳做法

本文包含一些秘訣和訣竅，可讓您充分利用 Power Query 中的數據整頓體驗。

選擇正確的連接器

Power Query 提供大量的數據連接器。 這些連接器的範圍從 TXT、CSV 和 Excel 檔案等數據源到 Microsoft SQL Server 等資料庫，以及 Microsoft Dynamics 365 和 Salesforce 等熱門 SaaS 服務。 如果您沒有看到 [取得數據] 視窗中所列的數據源，您一律可以使用 ODBC 或 OLEDB 連接器來連線到您的數據源。

使用工作的最佳連接器可提供最佳的體驗和效能。 例如，在連接到 SQL Server 資料庫時，使用 SQL Server 連接器，而不是 ODBC 連接器，不僅提供更佳 的取得數據 體驗，而且 SQL Server 連接器也提供可改善體驗和效能的功能，例如查詢折疊。 若要深入了解查詢折疊，請移至 Power Query 中的查詢評估和查詢折疊概觀。

每個數據連接器都會遵循標準體驗，如取得數據中所述。 此標準化體驗具有稱為 「數據預覽」的階段。 在這個階段中，您會提供方便使用的視窗，以選取您想要從數據源取得的數據，如果連接器允許該數據，以及該數據的簡單數據預覽。 您甚至可以透過 [導覽器 ] 視窗從數據源選取多個數據集，如下圖所示。

注意

若要查看 Power Query 中可用連接器的完整清單，請移至 Power Query 中的 連線 ors。

提前篩選

建議您在查詢的早期階段或儘早篩選您的數據。 某些連接器會透過查詢折疊來利用篩選，如 Power Query 中的查詢評估和查詢折疊概觀中所述。 這也是篩選出與您案例無關的任何數據的最佳作法。 這可讓您藉由只顯示數據預覽區段中的相關數據，來更專注於您手邊的工作。

您可以使用 [自動篩選] 選單來顯示資料列中找到之值的不同清單，以選取您想要保留或篩選掉的值。您也可以使用搜尋列來協助您在資料行中尋找值。

您也可以利用類型特定的篩選，例如 在上 一個日期、日期時間或甚至日期時區數據行。

這些類型特定的篩選可協助您建立動態篩選，其一律會擷取先前 x 秒數、分鐘數、小時、天、周、月、季或年的數據，如下圖所示。

注意

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

上次執行昂貴的作業

某些作業需要讀取完整的數據源，才能傳回任何結果，因此在 Power Query 編輯器 中預覽的速度會很慢。 例如，如果您執行排序，可能是前幾個排序的數據列位於源數據結尾。 因此，為了傳回任何結果，排序作業必須先讀取 所有數據 列。

傳回任何結果之前，其他作業（例如篩選條件）不需要讀取所有數據。 相反地，它們會以所謂的「串流」方式對數據進行操作。 透過傳回數據「串流」，並傳回結果。 在 Power Query 編輯器 中，這類作業只需要讀取足夠的源數據才能填入預覽。

可能的話，請先執行這類串流作業，最後再執行任何成本更高的作業。 這可協助將您等待預覽的時間量降至最低，以在每次將新步驟新增至查詢時呈現。

暫時針對數據的子集運作

如果在 Power Query 編輯器 中將新步驟新增至查詢速度很慢，請考慮先執行「保留第一個數據列」作業，並限制您正在處理的數據列數目。 然後，新增所需的所有步驟后，請移除 [保留第一個數據列] 步驟。

使用正確的數據類型

Power Query 中的某些功能與選取之數據行的數據類型相關。 例如，選取日期數據行時，[新增數據行] 功能表中 [日期和時間] 資料行群組底下的可用選項將可供使用。 但是，如果數據行沒有數據類型集，則這些選項會呈現灰色。

類型特定篩選也發生類似的情況，因為它們專屬於特定數據類型。 如果您的數據行未定義正確的資料類型，將無法使用這些類型特定的篩選。

請務必一律使用數據行的正確數據類型。 使用資料庫等結構化數據源時，數據類型資訊會從資料庫中找到的數據表架構中帶入。 但是，對於 TXT 和 CSV 檔案等非結構化數據源，請務必為來自該數據源的數據行設定正確的數據類型。 根據預設，Power Query 會為非結構化數據源提供自動數據類型偵測。 您可以深入瞭解這項功能，以及如何在數據類型中協助您。

注意

若要深入了解數據類型的重要性及其使用方式，請參閱 數據類型。

探索您的數據

開始準備數據並新增轉換步驟之前，建議您啟用Power Query 資料分析工具 ，輕鬆地探索數據的相關信息。

這些數據分析工具可協助您進一步瞭解您的數據。 這些工具會提供小型視覺效果，以根據每個數據行顯示資訊，例如：

• 數據行品質：提供小型條形圖和三個指標，其中代表數據行中有多少值落在有效、錯誤或空白值的類別之下。
• 數據行散發 — 提供數據行名稱下的一組視覺效果，以展示每個數據行中值的頻率和分佈。
• 數據行配置檔— 提供更完整的數據行檢視，以及與其相關聯的統計數據。

您也可以與這些功能互動，以協助您準備數據。

注意

若要深入了解數據分析工具，請移至 數據分析工具。

記錄您的工作

建議您在視需要時，將描述重新命名或新增至步驟、查詢或群組，以記錄您的查詢。

雖然 Power Query 會在套用的步驟窗格中自動為您建立步驟名稱，但您也可以將步驟重新命名，或在其中任何步驟中新增描述。

注意

若要深入瞭解在套用的步驟窗格中找到的所有可用功能和元件，請移至 使用 [套用的步驟] 清單。

採用模組化方法

完全可以建立單一查詢，其中包含您可能需要的所有轉換和計算。 但是，如果查詢包含大量的步驟，則最好將查詢分割成多個查詢，其中一個查詢會參考下一個查詢。 此方法的目標是將轉換階段簡化和分離為較小的部分，以便更容易瞭解。

例如，假設您有查詢，其中包含下圖所示的九個步驟。

您可以在合併與價格資料表步驟中將此查詢分割成兩個。 如此一來，就更容易了解合併之前已套用至銷售查詢的步驟。 若要執行這項作業，請以滑鼠右鍵按兩下 [與價格合併] 資料表 步驟，然後選取 [ 擷取上一個] 選項。

接著，系統會提示您輸入對話框，為您的新查詢指定名稱。 這會有效地將您的查詢分割成兩個查詢。 一個查詢會在合併之前擁有所有查詢。 另一個查詢會有一個初始步驟，它會參考您的新查詢，以及您在原始查詢 中從 [合併與價格] 數據表 步驟向下執行的其餘步驟。

您也可以利用您認為適合的查詢參考。 但最好讓查詢保持在一個層級，但一眼就看不見如此多的步驟並不令人生畏。

注意

若要深入了解查詢參考，請移至了解 查詢窗格。

建立群組

讓工作保持組織的絕佳方式，就是利用查詢窗格中的群組。

群組的唯一用途是協助您將工作組織成查詢的資料夾。 如果需要，您可以在群組內建立群組。 跨群組移動查詢就像拖放一樣簡單。

嘗試為您的群組提供有意義的名稱，對您和您的案例有意義。

注意

若要深入了解查詢窗格中找到的所有可用功能和元件，請移至 了解查詢窗格。

未來校訂查詢

請確定您建立未來重新整理期間不會有任何問題的查詢，是重中之重。 Power Query 中有數個功能可讓您的查詢復原變更，而且即使在數據源的某些元件變更時也能重新整理。

最佳作法是定義查詢的範圍，以做為應該執行的動作，以及在結構、配置、數據行名稱、數據類型，以及您考慮與範圍相關的任何其他元件方面應考慮的內容。

可協助您讓查詢復原變更的一些轉換範例如下：

• 如果您的查詢具有具有數據的動態資料列數，但可做為應移除之頁尾的固定數據列數目，您可以使用 [移除底部數據列 ] 功能。

  注意

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

• 如果您的查詢具有動態資料行數目，但只需要從數據集選取特定數據行，您可以使用 [選擇數據行 ] 功能。

  注意

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

• 如果您的查詢具有動態數目的數據行，而且您只需要取消樞紐數據行的子集，您可以使用 僅取消樞紐選取的數據行 功能。

  注意

  若要深入瞭解取消樞紐數據行的選項，請移至取消 樞紐數據行。

• 如果您的查詢有一個步驟可變更數據行的數據類型，但某些數據格會產生錯誤，因為值不符合所需的數據類型，您可以移除產生錯誤值的數據列。

  注意

  若要深入瞭解處理錯誤，請移至 處理錯誤。

使用參數

建立動態且具彈性的查詢是最佳做法。 Power Query 中的參數可協助您讓查詢更具動態性和彈性。 參數可用來輕鬆地儲存和管理可透過許多不同的方式重複使用的值。 但它在兩個案例中比較常用：

• 步驟自變數： 您可以使用 參數作為使用者介面所驅動之多個轉換的自變數。

  

• 自定義函式自變數：您可以從查詢建立新的函式，並將參數參考為自定義函式的自變數。

  

建立和使用參數的主要優點包括：

• 透過 [ 管理參數 ] 視窗集中檢視所有參數。

  

• 在多個步驟或查詢中重複使用參數。

• 讓建立自定義函式變得簡單易懂。

您甚至可以在數據連接器的某些自變數中使用參數。 例如，當您連接到 SQL Server 資料庫時，您可以為您的伺服器名稱建立參數。 然後，您可以在 [SQL Server 資料庫] 對話框中使用該參數。

如果您變更伺服器位置，您只需要更新伺服器名稱的參數，並更新您的查詢。

注意

若要深入瞭解如何建立和使用參數，請移至使用 參數。

建立可重複使用的函式

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

例如，假設您有多個查詢或值需要同一組轉換。 您可以建立自定義函式，稍後可以針對您選擇的查詢或值叫用。 此自定義函式可節省時間，並協助您在中央位置管理一組轉換，您可以隨時加以修改。

您可以從現有的查詢和參數建立 Power Query 自定義函式。 例如，假設查詢有數個程式代碼做為文字字串，而您想要建立將這些值譯碼的函式。

首先，您有一個參數具有做為範例的值。

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

• Origin = PTY
• 目的地 = LAX
• 航空公司 = CM
• FlightID = 1090

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

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

注意

若要深入瞭解如何在Power Query中建立和使用自定義函式，請參閱自定義函式一文。