只要使用 Power Query 編輯器,您就一直在建立 Power Query 公式。 讓我們看看 Power Query的運作方式。 您可以透過觀看實際操作的 Power Query 編輯器,瞭解如何更新或新增公式。  您甚至可以將自己的公式與 進階編輯器 一起匯總。           

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

查詢編輯器組件

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

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

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

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

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

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

查詢編輯器公式範例

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

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

您的瀏覽器不支援影片。 請安裝 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

程序

  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 Power Query 中數據編輯列左側的 Enter 圖示 圖示或按 Enter。 例如,將此公式變更為同時保留 Column2:Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. 選取 [輸入 Power Query 中數據編輯列左側的 Enter 圖示 ] 圖示,或按 Enter 以查看 [數據預覽] 中顯示的新結果。

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

在數據編輯列中建立公式

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

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

  2. 在數據編輯列中輸入=Text.Proper("text value"),然後選取 [輸入] Power Query 中數據編輯列左側的 Enter 圖示 圖示或按 Enter。結果會顯示在 [數據預覽] 中。

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

結果:

Text.Proper

 當您建立公式時,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 表格,其中有一個 [產品名稱] 欄,您想要轉換為適當的大小寫。 

之前:

之前

後:

步驟 4 - 結果

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

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

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

階段 1:開啟 進階編輯器

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

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

進階編輯器 2

階段 2:定義數據源

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

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

結果:

步驟 1 - 結果

階段 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. 若要將查詢載入至工作表,請選取 [完成],然後選取 [ 常用 ]> [關閉] & [載入] > [關閉] & [載入]

結果:

步驟 3 - 結果

階段 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. 若要將查詢載入至工作表,請選取 [完成],然後選取 [ 常用 ]> [關閉] & [載入] > [關閉] & [載入]

結果:

步驟 4 - 結果

您可以控制所有活頁簿 Power Query 編輯器 資料編輯列的行為。

顯示或隱藏數據編輯列

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

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

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

開啟或關閉 M Intellisense

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

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

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

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

另請參閱

適用於 Excel 的 Power Query 說明

建立和叫用自定義函數

使用 [套用步驟] 列表 (docs.com)

使用自定義函數 (docs.com)

Power Query M 公式 (docs.com)

處理錯誤 (docs.com)

Need more help?

Want more options?

探索訂閱權益、瀏覽訓練課程、瞭解如何保護您的裝置等等。

社群可協助您詢問並回答問題、提供意見反應,以及聆聽來自具有豐富知識的專家意見。