Start from a file you can replace deliberately
A reusable query records the cleanup steps so they can run again on a new source. Start with a copy of the fictional order export in a dedicated working folder. Keep the original source separately. This walkthrough describes the Power Query workflow; the downloaded worked workbook contains prepared data and formulas, with no embedded query or external file connection.
Work with the example
First source file ↓Second source file ↓All records are fictional. Import CSV identifier columns as Text. Quoted CSV fields do not define spreadsheet column types.
Power Query features and source support vary across Excel versions and platforms. The steps below use the common Excel desktop Get & Transform workflow. Check your installed version against Microsoft's documentation before relying on the exact command labels.
Record each transformation in a useful order
- Use Data → From Text/CSV and inspect the delimiter and quoted fields.
- Choose Transform Data. Review any automatic Changed Type step before it damages identifier columns.
- Set order, line, customer, phone, and SKU columns to Text.
- Convert the order date using a verified day/month/year source locale.
- Replace the RM prefix in price and discount columns, then convert using the declared numeric convention.
- Use the source's duplicate rule. For this sample only, one all-field exact repeated record is known to be accidental.
- Add gross and net line values. Gross is quantity × price; net subtracts the line discount once.
- Load the prepared table to a worksheet and build the reporting window separately.
Do not filter the query to the first reporting week if you intend the loaded table to support later periods. The date window belongs in the report unless the query's purpose explicitly requires a fixed period.
Test a second source before calling it repeatable
Save a backup of the first source, then replace the working source file with the second sample using the same filename and columns. Refresh the loaded query. The second sample contains two Shipped lines, five units, RM87.50 gross, RM2.50 discounts, and RM85.00 net for the week starting November 2, 2026.
| Refresh check | Expected second sample |
|---|---|
| Prepared records | 2 |
| Order IDs | 000201 and 000202 |
| Units | 5 |
| Gross / discount / net | RM87.50 / RM2.50 / RM85.00 |
| Dates | November 2 and November 3, 2026 |
Confirm the worksheet output changed, not just the preview inside the query editor. Check the report date window and refresh the report's own dependent objects when needed. A successful refresh message does not establish that the right source file was read.
Make schema changes visible
Rename a required column in a disposable source copy and confirm the query exposes the problem. A source export that changes column names or conventions needs a reviewed update to the query. Do not suppress the resulting errors as blanks or zeros.
Keep an example source file, a note of the intended types and locale, and the expected second-sample totals with the workflow. This makes later maintenance a comparison against a known case. The supplied source files support that check; the Power Query import itself still needs verification in your installed Excel version.
Sources & further reading
- About Power Query in Excel — Microsoft
- Power Query data types and locale — Microsoft
- Refresh an external data connection — Microsoft
The sample data and worked calculations are original instructional examples. Spreadsheet menus and supported features depend on your installed version. See the verification method for the checks performed.
Continue the workflow