在 Excel 中建立 Power Query 公式

Power Query 編輯器提供 Excel 的資料查詢與形狀調整體驗，讓你能從多個資料來源重新塑造資料。 要顯示Power Query 編輯器視窗，請從 Excel 工作表中匯入外部資料來源的資料，選取資料中的一個儲存格，然後選擇查詢 > 編輯。 以下是主要組成部分的摘要。

1. 你用來塑造資料的 Power Query 編輯器功能區

2. 你用來尋找資料來源和資料表的查詢面板

3. 右鍵選單，方便快捷地操作功能區指令

4. 資料預覽顯示對資料所應用步驟的結果

5. 查詢設定窗格，列出屬性和查詢中的每個步驟

在幕後，查詢的每個步驟都基於公式列中可見的公式。

有時你可能會想修改或創造一個公式。 公式使用Power Query公式語言，你可以用它來建立簡單和複雜的表達式。 欲了解更多語法、參數、備註、函式及範例，請參閱 Power Query M 公式語言。

以足球錦標賽列表為例，使用 Power Query 將你在網站上找到的原始資料整理成格式良好的表格。 在 查詢設定 窗格的 「套用步驟 」及 公式欄中，觀察每個任務如何建立查詢步驟及對應公式。

程序

1. 要匯入資料，請選擇「來自網頁的資料 >」，在網址框輸入「http://en.wikipedia.org/wiki/UEFA_European_Football_Championship」，然後選擇確定。

2. 在 導航 器對話框中，選擇左側的 結果[編輯] 表格，然後在底部選擇 「轉換資料 」。 Power Query 編輯器會出現。

3. 要更改預設查詢名稱，請在 查詢設定 欄格的 屬性下刪除「Results [編輯]」，然後輸入「UEFA champs」。

4. 要移除不需要的欄位，請選取第一、第四和第五欄，然後選擇 「首頁 」> 「移除欄位 」> 「移除其他欄位」。

5. 要移除不需要的值，請選擇 第 1 欄，選擇 「首頁 」> 「替換值」，在「尋找值」框中輸入「詳情」，然後選擇 確定。

6. 要移除包含「Year」字樣的列，請在 第 1 欄選擇篩選箭頭，清除「Year」旁的勾選框，然後選擇 確定。

7. 要重新命名欄位標題，請雙擊每個欄位，然後將「Column1」改為「Year」，「Column4」改為「Winner」，「Column5」改為「Final Score」。

8. 要儲存查詢，請選擇 「首頁 」> 「關閉 & 載入」。

結果

下表為每個應用步驟及相應公式的摘要。

查詢步驟與任務	公式
來源 連接網路資料來源	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
瀏覽 選擇連接的表格	=Source{2}[Data]
型號變更 更改資料型別 (Power Query會自動執行)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
移除其他柱子 移除其他欄，僅顯示感興趣的資料欄	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
被替換價值 替換值以清理選取欄位中的值	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
過濾列 欄位中的過濾器值	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
更名柱 將欄位標頭改為有意義	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

重要   編輯來源、導航和變更型別步驟時要小心，因為它們是由 Power Query 建立來定義和設定資料來源的。

顯示或隱藏公式條

公式列預設會顯示，但如果看不到，可以重新顯示。

• 選擇公式列 > > 檢視版面 。

Edit 公式欄中的一個公式

1. 要開啟查詢，先找到先前從Power Query 編輯器載入的查詢，選取資料中的一個儲存格，然後選擇查詢 > 編輯。 更多資訊請參閱 「在 Excel 中建立、載入或編輯查詢」。

2. 在 查詢設定 窗格中，在 「套用步驟」中選擇你想編輯的步驟。

3. 在公式列中，找到並更改參數值，然後選擇 Enter 圖示或按下 Enter。 例如，將此公式改為同時保留 Column2：
   
   Before： = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   After：= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. 選擇 Enter 圖示或按 Enter 鍵，即可在資料預覽中查看新結果。

5. 要在 Excel 工作表中查看結果，請選擇 「首頁 」> 「關閉 & 載入」。

在公式欄中建立一個公式

舉個簡單的公式範例，我們用 Text.Proper函式將文字值轉換成 proper case。

1. 要開啟空白查詢，請在 Excel 中選擇「資料 」> 取得來自其他來源的資料 > > 空白查詢。 更多資訊請參閱 「在 Excel 中建立、載入或編輯查詢」。

2. 在公式列中輸入 =Text.Proper("text value")，然後選擇 Enter 圖示或按下 Enter。
   
   結果會顯示在資料預覽中。

3. 要在 Excel 工作表中查看結果，請選擇 「首頁 」> 「關閉 & 載入」。

結果:

當你建立公式時，Power Query 會驗證公式語法。 然而，當你插入、重新排序或刪除查詢中的中間步驟時，可能會讓查詢失效。 請務必在資料預覽中驗證結果。

重要   編輯來源、導航和變更型別步驟時要小心，因為它們是由 Power Query 建立來定義和設定資料來源的。

使用對話框編輯公式

此方法利用會依步驟變化的對話框。 你不需要知道公式的語法。

1. 要開啟查詢，先找到先前從Power Query 編輯器載入的查詢，選取資料中的一個儲存格，然後選擇查詢 > 編輯。 更多資訊請參閱 「在 Excel 中建立、載入或編輯查詢」。

2. 在 查詢設定 窗格中，在 「套用步驟」中，選擇你想編輯步驟的 「編輯設定 」圖示，或右鍵點擊該步驟，然後選擇 「編輯設定」。

