只要使用 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 來收集您在網站上找到的原始資料,並轉換成格式良好的表格。 觀看如何在已應用步驟和資料編輯列的查詢設定窗格中,針對每個工作建立查詢步驟和對應的公式

您的瀏覽器不支援影片。

程序

  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 Power Query 中資料編輯列左側的 Enter 圖示 圖示或按 Enter。 例如,將這個公式變更為同時保留

    Column2:Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    之後:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

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

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

在資料編輯欄中建立公式

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

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

  2. 在資料編輯欄中,=Text.Proper("text value"),然後選取輸入 Power Query 中資料編輯列左側的 Enter 圖示 圖示或按 Enter。

    結果會顯示在資料預覽中。

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

結果:

Text.Proper

 當您建立公式時,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 的索引表,其產品名稱欄要轉換成適當的大小寫。 

之前:

之前

後:

步驟 4 - 結果

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

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

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

階段 1:開啟進位編輯器

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

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

進階編輯器 2

階段 2:定義資料來源

  1. 使用運算式建立let運算式Excel。CurrentWorkbook函數如下所示

    let    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]in


        

    Source 步驟 1 - 進階編輯器

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

結果:

步驟 1 - 結果

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

結果:

步驟 3 - 結果

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

結果:

步驟 4 - 結果

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

顯示或隱藏資料編輯欄

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

  2. 在左窗格中的 GLOBAL下,選取 Power Query 編輯器

  3. 在右窗格中 的版面配置下,選取或清除 顯示資料編輯列

開啟或關閉 M Intellisense

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

  2. 在左窗格中的 GLOBAL下,選取 Power Query 編輯器

  3. 在右窗格中的 [公式> 下,選取或清除資料編輯列中的 [啟用 M Intellisense、進位編輯器及自訂欄對話方塊

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

另請參閱

適用於 Excel 的 Power Query 說明

建立及調用自訂函數

使用已執行的步驟清單 (docs.com)

使用自訂函數 (docs.com)

Power Query M 公式 (docs.com)

處理錯誤 (docs.com)

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×