Power query自動偵測資料行類型和標頭,如何更改設定

Power query預設會自動偵測資料類型並且轉換,有些時候也許不需要這個功能。本文介紹如何手動轉換,更改查詢設定,關閉自動偵測資料行類型和標頭的功能。

一、Excel文字數值

Excel在處理資料的時候,如果是較為特殊的情形,例如截圖看到的「數值儲存成文字」,它會出現相關的「說明」視窗,這個在Excel通常不會造成實際問題,不過是使用函數公式的場合,例如VLOOKUP查找核對的時候,要注意到文字跟數字即使內容相同,也有可能會查找不到。

關於Excel Vlookup函數文字數值查找,可以參考贊贊小屋相關文章

Power query自動偵測資料行類型和標頭,如何更改設定 1

二、取得及轉換資料

沿續上個步驟範例資料,保留文字格式的狀態,上方功能區前往「資料」索引標籤,在「取得及轉換資料」工具箱中點選「從表格/範圍」,參考視窗的指令說明,Excel會自動將範圍轉換為表格資料進行處理,因此在這裡會出現「建立表格」的視窗,自動設定的預設值沒有問題,直接按「確定」。

關於Power Query如何取得資料並載入到Excel,可以參考贊贊小屋相關文章

Power query自動偵測資料行類型和標頭,如何更改設定 3

三、自動變更類型

即使原始Excel資料已經設定為文字格式,匯入到Power Query編輯器之後,可以看到仍然數字格式:「123 數字」,其中「123」表示是數值資料,「數字」則是欄位名稱。如果還是希望是文字格式的話,上方功能區預設的「常用」索引標籤中,將「資料類型」下拉,選擇「文字」。

注意到在右下方的「查詢設定」視窗中,目前有兩個操作步驟,第一個是匯入資料的「來源」,第二個是會自動進行的「已變更類型」,表示將文字變更為數字的資料類型。

Power query自動偵測資料行類型和標頭,如何更改設定 5

四、變更類料行類型

由於目前在查詢步驟中已經有一個由Power Query編輯器自動產生的「已變更類型」,依照上個步驟再變更類型時,會跳出「變更資料行類型」視窗,提醒確認是否再次進行變更,在此沒有問題的話直接按下「新增步驟」即可。注意到在和Excel相同作用的資料編輯列裡有個公式,它的作用可想而知是將來源資料轉換為整數類型的數值資料,「Int64.Type」依照微軟系統定正是64 位元帶正負號的整數。

Power query自動偵測資料行類型和標頭,如何更改設定 7

五、text文字資料

手動變更為文字類型之後,可以看到在資料行已經從123變成「ABC」,表示是文字類型資料。另外在Power Query的公式可以和上個步驟互相參照就可以知道「text」表示是文字類型,而「Int64」則是整數的數值資料類型。

Power query自動偵測資料行類型和標頭,如何更改設定 9

六、Power Query設定

第三步驟的Power Query自動轉換類型雖然方便,可是如同這篇文章所看到的,有時候其實並不需要。可以在「檔案>選項及設定」這裡點選「查詢選項」,準備進行相關設定。

Power query自動偵測資料行類型和標頭,如何更改設定 11

七、資料載入偵測

「查詢選項」主要分成「全域」跟「目前活頁簿」,而且兩個部分都有「資料載入」選項,意思是如果「全域」的話將會適用於所有檔案的Power Query編輯器,「目前活頁簿」顯然只適用於目前檔案。另外在「資料載入」這裡有個屬性是「類型偵測」,預設是「一律偵測非結構化來源的資料行類型與標頭」,可以改為「永不偵測非結構化來源的資料行類型與標頭」,如此就不會有本篇文章先前所看到的自動轉換類型的情況。

Power query自動偵測資料行類型和標頭,如何更改設定 13

Power Query資料類型

如同本篇文章一開始所見,Excel其實也有文字和數值資料之間的差異,不過通常Excel的資料處理和資料呈現同樣都是在工作表上,而且Excel執行過程中也會自動偵測並且進行處理,所以通常不致於發生問題,就算有問題也很好處理。然而Power Query是有點像SQL資料庫那樣,它只是單純的取得及轉換資料的工具,處理的資料可以在Power Query編輯器預先處理或者匯入到Excel後續再處理,因此建議使用Power Query的時候,嚴格像在建立SQL資料庫那樣在一開始就處理好資料類型的屬性,避免後續資料呈現有問題的話,還要回過頭到Power Query二次處理比較麻煩。

每天學習,每天充電:Power BI文章合集

Power query自動偵測資料行類型和標頭,如何更改設定 15
加入Line社群,口袋裡的Excel小教室!