贊贊小屋

Power Query分割資料行擷取長度,整理證交所統計表

Power BI中的Power Query是專門進行大數據資料處理的工具。本文以證交所的證券交易統計表為例,介紹依分隔符號分割資料行、複製資料行、擷取資料長度等操作。

實務工作中ERP系統導出來的報表常常會加了很多不必要的東西,以前往往在Excel先做資料整理再接著進行資料分析。現在既然有了Power BI這套工具,可以藉助其中Power Query編輯器進行相對較專業的整批資料清洗。證交所提供的證券交易統計表剛好和一般ERP系統報表結構類似,本文即以證券交易統計表為例,介紹Power Query有哪些好用的資料清洗工具。

一、證券交易統計表

台灣證券交易所網站上前往:「首頁\交易資訊\統計報表\市場交易月報」,下載「109/05」的「【證券交易統計表】月報」。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第1張

二、下載檔案

所取得的一個壓縮檔,請自行解壓縮,內容是一個Excel檔案。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第2張

三、Power Query取得資料

上方功能區依序前往:「資料\取得及轉換資料\取得資料\從檔案\從活頁簿」。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第3張

四、匯入資料

匯入第二步驟解壓縮的Excel檔案。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第4張

五、導覽器

從預覽畫面可見雖然只有一個工作表,Power Query把工作表上的表格也當作是一個資料對象。另外也可以看到這份原始資料的格式不是那麼適合直接分析,匯入資料前最好預先處理。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第5張

六、使用第一個資料列作為標頭

首先處理標題欄。顯然所匯入的原始資料第2列比較適合,用先前文章介紹過的「使用第一個資料列作為標頭」指令,它的路徑位於上方功能區的「常用\轉換」。連按兩次就會將「證券名稱」這一列移到Power Query編輯器目前查詢的標頭。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第6張

七、分割資料行

仔細觀察「證券名稱」這一欄,它有一些不必要的雜質,這些是可以連同報表一整列去掉的,而且它把股票代碼和股票名稱連在一起,例如「1101 台泥」,因此前往「常用\轉換\分割資料行」,下拉後選擇「分割符號」:「依分隔符號分割資料行」,準備把這一欄裡面的股票代碼和股票名稱切開。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第7張

八、依分隔符號分割資料行

依照上個步驟對於原始資料的觀察,下拉「選取或輸入分隔符號」選擇「空格」,在此只要分割一次就夠了,打算從左邊開始切,所以在「分割處」點選「最左邊的分隔符號」,設定好了按「確定」。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第8張

九、複製資料行

分割好了之後,雖然成功將股票代碼和股票名稱分開,但是可以看到目前查詢資料的第1欄仍然不太乾淨,上方功能區操作「新增資料行\一般\複製資料行」,準備複製後以這一欄的資料型態進一步處理。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第9張

十、擷取長度

Power Query會自動把複製的資料移到査詢表格最後面一欄,依照證交所編碼原則,公開發行公司股票為四位數字股票代號,所以這裡其實想要的資料列是四位數字項目,其餘都是不需要的。在上方功能區把「新增資料行\從文字\擷取」下拉,選擇「長度」,打算「依照所選資料行中值的字串長度」進一步篩選。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第10張

十一、長度欄位

擷取之後成功得到「長度」欄位,用先前文章介紹過的方法篩選「4」。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第11張

十二、排除篩選

上個步驟是選擇性的篩選之後,移到前面查詢表格第二欄「證券名稱.1」,果然只有長度為4的項目,不放心再篩選看看,最下方還有一些長度為4的英文及中文字串,這顯然也不是想要的,直接點掉。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第12張

十三、關閉並載入

至於資料已經清理乾淨,上方功能點選「檔案」索引標籤,「關閉並載入」。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第13張

十四、證券交易統計表

終於在Excel得到了很適合進行分析的資料報表了。

Power Query分割資料行擷取長度,整理證交所統計表 Power BI 第14張

Excel資料整理和Power Query資料清洗

這一節是Power BI中Power Query資料處理的第一節,經過一連串成功的範例操作,大概可以知道Power Query在上方功能區有相當多關於資料處理的配套指令,有些是和Excel相同類似的功能,例如篩選和資料剖析,有些則是Power Query特有的功能,例如新增資料行和擷取長度。

這一節範例其實也可以把整個原始資料倒到Excel做清洗,不過既然要開始使用Power BI,應該善用這個新工具。把資料清理的過程留在Power Query編輯器進行。微軟特別再開發了這套工具,Excel有的東西大概Power BI也會有,而且Power BI會有一些Excel無法簡單辦到的東西,簡單而言,Excel是比較手工的資料整理,Power Query是先進的資料清洗,後續章節會繼續介紹Power Query各項先進功能。



本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1