訓練
群組或摘要數據列
在 Power Query 中,您可以根據一或多個數據行中的值,將各種數據列中的值分組為單一值。 您可以選擇兩種類型的群組作業:
數據行群組。
數據列群組。
在本教學課程中,您會使用下列範例數據表。
數據表的螢幕快照,其中顯示 Year (2020 年)、國家/地區(美國、巴拿馬或加拿大)、產品(襯衫或短褲)、銷售頻道(在線或轉銷商),以及單位(從 55 到 7500 的各種值)
您可以在三個地方找到 [ 分組依據 ] 按鈕:
在 [首頁] 索引標籤的 [轉換] 群組中。
在 [轉換] 索引標籤的 [資料表] 群組中。
在快捷方式功能表上,當您以滑鼠右鍵按下以選取資料行時。
在此範例中,您的目標是摘要說明國家/地區銷售單位和銷售通道層級的總單位數。 您可以使用國家 /地區 和 銷售通路 數據行,依作業執行群組。
- 在 [首頁] 索引標籤上選取 [群組依據]。
- 選取 [ 進階 ] 選項,以便選取要分組的多個數據行。
- 選取 [ 國家/地區] 資料行。
- 選取 [ 新增群組]。
- 選取 [ 銷售通路] 數據行。
- 在 [新增數據行名稱] 中,輸入 [總單位],在 [作業] 中選取 [總和],然後在 [數據行] 中選取 [單位]。
- 選取確定
此作業提供下表。
透過依功能分組,可用的作業可透過兩種方式進行分類:
- 數據列層級作業
- 數據行層級作業
下表說明每個作業。
作業名稱 | 類別 | 描述 |
---|---|---|
Sum | 數據行作業 | 加總數據行中的所有值 |
平均 | 數據行作業 | 計算數據行的平均值 |
中間值 | 數據行作業 | 從數據行計算中位數 |
Min | 數據行作業 | 從數據行計算最小值 |
Max | 數據行作業 | 計算數據行的最大值 |
百分比 | 數據行作業 | 使用 0 到 100 的輸入值,從數據行計算百分位數 |
計算相異值 | 數據行作業 | 計算數據行相異值的數目 |
計算數據列 | 數據列作業 | 計算指定群組的數據列總數 |
計算相異數據列 | 數據列作業 | 計算指定群組中相異數據列的數目 |
所有數據列 | 數據列作業 | 輸出數據表值中沒有匯總的所有群組數據列 |
注意
[ 計算相異值 ] 和 [百分位數 ] 作業只能在 Power Query Online 中使用。
從原始範例開始,在此範例中,您會建立一個數據行,其中包含總單位和另外兩個數據行,該數據行會提供您在國家/地區和銷售通道層級所銷售的產品名稱與單位。
使用下列資料列做為 依資料列分組 :
- Country
- 銷售通路
依照下列步驟建立兩個新的數據行:
- 使用 Sum 作業匯總 Units 數據行。 將此數據行 命名為 [單位總計]。
- 使用 [所有數據列] 作業新增 Products 數據行。
完成該作業之後,請注意 Products 數據行在每個數據格內有 [Table] 值的方式。 每個 [數據表] 值都包含原始數據表中依 Country 和 Sales Channel 數據行分組的所有數據列。 您可以選取儲存格內的空格元,以檢視對話框底部資料表內容的預覽。
注意
詳細數據預覽窗格可能不會顯示用於群組依據作業的所有數據列。 您可以選取 [Table] 值,以查看對應分組作業的相關所有數據列。
接下來,您必須擷取在 [新產品] 數據行內數據表的 [單位] 數據行中具有最大值的數據列,並呼叫該新數據行 Top performer product。
使用 [數據表] 值的新 Products 數據行,您可以移至功能區上的 [新增數據行] 索引標籤,然後從 [一般] 群組中選取 [自定義] 數據行,以建立新的自定義數據行。
為您的新數據行 命名 Top performer product。 在 [自定義數據行公式] 下輸入公式。Table.Max([Products], "Units" )
該公式的結果會建立具有 [Record] 值的新數據行。 這些記錄值基本上只有一個數據列的數據表。 這些記錄包含 [Products] 資料行中每個 [資料表] 值之 Units 資料行最大值的數據列。
有了這個包含 [Record] 值的新 Top performer 產品數據行,您可以選取展開圖示、選取 [產品和單位] 字段,然後選取 [確定]。
移除 [ 產品 ] 資料行並設定新展開數據行的數據類型之後,您的結果會類似下圖。
注意
下列功能僅適用於 Power Query Online。
若要示範如何進行「模糊群組」,請考慮下圖所示的範例數據表。
模糊群組的目標是執行使用文字字串近似比對演算法的分組作業。 Power Query 會使用 Jaccard 相似度演演算法來測量實例組之間的相似度。 然後,它會將集結式階層式叢集套用至將實例群組在一起。 下圖顯示您預期的輸出,其中數據表會依 Person 資料行分組。
若要進行模糊群組,請執行本文先前所述的相同步驟。 唯一的差異在於這次,在 [分組依據 ] 對話框中,您選取 [ 使用模糊群組] 複選框。
針對每個數據列群組,Power Query 會挑選最頻繁的實例作為「標準」實例。 如果多個實例發生頻率相同,Power Query 會挑選第一個實例。 在 [分組依據] 對話框中選取 [確定] 之後,您會收到預期的結果。
不過,您可以展開 模糊群組選項,以更充分掌控模糊群組作業。
下列選項可用於模糊群組:
- 相似度臨界值 (選擇性):此選項指出必須將兩個值分組在一起的方式。 零 (0) 的最小值設定會使所有值群組在一起。 最大值設定為 1 只允許完全符合的值分組在一起。 預設為 0.8。
- 忽略大小寫:比較文字字串時,會忽略大小寫。 這個選項預設為啟用。
- 藉由合併文字元件來分組:演算法會嘗試將文字元件(例如將 Micro 和 soft 合併為 Microsoft)將值分組。
- 顯示相似度分數:在模糊分組之後,顯示輸入值與計算代表值之間的相似度分數。 需要新增作業,例如 [所有數據 列] 以在逐列層級上展示這項資訊。
- 轉換數據表 (選擇性):您可以選取將值對應至 Microsoft 的轉換數據表,以將它們分組在一起。
在此範例中,轉換數據表可用來示範如何對應值。 轉換資料表有兩個資料列:
- 來源:要在您的數據表中尋找的文字字串。
- To:要用來取代 From 數據行中文字字串的文字字串。
下圖顯示此範例中使用的轉換數據表。
重要
轉換數據表必須具有相同的數據行和數據行名稱,如上圖所示(它們必須標示為 “From” 和 “To” )。 否則 Power Query 無法將資料表辨識為轉換數據表。
返回 [ 分組依據 ] 對話框,展開 [模糊群組選項],將作業從 [計數數據列 ] 變更為 [所有數據列],啟用 [顯示相似度分數 ] 選項,然後選取 [轉換數據表 ] 下拉功能表。
選取轉換數據表之後,請選取 [ 確定]。 該作業的結果會提供下列資訊:
在此範例中,已啟用 [忽略大小寫] 選項,因此使用 [轉換] 數據表的 [From] 資料行中的值來尋找文字字串,而不考慮字串大小寫。 此轉換作業會先進行,然後執行模糊群組作業。
相似度分數也會顯示在人員數據行旁邊的數據表值中,其會反映值分組方式及其各自的相似度分數。 如有需要,您可以展開此數據行,或使用新頻率數據行中的值進行其他轉換。
注意
當依多個數據行分組時,如果取代值會增加相似度分數,轉換數據表就會在所有數據行中執行取代作業。
如需轉換數據表運作方式的詳細資訊,請移至 轉換數據表述詞。