使用 Power Query 時的最佳做法

發行項
03/13/2024

本文包含一些秘訣和訣竅，可讓您充分利用 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 中使用群組。

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

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

注意

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

未來校訂查詢

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

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

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

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

注意

若要深入瞭解依數據列位置篩選數據，請移至依數據列位置篩選數據表。
如果您的查詢具有動態資料行數目，但只需要從數據集選取特定數據行，您可以使用 [選擇數據行 ] 功能。

注意

若要深入瞭解如何選擇或移除數據行，請移至 [ 選擇] 或 [移除數據行]。
如果您的查詢具有動態數目的數據行，而且您只需要取消樞紐數據行的子集，您可以使用 僅取消樞紐選取的數據行 功能。

注意

若要深入瞭解取消樞紐數據行的選項，請移至取消樞紐數據行。
如果您的查詢有一個步驟可變更數據行的數據類型，但某些數據格會產生錯誤，因為值不符合所需的數據類型，您可以移除產生錯誤值的數據列。

注意

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