Power Query Basics - Clean and refresh a report

Record the cleanup instead of repeating it

Power Query imports and transforms data through a sequence of recorded steps. Refresh runs those steps again against the source. It is useful for recurring attendance exports, monthly sales files, and inventory reports that need the same cleanup each time. The transformation normally leaves the source data unchanged.

Open the source in Power Query

1. Select the complete source, press Ctrl+T, confirm headers, and name the Table RawOrders on Table Design.
2. Choose Data > From Table/Range in the Get & Transform Data area. The Power Query Editor opens with a preview.
3. Inspect Query Settings > Applied Steps. Excel may automatically add Changed Type. Remove or adjust that step if it interprets a column incorrectly; type errors can occur before later cleaning steps.

Clean, type, and filter the records

1. Select Order ID, Region, and Status using Ctrl to select multiple columns. Choose Transform > Format > Trim to remove surrounding spaces.
2. Select Region and Status, then Transform > Format > UPPERCASE. Keep the order IDs as text; do not convert identifiers to numbers.
3. Set Amount INR to Whole Number using its data-type icon. These example amounts are integers; use an appropriate decimal type for amounts containing paise.
4. Filter Status to OPEN. O102 is PAID, so it is excluded.
5. Check the four cleaned detail records before summarizing. In Applied Steps, selecting an earlier step lets you inspect the preview at that point.

Group by region and load the result

1. Choose Home > Group By. Group on Region; set the new column name to Total INR, Operation to Sum, and Column to Amount INR.
2. Sort Region ascending. The result should contain EAST, NORTH, and SOUTH once each.
3. Name the query OpenRevenueByRegion in Query Settings.
4. Choose Home > Close & Load > Close & Load To, select Table and New Worksheet, then load the result. If the dialog is not offered in your version, use the query's Load To option from Queries & Connections.
5. Reconcile the loaded total: ₹6,000 + ₹30,000 + ₹10,000 = ₹46,000.

Reuse the query next time

1. Append O106, East, text amount 2000, and Open inside RawOrders.
2. Choose Data > Refresh All and wait for the query to finish.
3. Verify EAST is now ₹8,000, NORTH remains ₹30,000, SOUTH remains ₹10,000, and the total is ₹48,000.

• Use the same pattern to standardize department names before an attendance report.
• For recurring files, import from a supported file or folder connector and retain a consistent schema.
• Load cleaned detail records instead of grouping when a later PivotTable needs the transaction-level data.

Common mistakes and practical checks

• Wrong totals: inspect the filter and Group By operation. Counting rows is different from summing Amount INR.
• Conversion errors: inspect the original values and choose a suitable type. For regional date or decimal conventions, use Change Type > Using Locale where appropriate.
• Broken steps after renamed columns: repair the first failing Applied Step against the source schema, then check later steps.
• Missing added records: confirm they are inside RawOrders, refresh, and inspect the source step.
• Unexpected duplicate groups: clean spaces and capitalization before grouping; review nonstandard characters separately.
• Removed errors hide a problem: investigate why values failed instead of automatically deleting every affected record.

Check your understanding

Choose an answer for instant feedback. You can try again; answers stay only on this page.

Related lessons

• TRIM - Normalize ordinary spaces
• Excel Tables - Give datasets a reliable structure
• Remove Duplicates - Decide the record key first
• Text to Columns - Split an import into fields
• PivotTables - Summarize source records