3. 在對話框裡，先做你的修改，然後選擇 確定。

插入一個步驟

完成一個重新塑造資料的查詢步驟後，會在當前查詢步驟下方新增一個查詢步驟。 但當你在步驟中間插入查詢步驟時，後續步驟可能會發生錯誤。 當你嘗試插入新步驟時，Power Query 會顯示「插入步驟」警告，而新步驟會改變欄位，例如欄位名稱，這些欄位會在插入後的步驟中出現。

1. 在 查詢設定 窗格中，在 「套用步驟」中，選擇你想緊接新步驟及其對應公式的步驟。

2. 選擇公式列左側 的「新增步驟 」圖示。 或者，右鍵點擊一個步驟，然後選擇「 插入步驟之後」。 會建立一個格式為 ：
   
   = <nameOfTheStepToReference>的新公式，例如 =Production.WorkOrder。

3. 請使用格式輸入新公式：
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   舉例來說，假設你有一個欄位為「性別」的資料表，你想新增一欄，值為「Ms.」或「Mr.」，依據個人的性別而定。 公式會是：
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

重新排序一個步驟

• 在 查詢設定 欄裡的 「已套用步驟」裡，右鍵點擊步驟，然後選擇 「移動前 」或 「移動後」。

[刪除步驟]

• 選擇步驟左側的 刪除 圖示，或右鍵點擊步驟，然後選擇 刪除 或 刪除直到結束。 刪除 圖示也位於公式列左側。

在這個範例中，我們用進階編輯器中的公式組合，將欄位中的文字轉換成正確的大小寫。

舉例來說，你有一個 Excel 表格，叫做 Orders，裡面有一欄 ProductName，你想把它轉換成正確的案例。

之前：

之後：

當你建立進階查詢時，你會根據 let 表達式建立一系列查詢公式步驟。 使用 let 表達式來指派名稱並計算值，然後由 in 子句引用，該子句定義了 Step。 此範例回傳的結果與「在公式欄中建立公式」區塊相同。

let
Source = Text.Proper("hello world")
in
Source

你會看到每個步驟都是建立在前一個步驟的基礎上，透過名稱來參考。 提醒一下，Power Query 公式語言是區分大小寫的。

第一階段：開啟進階編輯器

1. 在 Excel 中，選擇「資料 」> > 空白查詢> 取得其他來源的資料。 更多資訊請參閱 「在 Excel 中建立、載入或編輯查詢」。

2. 在Power Query 編輯器中，選擇 進階編輯器 的 Home >，該頁面會以 let 表達式的範本開啟。

第二階段：定義資料來源

1. 使用 Excel.CurrentWorkbook 函式建立 let 表達式，內容如下：
   
   let
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
   Source
   
   

2. 要將查詢載入工作表，請選擇 完成，然後選擇 主頁 > 關閉 & 載入 > 關閉 & 載入。

結果:

第三階段：將第一列升格為標頭

1. 要開啟查詢，從工作表中選取資料中的一個儲存格，然後選擇 查詢 > 編輯。 欲了解更多資訊，請參閱「在 Excel 中建立、載入或編輯查詢」 (Power Query)。

2. 在Power Query 編輯器中，選擇「> 進階編輯器 的 Home ，該頁面會以你在第二階段建立的陳述：定義資料來源」開頭。

3. 在 let 表達式中，新增 #“First Row as Header”，並使用 Table.PromoteHeaders 函式如下：
   
   let
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
   #"First Row as Header" = Table.PromoteHeaders(Source)
   in
   #"First Row as Header"

4. 要將查詢載入工作表，請選擇 完成，然後選擇 主頁 > 關閉 & 載入 > 關閉 & 載入。

結果:

第四階段：將欄位中的每個值改為適當的大小寫

1. 要開啟查詢，從工作表中選取資料中的一個儲存格，然後選擇 查詢 > 編輯。 更多資訊請參閱 「在 Excel 中建立、載入或編輯查詢」。

2. 在Power Query 編輯器中，選擇「> 進階編輯器 Home 」，該頁面會以你在第三階段建立的語句開頭：將第一列升遷為標頭。

3. 在 let 運算式中，使用 Table.TransformColumns 函式將每個 ProductName 欄位值轉換為正式文字，參考先前的「First Row as Header」查詢公式步驟，將 #“Capitalized Each Word” 加入資料來源，然後對結果指派 #“Capitalized Each Word”。
   
   let
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
   #"First Row as Header" = Table.PromoteHeaders(Source),
   #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
   #"Capitalized Each Word"

4. 要將查詢載入工作表，請選擇 完成，然後選擇 主頁 > 關閉 & 載入 > 關閉 & 載入。

結果:

你可以在 Power Query 編輯器中控制所有工作簿的公式列行為。

顯示或隱藏公式條

1. 選擇 檔案 > 選項與設定 ，> 查詢選項。

2. 在左側窗格的 GLOBAL，選擇 Power Query 編輯器。

3. 在右側選區，在 版面配置下，選擇或清除 「顯示公式列」。

開啟或關閉 M Intellisense

1. 選擇 檔案 > 選項與設定 ，> 查詢選項 。

2. 在左側窗格的 GLOBAL，選擇 Power Query 編輯器。

3. 在右側窗格的 「公式」中，選擇或清除公式 列中的「啟用 M Intellisense」，並選擇進階編輯器及自訂欄位對話框。

注意事項    更改此設定會在下次你開啟 Power Query 編輯器視窗時生效。