Power QueryExercises

Your first Power Query in Excel, from table to refresh

Guide · By Tahir Nas · Examples run in our simulator · Updated

Jump to the practice tasks

This walkthrough builds one small query end to end: a table of orders becomes a summary of completed sales by region, loaded back into Excel and refreshed after new orders arrive. Every step names the Excel command, and the table at the end maps each step to a lesson and an exercise on this site.

Six-step flow: data in an Excel table, Data then From Table/Range, shape the data as applied steps, Home then Close & Load, the result lands on a new worksheet, and Data then Refresh All replays the steps when the source changes.
The whole journey. Steps 3 to 6 repeat every time the data changes.

Step 1: start from an Excel table

Put the orders in an Excel table with one header row: Order, Region, Status, Qty and Price. Microsoft's From Table/Range steps start with the cursor in an Excel table (Microsoft Support: Import from an Excel table). If the data is in another workbook or a CSV file instead, use Data > Get Data and pick that source; the lessons on importing from Excel and importing CSV files cover both.

Step 2: Data > From Table/Range

With a cell in the table selected, choose Data > Get & Transform Data > From Table/Range. The Power Query editor opens with the data in a preview pane (Microsoft Support: Import from an Excel table). The first applied step reads the table. A query that reads a table from its own workbook uses Excel.CurrentWorkbook, which only exists inside Excel, so our simulator refuses it rather than pretend:

let
    Source = Excel.CurrentWorkbook(){[Name = "Sales"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Qty", Int64.Type}})
in
    Typed

Not run by the simulator

Excel.CurrentWorkbook is not supported in the Power Query Exercises simulator. See the compatibility page for the supported subset. This code is shown for real hosts only; see the simulator compatibility page.

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Host-only code: this reads the Sales table of the open workbook, so it runs in Excel and not in a browser.

Step 3: shape the data

Now add three steps. Each one appears in Applied Steps as you make it (Microsoft Learn: Applied steps):

  1. Keep completed orders. Open the filter arrow on the Status header and tick only Complete (Microsoft Learn: Filter by values in a column).
  2. Add a value column. Choose Add Column > Custom Column and enter [Qty] * [Price] (Microsoft Learn: Add a custom column). Then set its type, because the Excel editor's custom column dialog doesn't set one for you.
  3. Total by region. Choose Transform > Group By, group on Region and sum the new column (Microsoft Learn: Grouping or summarizing rows).

Here are the same steps in M, run on the four orders from the table above. In Excel, Source would be the From Table/Range step; here it is written inline so the query runs in the browser:

let
    Source = #table(
        {"Order", "Region", "Status", "Qty", "Price"},
        {
            {"A-101", "North", "Complete", "2", "12.50"},
            {"A-102", "South", "Cancelled", "1", "40.00"},
            {"A-103", "North", "Complete", "3", "8.00"},
            {"A-104", "South", "Complete", "4", "5.00"}
        }
    ),
    Typed = Table.TransformColumnTypes(Source, {{"Qty", Int64.Type}, {"Price", type number}}, "en-US"),
    Completed = Table.SelectRows(Typed, each [Status] = "Complete"),
    WithValue = Table.AddColumn(Completed, "Value", each [Qty] * [Price], type number),
    ByRegion = Table.Group(WithValue, {"Region"}, {{"Sales", each List.Sum([Value]), type number}})
in
    ByRegion

Result

RegionSales
North49
South20

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

North: 2 × 12.50 + 3 × 8.00 = 49. South: the cancelled order is filtered out, leaving 4 × 5.00 = 20.

Step 4: Home > Close & Load

Select Home > Close & Load to send the result back to Excel (Microsoft Support: Create, load, or edit a query in Excel). Power Query loads the result to a new worksheet by default; to choose another destination, use Close & Load To instead (Microsoft Support: Import from an Excel table). The guide to managing queries in Excel explains each Close & Load To option.

Step 5: change the source and refresh

Add a new completed order to the source table, then select Data > Refresh All. You can also press Ctrl+Alt+F5 to refresh the whole workbook, or Alt+F5 for the selected data (Microsoft Support: Refresh an external data connection in Excel). Power Query replays every step on the new rows. You don't redo any of the cleaning:

let
    Source = #table(
        {"Order", "Region", "Status", "Qty", "Price"},
        {
            {"A-101", "North", "Complete", "2", "12.50"},
            {"A-102", "South", "Cancelled", "1", "40.00"},
            {"A-103", "North", "Complete", "3", "8.00"},
            {"A-104", "South", "Complete", "4", "5.00"},
            {"A-105", "West", "Complete", "1", "30.00"}
        }
    ),
    Typed = Table.TransformColumnTypes(Source, {{"Qty", Int64.Type}, {"Price", type number}}, "en-US"),
    Completed = Table.SelectRows(Typed, each [Status] = "Complete"),
    WithValue = Table.AddColumn(Completed, "Value", each [Qty] * [Price], type number),
    ByRegion = Table.Group(WithValue, {"Region"}, {{"Sales", each List.Sum([Value]), type number}})
in
    ByRegion

Result

RegionSales
North49
South20
West30

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

After the refresh: same steps, one more order, and a West row that didn't exist before.

Each step, its lesson and an exercise

StepIn ExcelLessonPractise it
ImportData > From Table/RangeImport from ExcelLoad a named table
Set typesHome > Data TypeChoose types deliberatelyKeep leading zeros
FilterColumn filter arrowFilter rows by valueKeep only completed orders
Add a columnAdd Column > Custom ColumnApplied steps are a recipeClean and summarise a sales export
GroupTransform > Group ByGroup by with several aggregationsClean and summarise a sales export
Load and refreshHome > Close & Load; Data > Refresh AllRefresh in Excel, Power BI and the serviceReorder steps before grouping
Every exercise runs in the browser and is checked against data it hasn't seen, the same test a refresh puts your query through.

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. Free tasks need no account.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

Excel menu paths on this page were checked against these pages on 8 November 2026.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.