共用方式為


使用 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 中的連接器

提前篩選

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

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

Power Query 中 [自動篩選] 功能表的螢幕快照,其中強調數據行值。

您也可以利用類型特定的篩選,例如 先前的 來篩選日期、日期時間,或甚至日期時區欄位。

已強調上一個選項的日期列範例類型專用篩選器的螢幕截圖。

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

[篩選數據列] 對話框的螢幕快照,其中顯示 [在先前的日期特定篩選] 中。

備註

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

昂貴的運算能持續多久?

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

在傳回任何結果之前,其他操作(例如篩選)不需要讀取所有數據。 相反地,它們會以所謂的「串流」方式對數據進行作。 數據會以流的形式傳輸,同時返回結果。 在 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 分割成多個元件:

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

範例轉換查詢的螢幕快照,其中每個部分都位於其本身的數據行中。

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

已填入 [叫用自定義函式] 值之程式代碼清單的螢幕快照。

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

螢幕快照,顯示叫用自定義函式之後的最終輸出查詢。

備註

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