閱讀英文

共用方式為


使用 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 中的查詢評估和查詢折疊概觀中所述。 這也是篩選出與您案例無關的任何數據的最佳作法。 這可讓您藉由只顯示數據預覽區段中的相關數據,來更專注於您手邊的工作。

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

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 中有數個功能可讓您的查詢復原變更,而且即使在數據源的某些元件變更時也能重新整理。

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

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

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

    注意

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

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

    注意

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

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

    注意

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

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

    注意

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

使用參數

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

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

    選取轉換自變數的參數。

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

    建立函式]。

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

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

    [管理參數] 視窗。

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

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

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

具有伺服器名稱參數的 SQL Server 資料庫對話框。

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

注意

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

建立可重複使用的函式

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

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

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

程式代碼清單。

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

範例參數程式代碼值。

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

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

範例轉換查詢。

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

叫用自定義函式。

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

叫用自定義函式之後的最終輸出查詢。

注意

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