＜列轉行系列3＞將經理人代碼、姓名、職員從列拆分為行

-

  我今天要寫的這個問題在數據清理時是很常見的，特別是當資料是從網站或 pdf 中抓取時，經由 Ctrl+C，然後在 Excel 中 Ctrl+V 貼上，結果常常令人感到氣憤，所有的資料都被轉換成同一行，這與我原先在網頁或 pdf 中看到的很不一樣。我打算做這一系列情況的解決方案。 

狀況一：是每一組的列數固定，例如每 4 列就是一組數據，每組數據之間隔著一空白列。我有二種解法，第1集使用樞紐資料行來解，第2集使用分組工具來做。 

狀況二：當然就是每一組的列數不固定，有的可能 2 列就是一組，有的可能 3、4、5、6、7 列一組，還值得慶幸的是至少每一組數據還有前導資料行，讓我可以判斷每一組數據的頭在哪裡。第3集。 

狀況三：就是最棘手狀況了，就是狀況二沒有前導資料行，所有資料都在同一行，也沒有空白儲存格給你分組。第4集。 準備好跟我一起升級打怪了嗎？

================================================

＜列轉行系列3＞將經理人代碼、姓名、職員從列拆分為行



Data：給定有 Manager ECode, Manager Name 和 Employee Names 三類資料放在同一行， 其中 Employee Names 人數不一，任務是把這些資料拆分為一列列。

 💡 提示。。。

  1. 英文開頭數字結尾的是 the EMP code of the Manager。
  2. 下一列是 the Name of the Manager。
  3. 接下來的便是 Employees Names，直到出現下一個 fManager ECode 為止。 </aside>


Step 1 - 取出 Manager ECode。新增索引資料行，再新增一自訂資料行，並且輸入以下任一公式，看您喜歡哪一個都行!!

if List.ContainsAny( Text.ToList( [Data] ) , {"0" .. "9"} ) then [Data] else null

if (try Number.From(Text.End([Data], 1)) otherwise "") is number then [Data] else null



Step 2 - 取出Manager Name。這時我會藉由 [索引] 這資料行，取出 [Manager ECode] 的下一列資料。一樣，新增自訂資料行，並且輸入以下公式

if [Manager ECode] <> null then 已新增自訂[Data]{[索引] + 1} else null



Step 3 - 向下填滿。選擇 Manager ECode 和 Manager Name，向下填滿。


Step 4 - 分組。選擇 Manager ECode 和 Manager Name 為分組依據，輸入新資料行名稱，作業這裡選 "所有資料列” (很重要)。


這時可以看到，在 Table 裡的 "Data” 欄，前 2 列是經理人代碼和經理人名稱，是我們不要的，只要第 3 列以下的職員姓名，所以下一步我們移除前 2 列。

Step 5 - 移除 [Grouping] 的前 2 列。可以看到 [Grouping] 裡的資料, index 0,1 都被移除了，只剩下 2 以後的列數。

= Table.TransformColumns(已群組資料列,{"Grouping", each Table.RemoveFirstN(_, 2)})



Step 6 - 傳回為清單 (List)。使用 Table.Column 方式將 [Grouping] 裡的 “Data” 欄傳回為清單 (List)



Step 7 - 擷取清單。點擊 [清單] 旁的雙箭頭展開鈕 >>擷取值 >> 選一個你佮意的分隔符號，例如：逗號



Step 8 - 分割。選取 [清單] 資料行>> 常用 >> 分割資料行 >> 使用逗號來分割，再移除不需要的資料行。完工!!



留言

張貼留言