在 Excel 中建立 Power Query 公式

Power Query 編輯器提供資料查詢和重塑Excel，您可以使用它重塑來自許多資料來源的資料。 若要顯示 Power Query 編輯器視窗，請在工作表中從外部資料源Excel，選取資料中的儲存格，然後選取查詢>編輯。 以下是主要元件的摘要。

1. 您用於圖形資料的 Power Query 編輯器功能區

2. 您用於尋找資料來源和表格的查詢窗格

3. 操作功能表，方便使用功能區中命令的快捷方式

4. 顯示適用于資料之步驟結果的資料預覽

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

在幕後，查詢中的每個步驟都是以資料編輯列可見的公式為基礎。

有時候，您可能會想要修改或建立公式。 公式使用 Power Query 公式語言，您可以使用它來建立簡單和複雜的運算式。 有關語法、引數、備註、函數和範例詳細資訊，請參閱 Power Query M 公式語言。

使用足球錦標賽清單做為範例，使用 Power Query 來收集您在網站上找到的原始資料，並轉換成格式良好的表格。 觀看如何在已應用步驟和資料編輯列的查詢設定窗格中，針對每個工作建立查詢步驟和對應的公式。

程序

1. 若要匯出資料，請選取>網頁資料，在URL方塊中 HTTP://en.wikipedia.org/wiki/UEFA_European_Football_Championship 「HTTP://en.wikipedia.org/wiki/UEFA_European_Football_Championship」，然後選取 「確定」。

