Excel Power Query入門介紹-第一次學必看！

Excel Power Query是一個可連結多種資料來源、進行數據處理ETL的編輯器。只要是資料表(Table)，不管是存在於同一個Excel檔案，或是在不同路徑的Excel檔案中，Power Query都可以將它們合併進行數據處理。另外Power Query最大的優點是其擁有絕佳的使用者介面，一般不具備後端(Back-End)經驗的人都可以很快上手，進行資料處理。

Power Query歷史簡介 什麼是Power Query？ Excel Power Query基礎實用範例 1.連結資料來源 2.轉換資料 3.下載Query回Excel-僅建立連結 4.繼續連結其他資料來源 5.合併Query 6.下載Query回Excel-建立資料表 總結與補充

Power Query歷史簡介：

Power Query早在2010年以前就已經存在於Microsoft公司內部的軟體應用，直到2010年後才開放到Excel之插件(add-in)，Excel版本為2010或2013的電腦需下載插件使用(畫面如下)，但2010及2013版本Excel中之Power Query已於2019年停止更新。

Power Query在Excel 2016版後已變成內建工具，2019版、2021版及Microsoft 365版之顯示位置、操作功能幾乎相同，主要差別在於Excel 2019版的Power Query資料來源不包含PDF檔案。(Power Query功能區位於下圖紅框區)

什麼是Power Query？

Power Query顧名思義就是很強的Query，那什麼是Query呢？Query原屬於後端資料庫領域，Query的中文為查詢，其包含一串程式碼，代表著你對資料庫(Database)發出的一個詢問，例如你想要取得某資料表的某些欄位，就可以寫一個Query來得到，其原理與上篇文章介紹的數據處理ETL相近(還沒看過的朋友可以先去看看)。下圖為Google搜尋到，以資料庫語言MySQL所寫的一個Query範例給大家參考，此Query的意思是：查詢在員工(employees)資料表中員工報到日期(hire_date)在2000/01/01之後的清單。

資料庫、Query等名詞一般出現於後端(如公司資訊處)，所以一般辦公室同仁或前端用戶會覺得很陌生，其實資料庫的概念沒那麼複雜，資料庫就是由1個或多個資料表(Table)所組成，需至少具備儲存資料、寫入資料、修改資料等功能，所以其實Excel中的資料表也可以當作資料庫使用，此說詞亦於LinkedIn上討論過(如下圖)。

Excel Power Query因為可連結Excel資料表或工作表(Sheet)、後端資料庫、網頁資料表，甚至PDF資料表，所以可進行相當廣泛的應用，它可以幫你設定一套自動化資料處理(清理)的流程，舉個簡單的例子如：連結Sheet1 -> 刪除最上面兩列資料 -> 將某欄位篩選去除空白(Blank) -> 去除某些不需要的欄位 -> 載回Sheet2。

多說不如實做，接下來小編將介紹一個較基本的Power Query應用範例給大家看，其也是業界中使用頻率最高的一種用法。

Excel Power Query基礎實用範例：於辦公室裡，設定與其他同事們電腦路徑的Excel資料表進行連結，並可自動彙總於某經辦人員的Excel檔案中。

此範例為某工廠廠務室同仁每月要彙總各課向會計報銷費用的資料，因不想要每月向各課經辦要資料，再手動Copy回自己的Excel檔案，故利用Excel Power Query的功能建立Query(查詢)，將各課的資料表下載回自己的檔案裡，以後只要更新此Query，即可得到各課最新的資料。

下圖為此範例示意圖，最後需將兩個路徑(D:共享1-陳氏心及D:共享2-阮德孝)的會計報銷費用資料表自動結合起來。

以下將依操作流程進行說明：

1.連結資料來源(Data Source)：

由Tab Data裡點選Get Data–>From File–>From Excel Workbook。

選擇路徑D:共享1-陳氏心後，點選Excel檔案，進行載入(Import)。

接著會跳出一個瀏覽(Navigator)視窗，其中資料夾圖示(Icon)代表的是Excel檔案，資料夾裡面包含了資料表(Table)及工作表(Sheet)兩種資料，我們一般會選擇資料表類型，故點選陳氏心資料表，再點選轉換資料(Transform Data)後，接著會自動進入Power Query Editor。

2.轉換資料(Transform Data)：

下圖為Power Query Editor畫面，再繼續操作前，小編先簡單介紹一下此編輯器的介面：

(1)此編輯器共有4個Tab，其中所有的資料轉換、資料合併等功能皆在Tab Home、Transform及Add Column中進行；Tab View則是顯示選項設定，在文章最後會介紹一個不錯的功能給大家。

