Turn one CSV import into a repeatable query

Record an explicit type, trimming, filtering and calculation sequence, then prove that refreshing reruns that sequence.

Before you start

Windows desktop Excel 2016, 2019, 2021, 2024 and Microsoft 365 with Get & Transform. Microsoft 365 for Mac supports a Power Query Editor in version 16.69 or later; connectors and menus differ. The local-file exercise is not a promise of identical web or older perpetual Mac behavior.

Useful first: Import CSV without damaging identifiers

By the end, you can…

Explain each saved transformation step.
Separate an intentional filter from silently losing records.
Change the source and verify the next refresh against known totals.

YOUR PRACTICE FILE

Small data. A result you can check.

Synthetic data with ordinary spaces around the first North value. The zero-quantity row is retained in the source.

Download practice data

View the raw practice data

product_id,region,units,unit_price
P001," North ",2,15.00
P002,South,3,12.50
P003,North,4,8.00
P004,West,0,99.00

01Import without skipping the raw checkpoint

Save the UTF-8 sample CSV in a stable local folder. Import it with Data > From Text/CSV, using comma separation and no automatic type detection where available. Choose Transform Data. If present, remove the automatic Changed Type step before assigning types; keep or promote the header exactly once.

Name the query PositiveSales. Four input records should be visible. The exercise's rule is explicit: include only rows with units greater than zero. P004 is a zero-quantity quotation, so its exclusion is intentional. In a real report, confirm that rule with the data owner instead of assuming zero means a mistake.

02Build a short, explainable sequence

Set product_id and region to Text, units to Whole Number, and unit_price to Fixed Decimal Number using English (United States) through Change Type > Using Locale. Select region and choose Transform > Format > Trim. This sample uses ordinary edge spaces, so North becomes consistent; do not assume every invisible Unicode character is removed.

On units, choose Number Filters > Greater Than and enter 0. Three records remain. Choose Add Column > Custom Column, name it line_total and enter [units]*[unit_price]. Set line_total to Fixed Decimal Number. Sort product_id ascending so the output order is deliberate.

Review Applied Steps from top to bottom. Click the step before filtering to see four rows, then the filter step to see three. An Error value from a failed conversion needs investigation; deleting error rows would be an additional business decision, not a harmless formatting change.

product_id	region	units	unit_price	line_total
P001	North	2	15.00	30.00
P002	South	3	12.50	37.50
P003	North	4	8.00	32.00

03Load and define what refresh can change

Choose Home > Close & Load to a new worksheet. Rename the loaded Excel table SalesOutput on Table Design. In spare cells, =ROWS(SalesOutput[product_id]) must return 3 and =SUM(SalesOutput[line_total]) must return 99.5. Format the total to two decimals if you want 99.50 displayed.

The query records instructions; it is not a frozen copy of the original file. Refresh reads the source again and reruns those instructions. Keep the source path and column names stable, and put manual notes on a separate sheet. Editing the loaded results is not a durable correction because a refresh can replace them.

Sharing a workbook can expose its imported data and source path. Review both before sharing.

PUT IT INTO PRACTICE

Make the formerly excluded product a one-unit sale.

In the original CSV, change only P004's units from 0 to 1 and save as UTF-8 at the same path.
Back in Excel, right-click the loaded query table and choose Refresh. Wait for the refresh to finish before checking results.
Recheck the output row count and the sum of line_total.

Show the worked answer

The output now has 4 rows. P004 contributes 99.00.

The new total is 198.50; the other three line totals remain 30.00, 37.50 and 32.00.

Check your work

An unchanged 99.50 suggests the file was not saved, the wrong source was edited or refresh has not completed.
Restore P004 to zero and refresh to return to the initial three-row result.

CHECK YOUR UNDERSTANDING

One question before you move on.

Where should you make a correction that must survive future refreshes?In the chart title.In the authoritative source or an explicit transformation step.Only in a loaded result cell.

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.

NEXT LESSONAppend orders, merge customer names, and check the row count

Keep the skill close.

FIELD GUIDETRIM Not Working? Remove Hidden Spaces in Excel and Sheets FIELD GUIDEImport Decimal Commas in Excel Without Changing the Amounts FIELD GUIDECSV vs XLSX: what you lose when saving a spreadsheet as CSV WORKSPACE TOOLClean whitespace WORKSPACE TOOLCSV import checker

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.