在展開數據表數據行時優化Power Query

簡單且容易使用，可讓Power BI使用者快速收集數據併產生有趣且功能強大的報表，以做出智慧型手機商務決策，也允許使用者輕鬆產生效能不佳的查詢。 當外鍵關聯 SQL 資料表或 SharePoint 清單的方式有兩個數據表相關時，通常會發生這種情況。 （針對記錄，此問題並非 SQL 或 SharePoint 專屬，而且會在許多後端數據擷取案例中發生，特別是架構流暢且可自定義的情況。將數據儲存在共用通用索引鍵的個別數據表中也沒有什麼問題，事實上，這是資料庫設計和正規化的基本原則。 但它確實意味著一個更好的方法來擴大關係。

請考慮下列 SharePoint 客戶清單範例。

其所參考的位置清單如下。

第一次連線到清單時，位置會顯示為記錄。

此最上層數據會透過SharePoint API 的單一 HTTP 呼叫來收集（忽略元數據呼叫），您可以在任何Web調試程式中看到這些資料。

當您展開記錄時，您會看到從次要數據表聯結的欄位。

將相關數據列從一個數據表擴充到另一個數據表時，Power BI 的預設行為是產生 對 Table.ExpandTableColumn的呼叫。 您可以在產生的公式欄位中看到此專案。 不幸的是，此方法會針對第一個數據表中的每個數據列產生對第二個數據表的個別呼叫。

這會為主要清單中的每個數據列增加一個 HTTP 呼叫數目。 在上述五或六個數據列的範例中，這似乎並不像很多，但在 SharePoint 清單達到數十萬個數據列的生產系統中，這可能會導致顯著的體驗降低。

當查詢達到此瓶頸時，最佳緩和措施是使用傳統數據表聯結來避免每個數據列的呼叫行為。 這可確保只會有一個呼叫來擷取第二個數據表，而擴充的其餘部分可以使用兩個數據表之間的通用索引鍵在記憶體中發生。 在某些情況下，效能差異可能會很大。

首先，從原始數據表開始，並指出您要展開的數據行，並確定您擁有專案的標識碼，以便比對它。 一般而言，外鍵的名稱類似於附加標識符的數據行顯示名稱。 在此範例中，它是 LocationId。

其次，載入次要數據表，請務必包含標識碼，這是外鍵。 以滑鼠右鍵按兩下 [查詢] 面板以建立新的查詢。

最後，使用符合的個別數據行名稱聯結兩個數據表。 您通常會先展開數據行，然後在預覽中尋找相符的數據行，以尋找此欄位。

在此範例中，您可以看到 主要清單中的LocationId 符合 次要清單中的識別碼 。 UI 會將這個 重新命名為 Location.Id ，使數據行名稱是唯一的。 現在讓我們使用這項資訊來合併數據表。

以滑鼠右鍵按下查詢面板，然後選取 [新增查詢>合併>合併查詢] 作為 [新增]，您會看到一個易記 UI，以協助您合併這兩個查詢。

從下拉式清單中選取每個數據表，以查看查詢的預覽。

選取這兩個數據表之後，請選取以邏輯方式聯結數據表的數據行（在此範例中，它是 主要數據表中的LocationId 和 次要數據表的Id ）。 對話框會指示您使用該外鍵比對的數據列數目。 您可能會想要針對這類數據使用預設聯結種類（左外部）。

選取 [ 確定 ]，您會看到新的查詢，這是聯結的結果。 現在展開記錄並不表示對後端的其他呼叫。

重新整理此數據只會對 SharePoint 產生兩個呼叫，一個用於主要清單，另一個用於次要清單。 聯結將會在記憶體中執行，大幅減少對 SharePoint 的呼叫數目。

此方法可用於PowerQuery中具有相符外鍵的任何兩個數據表。

注意

SharePoint 使用者清單和分類法也可以以數據表的形式存取，而且可以完全依照上述方式聯結，前提是使用者有足夠的許可權可存取這些清單。