(2)在編輯器右方有Query Settings介面，其包含上方的Query的名稱，下方的Query的步驟(Steps)，步驟順序為從上到下，各位可以看到第1個步驟為Source(代表我們選取的路徑檔案)，第2個步驟為Navigation(代表我們選擇了陳氏心資料表，第3個步驟為Changed Type(剛載入Power Query時編輯器會自動偵測資料類型)。

(3)資料轉換完成後，點選Close & Load即可回到Excel視窗中繼續選擇下載方式。

因為此範例中陳氏心Query不需再進行其他轉換，故可以直接點選Close & Load下載回Excel中。

3.下載Query回Excel-僅建立連結：

點選Close & Load後，會出現兩個選項給你選擇，小編建議新的Query皆點選Close & Load To，這樣可接著進行選擇下載方式；若點選Close & Load，Excel會直接載入資料表到新的工作表A1儲存格(當然之後還可以修改) 。

若不是新的Query，就只能選擇Close & Load。

回到Excel畫面後，會出現載入資料視窗給你選擇，大致分為3種載入方式：(如下圖)

1.資料表(Table)：為預設值

2.樞紐分析表(PivotTable)

3.僅建立連結(Only Create Connection)

因陳氏心資料表並非我們最終要的資料(只是中間段資料表)，故我們點選Only Create Connection。

建立資料連結後，Excel畫面右方會自動出現Queries & Connections視窗(如下圖)，這裡可以看到此Excel工作簿的所有資料連結，要打開此視窗也可以從Tab Data–>Queries & Connections中打開，建議大家養成習慣打開此視窗，因為若有某個Query出現異常，畫面會顯示驚嘆號提醒你進行檢查。

4.繼續連結其他資料來源：

因為我們最終是要合併陳氏心資料表及阮德孝資料表，故我們用同樣的方式將阮德孝資料表建立資料連結，完成後畫面如下。

5.合併Query：

接著要回到Power Query Editor進行資料表轉換，最快的方式就是於上圖視窗中，滑鼠點擊兩下任一個Query即可進入。

將兩個資料表合併的功能為Append Queries(於Tab Home中)，點選後會出現兩個選項：Append Queries as New及Append Queries ，此範例應點選Append Queries as New，意即再建立一個新的Query；若點選Append Queries則接下來的Query步驟會在陳氏心Query中進行。

Append視窗自動開啟後，選擇Second table為阮德孝，再按OK；若是有多個資料表要合併，則視窗上方要點選Three or more tables。

合併後你會發現編輯器左側Queries欄位出現了一個新的Query(叫做Append1)，編輯器中間資料表為陳氏心及阮德孝Query合併後的資料表(共有6列)。

另外要特別提醒一下，使用合併功能的前提是兩個資料表表頭(Header)名稱要完全一樣(順序可以不同)，才可進行合併。

接著將Append1這個Query重新設定一個名稱為：會計費用報銷統計表。

6.下載Query回Excel-建立資料表：

因無需再進行其他資料轉換，故可以直接點選Close & Load To，回到Excel中之載入資料視窗。

在視窗中載入方式選項裡選擇資料表(Table)，及選擇要將資料表放在Sheet1之B2儲存格中。

點選OK後，合併後的資料表即出現在Sheet1的B2儲存格中，大家還可以看到在右側的Queries & Connections視窗中，會計費用報銷統計表是顯示6 rows loaded。

未來只要點擊更新圖示，可自動更新陳氏心及阮德孝的資料。(或可於左方資料表範圍內任一儲存格點擊滑鼠右鍵，點選Refresh更新)

到這邊已算完成了本篇文章的案例。

最後再呼應一下文章前面所提的Power Query Editor中Tab View裡的好用的功能介紹(如下圖)，進入Tab View後，點擊叫出Query Dependencies視窗，此視窗顯示了此Excel檔案中所有Query的相互關係，及載入資料的路徑，小編認為此功能很方便，例如在向其他同事說明的時候可以把它叫出來一併著看。

總結及補充：

講述完了Excel Power Query的基本操作，第一次接觸的朋友可能會覺得有點複雜，但相信在實際操作後會很快上手。

Power Query還有許多實用的功能，包含像樞紐化(Pivot)、反樞紐化(UnPivot)、以某欄位進行集計(Group By)，或文字處理等等，未來小編將陸續進行介紹。(請參考此新文章介紹Power Query樞紐及反樞紐)

大家不要誤以為Power Query是屬於Excel，Power Query也應用於其他軟體中，如Power BI。另針對雲端資料轉換的部分，Microsoft有於Power Apps平台上開發Dataflow軟體(即為Power Query雲端版)。

Power Query有一套自己的程式語言叫「M語言」，一般使用者雖然不用特別去學，但可以學著去看懂它的公式組成，小編認為最好用的就是參數的功能，可以將一個Query的結果做為另一個Query的參數(如日期、比較值等等)，以達到更進階之自動化設定。