在 Excel 中建立 Power Query 公式

Power Query 編輯器 為 Excel 提供數據查詢和重塑體驗，讓您可以用來重新重塑來自許多數據源的數據。 若要顯示 Power Query 編輯器 視窗，請從 Excel 工作表中的外部數據源匯入數據、選取數據中的單元格，然後選取 [查詢 > 編輯]。 以下是主要元件的摘要。

1. 您用來重塑資料 Power Query 編輯器 功能區

2. 您用來找出資料來源和資料表的 [查詢] 窗格

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

4. 顯示套用至數據之步驟結果的數據預覽

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

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

有時候您可能會想要修改或建立公式。 公式使用 Power Query 公式語言，可用來建立簡單又複雜的表達式。 如需語法、自變數、批注、函數及範例的詳細資訊，請參閱 Power Query M 公式語言。

以足球賽事清單為例，使用 Power Query 來擷取您在網站上找到的原始數據，並將它轉換成格式化完善的表格。 觀看如何在 [套用步驟] 底下的 [ 查詢設定 ] 窗格和數據編輯列中，為每個工作建立查詢 步驟和對應 公式。

程序

1. 若要匯入數據，請選取 [數據 > 從 Web]，在 [URL ] 方塊中輸入「http://en.wikipedia.org/wiki/UEFA_European_Football_Championship」，然後選取 [ 確定]。

2. 在 [ 導覽] 對話框中，選取左側的 [ 結果 [編輯] 數據表，然後選取底部的 [ 轉換數據 ]。 隨即出現 Power Query 編輯器。

3. 若要變更預設查詢名稱，請在 [ 查詢設定] 窗格的 [ 內容] 底下，刪除 [結果 [編輯]]]，然後輸入 “而且 後方參閱]。

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

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

6. 若要移除其中含有「年」字樣的列，請選取 [欄1] 中的篩選箭號，清除 [年份] 旁的複選框，然後選取 [ 確定]。

7. 若要重新命名欄標題，請按兩下每個標題，然後將 「欄1」變更為「年份」、「欄4」變更為「優勝者」，以及「欄5」變更為「最後分數」。

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

結果

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

查詢步驟和工作	公式
來源 線上至 Web 數據源	= 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 a formula in the formula bar

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 以查看 [數據預覽] 中顯示的新結果。

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

在數據編輯列中建立公式

例如，讓我們使用 Text.Proper函數，將文字值轉換為適當的大小寫。

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

2. 在數據編輯列中輸入=Text.Proper("text value")，然後選取 [輸入] 圖示或按 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 表格，其中有一個 [產品名稱] 欄，您想要轉換為適當的大小寫。 

之前：

後：

當您建立進階查詢時，您會根據 let 運算式建立一系列查詢公式步驟。 使用 let 表達式來指派名稱，並計算由 in 子句所參照的值，該子句會定義步驟。 此範例會傳回與[在數據編輯列中建立公式] 區段中的相同結果。

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

您會看到每個步驟都是根據上一個步驟建立，方法是依名稱參照一個步驟。 提醒您，公式語言 Power Query 會區分大小寫。

階段 1：開啟 進階編輯器

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

2. 在 Power Query 編輯器 中，選取 [首頁 > 進階編輯器，該 進階編輯器 會以 let 表達式範本開啟。

階段 2：定義數據源

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

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

結果：

階段 3：將第一列升階為標題

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

2. 在 Power Query 編輯器 中，選取 [首頁 > 進階編輯器，開啟時會以您在階段 2：定義數據源中建立的語句開啟。

3. 在 let 運算式中，新增 #“First Row as Header” 和 Table.PromoteHeaders 的運作方式如下：
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"First Row as Header"

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

結果：

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

1. 若要開啟查詢，請從工作表中選取數據中的儲存格，然後選取 [查詢 > 編輯]。 如需詳細資訊，請參閱 在 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. 在左窗格的 [全域] 底下，選取 [Power Query 編輯器]。

3. 在右窗格中的 [ 版面配置] 底下，選取或清除 [顯示數據編輯列]。

開啟或關閉 M Intellisense

1. 選取 [檔案 > 選項] 和 [設定 > 查詢選項]。

2. 在左窗格的 [全域] 底下，選取 [Power Query 編輯器]。

3. 在右窗格的 [ 公式] 底下，選取或清除 數據編輯列、進階編輯器和自定義欄對話方塊中的 [啟用 M Intellisense]。

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