閱讀英文

共用方式為


群組或摘要數據列

在 Power Query 中,您可以根據一或多個數據行中的值,將各種數據列中的值分組為單一值。 您可以選擇兩種類型的群組作業:

  • 數據行群組。

  • 數據列群組。

在本教學課程中,您會使用下列範例數據表。

範例初始數據表。

數據表的螢幕快照,其中顯示 Year (2020 年)、國家/地區(美國、巴拿馬或加拿大)、產品(襯衫或短褲)、銷售頻道(在線或轉銷商),以及單位(從 55 到 7500 的各種值)

在何處尋找 [分組依據] 按鈕

您可以在三個地方找到 [ 分組依據 ] 按鈕:

  • 在 [首頁] 索引標籤的 [轉換] 群組中

    Power Query 功能區的螢幕快照,其中 [常用] 索引標籤中強調 [群組依據] 選項。

  • 在 [轉換] 索引標籤的 [資料表] 群組中

    Power Query 功能區的螢幕快照,其中已強調 [轉換] 索引卷標的 [群組依據] 選項。

  • 在快捷方式功能表上,當您以滑鼠右鍵按下以選取資料行時。

    快捷方式選單中強調 [群組依據] 選項的數據表螢幕快照。

使用聚合函數依一或多個數據行分組

在此範例中,您的目標是摘要說明國家/地區銷售單位和銷售通道層級的總單位數。 您可以使用國家 /地區銷售通路 數據行,依作業執行群組。

  1. 在 [首頁] 索引標籤上選取 [群組依據]。
  2. 選取 [ 進階 ] 選項,以便選取要分組的多個數據行。
  3. 選取 [ 國家/地區] 資料行。
  4. 選取 [ 新增群組]。
  5. 選取 [ 銷售通路] 數據行。
  6. 在 [新增數據行名稱] 中,輸入 [總單位],在 [作業] 中選取 [總和],然後在 [數據行] 中選取 [單位]。
  7. 選取確定

[分組依據] 對話框的螢幕快照,其中已填入匯總數據行。

此作業提供下表。

範例輸出數據表的螢幕快照,其中包含 [國家/地區]、[銷售頻道] 和 [總單位] 資料行。

可用的作業

透過依功能分組,可用的作業可透過兩種方式進行分類:

  • 數據列層級作業
  • 數據行層級作業

下表說明每個作業。

作業名稱 類別 描述
Sum 數據行作業 加總數據行中的所有值
平均 數據行作業 計算數據行的平均值
中間值 數據行作業 從數據行計算中位數
Min 數據行作業 從數據行計算最小值
Max 數據行作業 計算數據行的最大值
百分比 數據行作業 使用 0 到 100 的輸入值,從數據行計算百分位數
計算相異值 數據行作業 計算數據行相異值的數目
計算數據列 數據列作業 計算指定群組的數據列總數
計算相異數據列 數據列作業 計算指定群組中相異數據列的數目
所有數據列 數據列作業 輸出數據表值中沒有匯總的所有群組數據列

注意

[ 計算相異值 ] 和 [百分位數 ] 作業只能在 Power Query Online 中使用。

執行依一或多個數據行分組的作業

從原始範例開始,在此範例中,您會建立一個數據行,其中包含總單位和另外兩個數據行,該數據行會提供您在國家/地區和銷售通道層級所銷售的產品名稱與單位。

包含作業的範例輸出數據表螢幕快照。

  1. 使用下列資料列做為 依資料列分組

    • Country
    • 銷售通路
  2. 依照下列步驟建立兩個新的數據行:

    1. 使用 Sum 作業匯總 Units 數據行。 將此數據行 命名為 [單位總計]。
    2. 使用 [所有數據列] 作業新增 Products 數據行

    [群組依據] 對話框的螢幕快照,其中包含非匯總數據行。

完成該作業之後,請注意 Products 數據行在每個數據格內有 [Table] 值的方式。 每個 [數據表] 值都包含原始數據表中依 CountrySales Channel 數據行分組的所有數據列。 您可以選取儲存格內的空格元,以檢視對話框底部資料表內容的預覽。

數據表詳細數據預覽窗格的螢幕快照。

注意

詳細數據預覽窗格可能不會顯示用於群組依據作業的所有數據列。 您可以選取 [Table] 值,以查看對應分組作業的相關所有數據列。

接下來,您必須擷取在 [新產品] 數據行內數據表的 [單位] 數據行中具有最大值的數據列,並呼叫該新數據行 Top performer product

擷取最佳效能者產品資訊

使用 [數據表] 值的新 Products 數據行,您可以移至功能區上的 [新增數據行] 索引標籤,然後從 [一般] 群組中選取 [自定義] 數據行,以建立新的自定義數據行。

Power Query 功能區的螢幕快照,其中已強調 [新增數據行] 索引卷標中的 [自定義數據行] 選項。

為您的新數據行 命名 Top performer product。 在 [自定義數據行公式] 下輸入公式。Table.Max([Products], "Units" )

