【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query




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

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

一、證券交易統計表

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第1張

二、下載檔案

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第2張

三、Power Query取得資料

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第3張

四、匯入資料

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第4張

五、導覽器

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第5張

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

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第6張

七、分割資料行

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第7張

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

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第8張

九、複製資料行

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第9張

十、擷取長度

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第10張

十一、長度欄位

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第11張

十二、排除篩選

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第12張

十三、關閉並載入

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的Power Query Power BI 第13張

十四、證券交易統計表

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

【Excel Power BI】證券交易所的證券交易統計表需要整理?交給專業的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各項先進功能。




當前文章延伸閱讀:
Power BI