Power Query foundations

A query is a saved preparation recipe

Imagine a weekly order export with extra spaces in Region, quantities stored as text and an unwanted Notes column. Cleaning it manually each week is slow and easy to vary accidentally. Power Query saves ordered transformation steps and reapplies them when you refresh.

Separate three things: the original source, the query's transformation recipe, and the loaded result. Changing a cell in the loaded result does not rewrite the source or create a transformation step; a refresh can replace that change.

The browser exercises below check preparation decisions and reconciliation. The native import, editor and refresh tasks must be performed in a compatible Excel version; this website does not run Power Query.

Create your first table query in Windows desktop Excel

1. Enter headings Order, Region, Units and Unit price in A1:D1. Add O101/East/3/20, O102/West/2/15 and O103/East/5/10 beneath them.
2. Select the range, use Insert → Table, confirm headings, and name the table OrdersRaw.
3. Select a table cell and choose Data → From Table/Range to open Power Query Editor.
4. Name the query OrdersClean. Inspect the Source and any automatic Changed Type steps.
5. Set Order and Region to Text, Units to Whole Number and Unit price to Decimal Number.
6. Add Column → Custom Column, name it Sales, and use [Units]*[Unit price].
7. Choose Home → Close & Load to return a table on a new sheet.

The custom-column expression is Power Query M syntax. Square brackets access fields; it is not an A1 worksheet formula and is not entered into the browser formula checker.

Data types express meaning

An identifier such as 00123 should remain Text if its leading zeros are significant. Units should be numeric so they can be added. A date string such as 04/05/2026 is ambiguous internationally: decide whether the source means 4 May or April 5, then use Change Type → Using Locale when needed.

Inspect errors after converting types. Replacing every invalid quantity with zero hides missing or malformed input. Investigate the source and choose a documented treatment, such as correcting the record or retaining an exception list.

For cleaning text, select the Region column and use Transform → Format → Trim where available. Trim removes surrounding whitespace; it does not prove that East and E are the same approved category.

Worked check: row count and total sales

The three practice orders produce Sales of 60, 30 and 50: three records and a total of 140. Compare that total with the source before trusting the output.

1. Append O104/East/2/25 to the input table, ensuring it becomes part of the table.
2. Refresh the query through Data → Refresh All or the query's Refresh command.
3. Confirm four records and total Sales 190.
4. Verify the new row received the same type and Sales transformations.

A refresh reruns the saved steps against accessible source data. It does not automatically know a renamed file's location or obtain another person's permissions. If you share a file-based query, the recipient may need a reachable source and updated source settings.

Check the available Excel tools

Power Query is integrated in supported Windows desktop Excel versions through Data/Get & Transform. Microsoft 365 for Mac and Excel for the web also have Power Query capabilities, but available import, editing, connector and refresh operations differ. Follow Microsoft's current platform table if a command is missing. Android and iOS do not support Power Query.

Use the detailed Windows table/range steps where those commands exist; other platforms may require Get Data and a supported source instead. A missing connector is a platform or license limitation, not a reason to buy anything to complete the browser exercises.