Microsoft Excel · Power Query bounded introductory track · Intermediate
Import and refresh data with Power Query
Import a local CSV with Power Query, load the results, and refresh a saved source change without confusing preview and worksheet data.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: Use Data > Get Data > From File > From Text/CSV, inspect the preview, and load the query. When the source changes, refresh the loaded worksheet query. Refreshing only the Power Query Editor preview does not update the worksheet result.
Scope and prerequisites
Use English Excel for Microsoft 365 desktop on Windows with Power Query available. Windows, Mac, and Excel for the web all have Power Query capabilities, but their supported sources, editing interfaces, authentication, and refresh options differ. These local-file Windows steps are not a promise of identical Mac or browser support. No database, cloud account, or paid connector is needed.
Create a plain-text file named sales-practice.csv in a folder you control. Save these exact contents, including the header row:
Order,Region,Amount
A01,North,120
A02,South,80
A03,North,50
The expected result has three data rows, three columns, and an Amount total of 250. Order is an identifier, not a measure. Keep this source file separate from the Excel workbook that will hold the query results.
Connect and inspect
- In a blank workbook, choose Data > Get Data > From File > From Text/CSV.
- Select sales-practice.csv and import it. Inspect the delimiter and preview before continuing: the fields must occupy three columns, not one column containing commas.
- Choose Transform Data to inspect the data in Power Query Editor.
- Confirm that the first row supplies the column names. Set Order and Region to Text and Amount to a numeric type, such as Whole Number, using the column data-type controls.
- Review Applied Steps. These steps form the reusable query; they are not edits to the original CSV.
- Choose Home > Close & Load to load the result into the workbook. Rename the result sheet to QueryResult so it is not confused with a source sheet.
Inspect all three identifiers and amounts. Select the result table and set its Table Design table name to ImportedSales. In E1 enter Checked total and in E2 enter =SUM(ImportedSales[Amount]). This off-table check expands with the table and stays out of its A:C growth area. See SUM for the calculation. Do not insert manual corrections directly into the query’s output table and expect them to survive the next refresh.
Refresh a known source change
Open the CSV in a text editor and change A02’s Amount from 80 to 100. Add this final row and save the CSV:
A04,South,30
In Excel, select a cell in the loaded query table and choose Query > Refresh > Refresh. Wait for the operation to finish before reading its results. The expected output now contains four data rows with amounts 120,100,50,30, totaling 300. The increase of 50 comes from a 20 correction plus a 30 addition.
If instead you use Home > Refresh Preview inside Power Query Editor, you update the preview/cache. That is not the same as refreshing the loaded worksheet. Close and load the edited query or refresh the worksheet query before checking the worksheet total.
Save and diagnose failures
Save the report as XLSX, close it, and reopen it. Check the displayed 300 total, then refresh again with the source file still available. A saved result is a snapshot; a successful refresh additionally proves the current source can be read.
If the file has moved, repair its source path rather than repeatedly refreshing the old location. If the total remains 250, confirm you saved the CSV, refreshed the intended query, and selected the output sheet rather than another table. If privacy or access restrictions appear, resolve them through the authorized source settings; do not disable protections to make the example run. See privacy-safe Formula Firewall fixes for that separate issue.