借助Power Query快速轉換數據

王志軍

1. 數據轉換為表

打開數據表，切換到“數據”選項卡，在“獲取和轉換”功能組單擊“從表格”按鈕，在隨后彈出的對話框中根據提示選擇數據源，Excel會自動將選定區域轉換為表，并打開查詢編輯器界面。

2. 提取學科數據

在“表1-查詢編輯器”窗口選擇學號、姓名兩列的列標，切換到“轉換”選項卡，在“任意列”功能組依次選擇“逆透視列→逆透視其他列”，執行后可以將當前選定列轉換為“屬性/值”對，并與每行中的剩余值相結合，顯示效果如圖所示（圖2）。

可以看到“屬性”列包含了語文、數學、英語等多門學科的名稱，我們首先需要將這些學科的名稱從“屬性”列提取出來。選擇“屬性”列的列表，右擊選擇“拆分列→按分隔符”，此時會打開“按分隔符拆分列”對話框，在“選擇或輸入分隔符”列表框選擇“自定義”，輸入“|”進行分隔，這里不需要更改其他選項，執行之后的效果如圖所示（圖3）。可以看到原來的“屬性”列已經被分隔為屬性1、屬性2兩列，“屬性1”是各門學科的名稱，“屬性2”則是成績、等第。

由于“屬性2”列仍然混合了成績、等第這兩個內容，因此還需要將這些內容區分開來。選擇“屬性2”列，切換到“轉換”選項卡，在“任意列”功能組單擊“透視列”按鈕，這一操作是為了使用當前所選列中的名稱創建新列，此時會彈出“透視列”對話框，在“值列”下拉列表框選擇“值”，點擊“高級選項”前面的按鈕，在下拉列表框選擇“不要聚合”，最終設置如圖所示（圖4）。

完成上述設置之后，原有的“屬性2”列已經被轉換為成績、等第兩個新的列。如果覺得默認的“屬性1”不太合適，可以右擊重命名為“學科”。關閉查詢編輯器，此時會提示是否保留更改（圖5），確認之后會返回Excel主界面，此時就可看到最終效果（圖6）。接下來可以根據對各個項目進行適當的篩選，感興趣的朋友可以一試。

如圖所示的“DATA”列都是字母、數字的混合文本（圖7），現在要求依次提取純文本、純數值、不重復數值。手工提取顯然是相當麻煩，而且也容易出錯，利用Power Query內置的函數可以輕松實現。

在源數據選擇“DATA”列的數據區域，切換到“數據”選項卡，在“獲取和轉換”功能組單擊“從表格”按鈕，此時會打開查詢編輯器，切換到“添加列”選項卡，單擊“常規”功能組的“添加自定義列”按鈕，此時會彈出“添加自定義列”對話框，首先將默認的列名“Custom”修改為“純文本”，接下來在下面的“自定義列公式”窗格輸入“= Text.Trim（Text.Remove（[DATA]，{"0".."9"}），" "）”，檢查無誤之后單擊“確定”按鈕，很快就可以在新添加的自定義列顯示從“DATA”列提取出來的純文本內容，按照同樣的方法繼續添加“純數值”、“不重復數值”兩個自定義列，公式分別為“=Text.Remove（Text.Trim（Text.Remove（[DATA]，{"A".."z"}），" "），" "）”和“=Text.Combine（List.Distinct（Text.ToList（[純數值]）））”，請注意后一個公式的“純數值”必須與上一個自定義列的名稱保持一致，否則會提示“Error”。

關閉查詢編輯器，在提示是否保留更改時，請選擇“保留”，返回Excel主界面之后，就可以看到分離效果（圖8）。