[自定義數據行] 對話框的螢幕快照,其中已輸入 Table.Max 的公式。

該公式的結果會建立具有 [Record] 值的新數據行。 這些記錄值基本上只有一個數據列的數據表。 這些記錄包含 [Products] 資料行中每個 [資料表] 值之 Units 資料行最大值的數據列

使用 Table.Max 自定義數據行公式結果的螢幕快照。

有了這個包含 [Record] 值的新 Top performer 產品數據行,您可以選取展開圖示、選取 [產品和單位] 字段,然後選取 [確定]。

頂端執行者產品數據行上記錄值的展開作業螢幕快照。

移除 [ 產品 ] 資料行並設定新展開數據行的數據類型之後,您的結果會類似下圖。

包含所有轉換之最終數據表的螢幕快照。

模糊群組

注意

下列功能僅適用於 Power Query Online。

若要示範如何進行「模糊群組」,請考慮下圖所示的範例數據表。

數據表的螢幕快照,其中包含名稱 Miguel 和 William 的各種拼字和大小寫。

模糊群組的目標是執行使用文字字串近似比對演算法的分組作業。 Power Query 會使用 Jaccard 相似度演演算法來測量實例組之間的相似度。 然後,它會將集結式階層式叢集套用至將實例群組在一起。 下圖顯示您預期的輸出,其中數據表會依 Person 資料行分組。

數據表的螢幕快照,其中顯示 Person 的項目分別為 Miguel 和 Mike,以及 Frequency 為 3 和 2。

若要進行模糊群組,請執行本文先前所述的相同步驟。 唯一的差異在於這次,在 [分組依據 ] 對話框中,您選取 [ 使用模糊群組] 複選框。

[分組依據] 對話框中強調的 [模糊群組] 複選框螢幕快照。

針對每個數據列群組,Power Query 會挑選最頻繁的實例作為「標準」實例。 如果多個實例發生頻率相同,Power Query 會挑選第一個實例。 在 [分組依據] 對話框中選取 [確定] 之後,您會收到預期的結果。

模糊群組範例最終數據表的螢幕快照,沒有轉換數據表。

不過,您可以展開 模糊群組選項,以更充分掌控模糊群組作業。

[分組依據] 對話框的螢幕快照,其中強調模糊群組選項。

下列選項可用於模糊群組:

  • 相似度臨界值 (選擇性):此選項指出必須將兩個值分組在一起的方式。 零 (0) 的最小值設定會使所有值群組在一起。 最大值設定為 1 只允許完全符合的值分組在一起。 預設為 0.8。
  • 忽略大小寫:比較文字字串時,會忽略大小寫。 這個選項預設為啟用。
  • 藉由合併文字元件來分組:演算法會嘗試將文字元件(例如將 Microsoft 合併Microsoft)將值分組。
  • 顯示相似度分數:在模糊分組之後,顯示輸入值與計算代表值之間的相似度分數。 需要新增作業,例如 [所有數據 列] 以在逐列層級上展示這項資訊。
  • 轉換數據表 (選擇性):您可以選取將值對應至 Microsoft 的轉換數據表,以將它們分組在一起。

在此範例中,轉換數據表可用來示範如何對應值。 轉換資料表有兩個資料列:

  • 來源:要在您的數據表中尋找的文字字串。
  • To:要用來取代 From 數據行中文字字串的文字字串。

下圖顯示此範例中使用的轉換數據表。

此數據表的螢幕快照,其中顯示Mike和 William 的From值,以及 Miguel 和 Bill 的 To 值。

重要

轉換數據表必須具有相同的數據行和數據行名稱,如上圖所示(它們必須標示為 “From” 和 “To” )。 否則 Power Query 無法將資料表辨識為轉換數據表。

返回 [ 分組依據 ] 對話框,展開 [模糊群組選項],將作業從 [計數數據列 ] 變更為 [所有數據列],啟用 [顯示相似度分數 ] 選項,然後選取 [轉換數據表 ] 下拉功能表。

模糊群組範例轉換數據表下拉功能表的螢幕快照。

選取轉換數據表之後,請選取 [ 確定]。 該作業的結果會提供下列資訊:

模糊群組範例最終數據表與轉換數據表的螢幕快照。

在此範例中,已啟用 [忽略大小寫] 選項,因此使用 [轉換] 數據表[From] 資料行中的值來尋找文字字串,而不考慮字串大小寫。 此轉換作業會先進行,然後執行模糊群組作業。

相似度分數也會顯示在人員數據行旁邊的數據表值中,其會反映值分組方式及其各自的相似度分數。 如有需要,您可以展開此數據行,或使用新頻率數據行中的值進行其他轉換。

注意

當依多個數據行分組時,如果取代值會增加相似度分數,轉換數據表就會在所有數據行中執行取代作業。

如需轉換數據表運作方式的詳細資訊,請移至 轉換數據表述詞

另請參閱

新增自訂數據行
拿掉重複專案