在 Excel 中建立 Power Query 公式

只要使用 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)

需要更多協助?

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

×