[Excel][PowerQuery]PowerQuery 的初體驗 - 大型 XML 和 Web 表格的處理
前一陣子參加 SQL Pass 聚會的時候，聽到尹相志有介紹新的 Power 家族系列的新成員 – Power Query，可以做到個人版本的 ETL 工具，讓 Excel 可以更便捷的去讀取一些外部資料來源，並且將資料作整理和匯整之後，變成 Table 的樣式讓 Excel 作後續的資料分析。本來想說那就是個簡單版本的 SSIS ，沒有太在意他會帶來甚麼特別的功效，也就不是那麼注意。而就那麼剛好，這幾天有個朋友，給了我一個 XML 檔案，因為這個 XML 檔案有 90MB，算是蠻大的資料檔案，加上欄位又是有複合型式，原本先使用 Excel 直接去開啟，但開了將近 30mins 還是一值在那裏轉圈圈；而如果要使用 SSIS 來處理，又有點像是拿開山刀來切水果，因此想說就來試試看 Power Query 是否真如傳說中的那麼好用。
Power Query 是今年新推出的，可以搭配 Excel 2010 和 Excel 2013 的版本來使用，因為這個是屬於新增功能，並非內建在 Excel 內的，因此必須要上網下載檔案 ( 網址 : http://www.microsoft.com/zh-tw/download/details.aspx?id=39933 )。目前已經有各國語系的版本了，下載的時候要注意您的使用環境和 Excel 的版本來做挑選。如果您的電腦環境上還是使用 IE 8 或者是之前的版本，則安裝的時候會跳出如下的訊息，因此只好先升級到 IE 9 之後，再來繼續安裝囉。
基本上整個安裝過程幾乎都是 「下一步」就可以完成了，如果您的電腦性能沒有特別的不佳的狀況下，應該都可以在三分鐘內完成整個安裝步驟。
安裝好之後，我們就可以直接開啟 Excel 來看看，果然在選單上多了一個 POWER QUERY 的選項了
因此就拿那個非常大的 XML 來試車一下囉，在 POWER QUERY 下面我們選擇 「從檔案」→「從 XML」
因為這個 XML 大了一點，加上我又是在一個測試的虛擬環境下使用，因此差不多花了 2 mins 的時間載入檔案，載入後會進入到「查詢編輯器」。在這個視窗中有兩個很重要的部分，第一個部分是呈現出預計處理的結果，而針對這個部分你可以配合上方的各項功能做調整和設定，而所有的設定處理的每個步驟，都會紀錄在最右邊有個「套用的步驟」，因此如果設定過程中如果發覺設定錯誤，或者是要做改變，可以直接把任何的一個步驟給刪除，使用起來非常的方便。
首先我先展開根結點，在欄位名稱上點開就可以看到其有包含的欄位，一開始我先全選把所有的 NODE 都先載入
當完成上述步驟的時候，會發現在右邊的「套用的步驟」內已經根據剛才的步驟，多了一個 「Expand 法規」，由於這個 XML 內的資料有點多，因此就像使用 Excel 的習慣般，接下來我們在欄位名稱上有向下的三角符號的位置，可以做一些過濾條件，這裡我先按照我所想到的先過濾取得所有「法律」類的資料。
接下來我們再針對其他的欄位作過濾，取得所有「個人資料保護法」的相關條文資訊。
也可以把一些不需要使用的欄位設定「移除資料行」
因為條文內容是 Sub Node，因此類似一開始的步驟，將條文的結點給展開
果然如我們所想要的就只剩下我們所需要的所有個資法的相關條文資訊
接下來可以再把一些不需要的欄位給去除之後，就可以按下左上方的「套用並關閉」的按鈕，將資料給帶回到 Excel 內使用了。
當然除了針對 XML 這樣的格式可以讓我們很方便的就取出資料，像如果我們要的一些是網頁上的資訊，以往都要想辦法用程式去 Parse，現在如果使用 Power Query，也是另外一種不錯的選擇，我拿棒球維基上的資料來作範例。比方說我想分析一些球員的資料，我可以先用瀏覽器找到後，紀錄連結的網址
這個時候我利用 POWER QUERY 選擇透過 「從 WEB 」，將剛剛從瀏覽器上所記錄的網址給貼上。
確定後可以透過「導覽器」看到網址內相關的標格資訊，我們可以點選每一個看看哪個才是我們所要的資料。
確定好是哪個表格的資訊之後，就可以在那個表格按下滑鼠右鍵，選擇「編輯查詢」
接下來我們可以把一些不必要的欄位給刪除
就可以很容易取得我們所想要的資料了
除了這些基本的操作之外，POWER QUERY 還有很多不錯的功能，像是去除重複，只取某部分的資料等功能。另外微軟也針對 POWER QUERY 製作了一個 M 的 Script Language，提供非常多的功能，讓我們可以透過這樣的一個工具，很容易的協助 Excel 取得相關需要進行分析的資料，而接下來就可以再配合 POWER PIVOT 的處理和 POWER VIEW 去呈現資料，讓個人化的 BI 可以更上一層樓。