Create Power Query formulas in Excel

Just by using the Power Query Editor, you have been creating Power Query formulas all along. Let's see how Power Query works by looking under the hood. You can learn how to update or add formulas just by watching the Power Query Editor in action.  You can even roll your own formulas with the Advanced Editor.           

The Power Query Editor provides a data query and shaping experience for Excel that you can use to reshape data from many data sources. To display the Power Query Editor window, import data from external data sources in an Excel worksheet, select a cell in the data, and then select Query > Edit. The following is a summary of the main components.

Query Editor Parts

  1. The Power Query Editor ribbon that you use to shape your data

  2. The Queries pane that you use to locate data sources and tables

  3. Context menus that are convenient shortcuts to commands in the ribbon

  4. The Data Preview that displays the results of the steps applied to the data

  5. The Query Settings pane that lists properties and each step in the query

Behind the scenes, each step in a query is based on a formula that is visible in the formula bar.

Query Editor Formula Sample

There may be times when you want to modify or create a formula. Formulas use the Power Query Formula Language, which you can use to build both simple and complex expressions. For more information about syntax, arguments, remarks, functions, and examples, see Power Query M formula language.

Using a list of soccer championships as an example, use Power Query to take raw data that you found on a website and turn it into a well-formatted table. Watch how query steps and corresponding formulas are created for each task in the Query Settings pane under Applied Steps and in the Formula bar.

Your browser does not support video.

Procedure

  1. To import the data, select Data > From Web, enter "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" in the URL box, and then select OK.

  2. In the Navigator dialog box, select the Results [Edit] table on the left, and then select Transform Data at the bottom. The Power Query editor appears.

  3. To change the default query name, in the Query Settings pane, under Properties, delete "Results [Edit]" and then enter "UEFA champs".

  4. To remove unwanted columns, select the first, fourth, and fifth columns, and then select Home > Remove Column > Remove Other Columns.

  5. To remove unwanted values, select Column1, select Home > Replace Values, enter "details" in the Values to Find box, and then select OK.

  6. To remove rows that have the word "Year' in them, select the filter arrow in Column1, clear the check box next to "Year", and then select OK.

  7. To rename the column headers, double-click each of them and then change "Column1" to "Year", "Column4" to "Winner", and "Column5" to "Final Score".

  8. To save the query, select Home > Close & Load.

Result

Results of the walkthrough - the first few rows

The following table is a summary of each applied step and the corresponding formula.

Query step and task

Formula

Source

Connect to a web data source

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Navigation

Select the table to connect

=Source{2}[Data]

Changed Type

Change datatypes (which Power Query does automatically)

= 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}})

Removed Other Columns

Remove other columns to only display columns of interest

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

Replaced Value

Replace values to clean up values in a selected column

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

Filtered Rows

Filter values in a column

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Renamed Columns

Changed column headers to be meaningful

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Important    Be careful editing the Source, Navigation, and Changed Type steps because they are created by Power Query to define and set up the data source.

Show or hide the formula bar

The formula bar is shown by default, but if it's not visible you can redisplay it.

  • Select View > Layout > Formula Bar.

Edit a formula in the formula bar

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. In the Query Settings pane, under Applied Steps, select the step you want to edit.

  3. In the formula bar, locate and change the parameter values, and then select the Enter The Enter icon to the left of the formula bar in Power Query  icon or press Enter. For example, change this formula to also keep Column2:

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

  4. Select the Enter The Enter icon to the left of the formula bar in Power Query  icon or press Enter to see the new results displayed in the Data Preview.

  5. To see the result in an Excel worksheet, select Home Close & Load.

Create a formula in the formula bar

For a simple formula example, let’s convert a text value to proper case using the Text.Proper function.

  1. To open a blank query, in Excel select Data > Get Data > From Other Sources > Blank Query. For more information see Create, load, or edit a query in Excel.

  2. In the formula bar, enter =Text.Proper("text value"), and then select the Enter The Enter icon to the left of the formula bar in Power Query  icon or press Enter.

    The results display in Data Preview .

  3. To see the result in an Excel worksheet, select Home Close & Load.

Result:

Text.Proper  

 When you create a formula, Power Query validates the formula syntax. However, when you insert, reorder, or delete an intermediate step in a query you might potentially break a query.  Always verify the results in Data Preview.

Important    Be careful editing the Source, Navigation, and Changed Type steps because they are created by Power Query to define and set up the data source.

Edit a formula by using a dialog box