2. 在 [ 導航器] 對話方塊中，選取左側的 [結果 [編輯] 資料表，然後選取底部的 [轉換 資料]。 Power Query 編輯器即會顯示。

3. 若要變更預設查詢名稱，請在 [查詢設定] 窗格中的 [屬性] 下，刪除「結果 [編輯]」，然後輸入「UEFA 的優等專案」。

4. 若要移除不想要的欄，請選取第一欄、第四欄和第五欄，然後選取>移除欄>移除其他欄。

5. 若要移除不想要的值，請選取欄1，選取>取代值，在 「尋找值」 方塊中輸入「詳細資料」，然後選取 「確定」。

6. 若要移除包含「年份」一詞的列，請選取欄 1中的篩選箭號，清除 「年份」旁的核取方塊，然後選取 「 確定」。

7. 若要重新命名欄標題，請按兩下每一個標題，然後將 "Column1" 變更為 "Year"，"Column4" 變更為 "優勝者"，而 "Column5" 變更為 "Final Score"。

8. 若要儲存查詢，請選取>關閉&載入。

結果

下表是每個已應用步驟及對應公式的摘要。

查詢步驟和工作	公式
Source 連線網頁數據源	= 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 建立，以定義及   設定資料來源。

顯示或隱藏資料編輯欄

資料編輯欄預設會顯示，但如果沒有顯示，您可以重新顯示資料編輯欄。

• 選取在資料編輯>中>版面配置。

在編輯欄中編輯公式

1. 若要開啟查詢，請找出先前從 Power Query 編輯器載入的查詢，選取資料中的儲存格，然後選取查詢>編輯。 詳細資訊請參閱在 Excel中建立、載入或編輯Excel。

2. 在查詢設定窗格中，選取要編輯的步驟。

3. 在資料編輯欄中，找出並變更參數值，然後選取 Enter 圖示或按 Enter。 例如，將這個公式變更為同時保留
   
   Column2：Before： = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   之後：= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. 選取輸入 圖示，或按 Enter 以查看資料預覽中顯示的新結果。

5. 若要在工作表中查看Excel結果，請選取>關閉&載入。

在資料編輯欄中建立公式

對於簡單的公式範例，讓我們使用 Text.Proper函數將文字值轉換成適當的大小寫。

1. 若要開啟空白查詢，請Excel選取資料>取得資料>從其他來源>空白查詢。 詳細資訊請參閱在 Excel中建立、載入或編輯Excel。

2. 在資料編輯欄中，=Text.Proper("text value")，然後選取輸入 圖示或按 Enter。
   
   結果會顯示在資料預覽中。

3. 若要在工作表中查看Excel結果，請選取>關閉&載入。

結果：

 當您建立公式時，Power Query 會驗證公式語法。 不過，當您在查詢中插入、重新排序或刪除中間步驟時，可能會中斷查詢。  一直在資料預覽中驗證結果。

重要    請小心編輯來源、流覽和變更類型步驟，因為它們是由 Power Query 建立，以定義及   設定資料來源。

使用對話方塊編輯公式

此方法會使用視步驟而異的對話方塊。 您不需要知道公式的語法。

1. 若要開啟查詢，請找出先前從 Power Query 編輯器載入的查詢，選取資料中的儲存格，然後選取查詢>編輯。 詳細資訊請參閱在 Excel中建立、載入或編輯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.")
   

重新排列步驟

• 在 [查詢設定窗格的[已應用步驟> 下，以滑鼠右鍵按一下步驟，然後選取[上移或下移> 。

[刪除步驟]

• 選取步驟的 [刪除或刪除檔圖示>，或以滑鼠右鍵按一下步驟，然後選取[刪除或刪除直到結束。 資料 編輯 左側也提供 Delete 圖示。

在此範例中，讓我們使用進位編輯器中的公式組合，將欄中的文字轉換成適當的大小寫。 

例如，您擁有一個Excel名為 Orders 的索引表，其產品名稱欄要轉換成適當的大小寫。 

之前：

後：

當您建立進位查詢時，會根據 let 運算式建立一系列查詢公式步驟。 使用 let 運算式來指定名稱，並計算 in 子句所參照的值，而 in 子句會定義步驟。 此範例會返回與「在資料編輯欄中建立公式」區段相同的結果。

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

您會看到每個步驟都參照一個逐一名稱的步驟，以上一個步驟建立。 提醒一下，Power Query 公式語言會區分大小寫。

階段 1：開啟進位編輯器

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

2. 在 Power Query 編輯器中，選取>進>編輯器，該編輯器隨即開啟，其中會包含 let 運算式的範本。

階段 2：定義資料來源

1. 使用運算式建立let運算式Excel。CurrentWorkbook函數如下所示
   
   ：let    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]in
   
   
       
   
   Source

2. 若要將查詢載入至工作表，請選取完成 ，然後選取>關閉&載入>載入& 。

結果：

階段 3：將第一列升級為頁首

1. 若要開啟查詢，請從工作表選取資料中的儲存格，然後選取查詢>編輯。  詳細資訊請參閱在Power Query中建立、載入或編輯Excel (查詢) 。

2. 在 Power Query 編輯器中，選取>進>編輯器，該編輯器隨即開啟，其中會包含您在第 2 階段中建立的聲明：定義資料來源。

3. 在let 運算式中，新增 #"第一列做為頁首」和Table.PromoteHeaders函數，
   
   如下所示
   ：let     
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   
   #"First Row as Header" = Table.PromoteHeaders(Source)in    #"First Row as Header"

4. 若要將查詢載入至工作表，請選取完成 ，然後選取>關閉&載入>載入& 。

結果：

階段 4：將欄中的每個值變更為適當的大小寫

1. 若要開啟查詢，請從工作表選取資料中的儲存格，然後選取查詢>編輯。  詳細資訊請參閱在 Excel中建立、載入或編輯Excel。

2. 在 Power Query 編輯器中，選取>進位編輯器，此編輯器隨即開啟，其中會以您在第 3 階段中建立的聲明：將第一列升級為頁首。

3. 在let 運算式中，使用Table.TransformColumns函數將每個 ProductName 欄值轉換成適當的文字，參照上一個「第一列做為標題」查詢公式步驟，將 #"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 編輯器視窗時，變更此設定將會生效。