Power Query 轉換是數據處理的重要過程,轉換工作開始之前要先確定好自己的目標,你需要得到什麼樣的結果。其實轉換工作的實質就是:
- 無序變有序
- 混亂變統一
有了目標之後,就是選擇適當的轉換方法,這個有點和Excel寫公式相近,同一個問題可能會有很多種公式的寫法,每個人都有自己一個習慣,有的高手認為,一步搞定,公式越短越顯功力,有的則分步完成,同樣能得到結果。所以只要解決問題,你的方法就是對的。
轉換方法,大的方向有三個:
- 以少生多
- 以多變少
- 形狀變換
具體到操作上:
- 以少生多:拆分列、添加列
- 以多變少:刪除行、刪除列、篩選行
- 形狀變換:透視列、逆透視列
Power Query 轉換操作的基本約定:
- 所有轉換操作都在編輯器中進行
- 查詢可以重複使用
- 列可以增加、可以減少
- 行不能增加、可以減少
- 使用M函數的時候,注意首字母要大寫
我們來看一個例子:從天氣網站上獲取城市列表的轉換過程:
左邊是我們的目標表格,右變是網頁的截圖,看起來很整齊,但是如果你直接複製到Excel中就是一團漢字,拆都拆不開。看看源碼的樣子,也是很頭疼
98行是我們要的數據,看起來也是很亂。
Power Query數據處理過程的第一步是建立連接,從網頁建立連接是可以的,但是每次都要從網站抓取,不是很方便,我們可以源代碼複製到文本中,然後從文本建立連接。
轉換開始
第一步:修改源的設置
默認的是HTML格式的,需要修改成文格式
第二步:減法去掉多餘的內容
我們複製到文本的時候多了一行空白,變成99行了,保留99行,其他行都去掉。
第三步:加法分列
省直轄市是h2字體,我們用<h2>做符號分列,一列變多列。
第四步:變換轉置:行變列
因為後續的出列還要繼續拆分,放在列拆分比較方便。
第五步:加法繼續拆分列
HTML中標誌都是成對出現的<h2>,</h2>,所以我們再用</h2>拆分一次,就把省直轄市與區縣分開。
第六步:準備分列字符
用逗號替換</a>,逗號就是後續分類用的符號
第七步:減法去掉多餘的字母與字符
使用Text.Remove函數去掉多餘字符,保留漢字。添加兩個自定義列
省市=Text.Remove([Column1.1],{"a".."z","<","=","/",">","0".."9"})
區縣=Text.Remove([Column1.2],{"a".."z","<","=","/",">"," ", "-","0".."9"})
這裡使用了M函數,要首字母大寫,至於刪除什麼符號,要根據具體情況,適當添加,刪減符號,當然不能把逗號也remove了,否則我們做的分列符號就白做了。
第八步:減法去除多餘的列,與空行
第九步:加法拆分列
看見了麼,我們預留的逗號在這一步發揮作用了。
第十步:變換逆透視列
選第2列,按SHIFT+END選中除第一列外的後面所有列,然後按逆透視按鈕。
第十一步:減法刪除列,篩選空白行
到這裡轉換工作就全部完成了。
其實我也不是一下子就全部做好,中間會有些問題,發現問題在去修正,例如自定列的時候,去除空格和短劃線,就是在後來的檢查中發現,縣市的裡面有些有多餘的空格和短劃線,糾正這個問題,可以添加兩步字符替換,也可以在Text.Remove函數中直接去除。
Power Query轉換操作,就是這樣不斷的找規律,加加減減,最後轉換成你需要的結果。