This method makes use of dialog boxes that vary depending on the step. You don't need to know the syntax of the formula.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. In the Query Settings pane, under Applied Steps, select the Edit Settings Settings icon icon of the step you want to edit or right-click the step, and then select Edit Settings.

  3. In the dialog box, make your changes, and then select OK.

Insert a step

After you complete a query step that reshapes your data, a query step is added below the current query step. but when you insert a query step in the middle of the steps, an error might occur in subsequent steps. Power Query displays an Insert Step warning when you try to insert a new step and the new step alters fields, such as column names, that are used in any of the steps that follow the inserted step.

  1. In the Query Settings pane, under Applied Steps, select the step you want to immediately precede the new step and its corresponding formula.

  2. Select the Add Step Function icon icon to the left of the formula bar. Alternatively, right click a step and then select Insert Step After. A new formula is created in the format :

    = <nameOfTheStepToReference>, such as =Production.WorkOrder.

  3. Type in the new formula using the format:

    =Class.Function(ReferenceStep[,otherparameters])

    For example, assume you have a table with the column Gender and you want to add a column with the value “Ms.” or “Mr.”, depending on the person’s gender. The formula would be:

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Example formula

Reorder a step

  • In the Queries Settings pane under Applied Steps, right click the step, and then select Move Up or Move Down.

Delete a step

  • Select the  Delete Delete a step icon to the left of the step, or right click the step, and then select Delete or Delete Until End. The Delete Delete a step icon is also available to the left of the formula bar.

In this example, let’s convert the text in a column to proper case using a combination of formulas in the Advanced Editor. 

For example, you have an Excel table, called Orders, with a ProductName column that you want to convert to proper case. 

Before:

Before

After:

Step 4  - Result

When you create an advanced query, you create a series of query formula steps based on the let expression. Use the let expression to assign names and calculate values that are then referenced by the in clause, which defines the Step. This example returns the same result as the one in the "Create a formula in the formula bar" section.

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

You'll see that each step builds upon a previous step by referring to a step by name. As a reminder, the Power Query Formula Language is case-sensitive.

Phase 1: Open the Advanced Editor

  1. In Excel, select Data > Get DataOther Sources > Blank Query. For more information see Create, load, or edit a query in Excel.

  2. In the Power Query Editor, select Home Advanced Editor, which opens with a template of the let expression.

Advanced Editor2

Phase 2: Define the data source

  1. Create the let expression using the Excel.CurrentWorkbook function as follows:

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

    Step 1 - Advanced Editor

  2. To load the query to a worksheet, select Done, and then select Home Close & Load > Close & Load.

Result:

Step 1 - Result

Phase 3: Promote the first row to headers

  1. To open the query, from the worksheet select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel (Power Query).

  2. In the Power Query Editor, select Home Advanced Editor, which opens with the statement you created in Phase 2: Define the data source.

  3. In the let expression, add #"First Row as Header" and Table.PromoteHeaders function as follows:

    let
        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
      #"First Row as Header" = Table.PromoteHeaders(Source)
    in
        #"First Row as Header"

  4. To load the query to a worksheet, select Done, and then select Home Close & Load > Close & Load.

Result:

Step 3  - Result

Phase 4: Change each value in a column to proper case

  1. To open the query, from the worksheet select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. In the Power Query Editor, select Home Advanced Editor, which opens with the statement you created in Phase 3: Promote the first row to headers.

  3. In the let expression, convert each ProductName column value to proper text by using the Table.TransformColumns function, referring to the previous "First Row as Header” query formula step, adding #"Capitalized Each Word" to the data source, and then assigning #"Capitalized Each Word" to the in result.

    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. To load the query to a worksheet, select Done, and then select Home Close & Load > Close & Load.

Result:

Step 4  - Result

You can control the behavior of the formula bar in the Power Query Editor for all your workbooks.

Display or hide the formula bar

  1. Select File > Options and Settings > Query Options.

  2. In the left pane, under GLOBAL, select Power Query Editor.

  3. In the right pane, under Layout, select or clear Display the Formula Bar.

Turn on or off M Intellisense

  1. Select File > Options and Settings > Query Options .

  2. In the left pane, under GLOBAL, select Power Query Editor.

  3. In the right pane, under Formula, select or clear Enable M Intellisense in the formula bar, advanced editor, and custom column dialog.

Note    Changing this setting will take effect the next time you open the Power Query Editor window.

See Also

Power Query for Excel Help

Create and invoke a custom function

Using the Applied Steps list (docs.com)

Using custom functions (docs.com)

Power Query M formulas (docs.com)

Dealing with errors (docs.com)

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×