問題的形成
作為輸入數據，我們有一個 Excel 文件，其中一張表包含多個表格，其中包含以下形式的銷售數據：
注意：
- 不同大小的表格，在行和列中具有不同的產品和區域集，無需任何排序。
- 表格之間可以插入空行。
- 表的數量可以是任意的。
兩個重要的假設。 假設：
- 在每張表格上方的第一列中，有該表格說明其銷售額的經理的姓名（Ivanov、Petrov、Sidorov 等）。
- 所有表格中的商品名稱和地區名稱都以相同的方式書寫 - 精確到大小寫。
最終目的是將所有表的數據收集到一個扁平的規範化表中，方便後續分析和構建匯總，即在這個：
步驟 1. 連接到文件
讓我們創建一個新的空 Excel 文件並在選項卡上選擇它 數據 命令 獲取數據 - 從文件 - 從書 （數據 - 來自文件 - 來自工作簿）. 使用銷售數據指定源文件的位置，然後在導航器窗口中選擇我們需要的工作表並單擊按鈕 轉換數據 （轉換數據）:
因此，其中的所有數據都應加載到 Power Query 編輯器中：
步驟 2. 清理垃圾
刪除自動生成的步驟 修改型 （更改類型） и 高架標頭 （提升的標題） 並使用過濾器刪除空行和總計行 空 и 總計 由第一列。 結果，我們得到以下圖片：
步驟 3. 添加管理員
為了以後了解誰的銷售額在哪裡，有必要在我們的表中添加一列，其中每一行都會有一個對應的姓氏。 為了這：
1. 讓我們使用命令添加一個帶有行號的輔助列 添加列 – 索引列 – 從 0 （添加列——索引列——從 0 開始）.
2. 使用命令添加帶有公式的列 添加列 - 自定義列 （添加列 - 自定義列） 並在那裡引入以下結構：
這個公式的邏輯很簡單——如果第一列下一個單元格的值是“產品”，那麼這意味著我們偶然發現了一個新表的開頭，所以我們將前一個單元格的值顯示為經理的姓名。 否則，我們不顯示任何內容，即 null。
要獲取具有姓氏的父單元格，我們首先參考上一步中的表格 #“添加索引”，然後指定我們需要的列名 [欄1] 方括號中的單元格編號和大括號中該列中的單元格編號。 單元格數將比我們從列中獲取的當前單元格數少一 Index 指數分別。
3. 剩下的就是用 空 使用命令來自更高單元格的名稱 變換 - 填充 - 向下 （變換——填充——向下） 並刪除不再需要的具有索引的列和第一列中具有姓氏的行。 結果，我們得到：
步驟 4. 由經理分組到單獨的表中
下一步是將每個經理的行分組到單獨的表中。 為此，在 Transformation 選項卡上，使用 Group by 命令（Transform – Group By）並在打開的窗口中，選擇 Manager 列和操作 All rows（所有行）來簡單地收集數據而不應用任何聚合函數它們（總和、平均值等）。 P.）：
結果，我們為每個經理獲得了單獨的表格：
第 5 步：轉換嵌套表
現在我們給出位於結果列的每個單元格中的表格 所有資料 體面的形狀。
首先，刪除每個表中不再需要的列 經理. 我們再次使用 自定義列 選項卡 轉型 （變換——自定義列） 和以下公式：
然後，使用另一個計算列，我們將每個表中的第一行提升到標題：
最後，我們執行主要的轉換——使用 M 函數展開每個表 表.UnpivotOtherColumns:
標題中的區域名稱將進入一個新列，我們將獲得一個更窄但同時更長的規範化表。 空單元格 空 被忽略。
擺脫不必要的中間列，我們有：
步驟 6 展開嵌套表
使用列標題中帶有雙箭頭的按鈕將所有規範化嵌套表擴展為單個列表：
......我們終於得到了我們想要的：
您可以使用以下命令將結果表導出回 Excel 主頁 — 關閉並加載 — 關閉並加載... （首頁 — 關閉並加載 — 關閉並加載到…）.
- 從多本書中構建具有不同標題的表格
- 從給定文件夾中的所有文件中收集數據
- 將書中所有工作表中的數據收集到一個表中