Stop Pasting CSVs: Import and Refresh with Power Query

If you export data to a CSV file every month and then copy-paste it into Excel, this tutorial is for you. That manual import and cleanup routine can be fully automated with Power Query. We load the CSV once, apply any transformations we need, and from that point forward it is a single-click refresh. No more copy-paste, no more manual cleanup. In the exercises below, we walk through importing a CSV, transforming the data on the way in, updating the source file, and validating the results with control totals and a PivotTable report.

Exercise 1: Import a CSV with Power Query

Let’s say we have a CSV file that comes out of our accounting system every month. In the past, we would open it, copy the data, and paste it into Excel. With Power Query, we do this just once and let Excel handle it from there.

To get started, we go to Data, Get Data, From File, From Text/CSV. We browse to the CSV file, select it, and click Import.

That opens a preview of the file. The data looks clean, so we click the dropdown arrow next to the Load button and choose Load To. We tell Excel to put it in a table at a specific cell in an existing worksheet, then click OK.

We hit Enter, and bam: the CSV data lands in a formatted Excel Table, connected to the source file through a Power Query query. The Queries and Connections panel on the right confirms 6 rows loaded.

Sure, copy-paste might feel faster the first time. But going forward, refreshing this data takes exactly one click. That is the payoff.

Exercise 2: Transform Data and Refresh

Now that we have the basic import working, it is time to make things more interesting. Power Query lets us clean and reshape the data on the way in, so we never have to do it manually after the paste.

We go back to Data, Get Data, From File, From Text/CSV and browse to the file again. This time, instead of clicking Load, we click Transform Data. That opens the Power Query Editor.

On the right side we see the Applied Steps panel. This is a running list of every transformation we apply. Excel automatically added Promoted Headers and Changed Type steps. We can edit or delete any step if it is not right.

From here, we can remove columns we do not need, filter out rows we do not want, or reorder columns. For example, we can click and drag the Description column to move it earlier in the table. Every step gets recorded in the Applied Steps list.

Once we are happy with our transformations, we click Close and Load To, choose a table in an existing worksheet, pick a cell, and click OK. And bam: the transformed data is in Excel, column order AND data types exactly as we set them up.

Updating the Source File

Next period, we simply save the new export to the same folder with the same file name. Power Query remembered exactly where it found the file, so a one-click refresh pulls in the new data without us touching anything else.

But what if the file is in a different folder? Not a problem. We open the query in the editor, click the gear icon next to the Source step, and browse to the new location.

We pick the new file, click OK, then Close and Load. The query runs, and bam: fresh data is in the table, all transformations applied automatically.

Exercise 3: Verify the Pull with Control Totals

Importing data is great. Trusting the import is even better. We should always verify that the row count and dollar total we expected actually came into Excel. This is our control total check.

We know from the source system that we expected 6 rows. To count what came in, we use the COUNT function. Here is a quick look at COUNT before we use it. It counts the number of numeric values in a range. The function arguments are:

Here is a quick look at the COUNT function. It returns the number of cells in a range that contain numeric values. The function arguments are:

• value1: the first range or value to count
• value2, …: additional ranges or values to count (optional)

We write the following formula to count rows in the Date column of our imported table:

=COUNT(data__2[Date])

We enter that, and the result is 6. Expected: 6. Imported: 6. We then compute the difference so we can monitor it every refresh:

=D8-D9

We do the same thing for the dollar total using SUM. Before we use it, a quick refresher:

Before diving in, a quick look at the SUM function. It returns the total of all numeric values in a range. The function arguments are:

• number1: the first range or value to add
• number2, …: additional ranges or values to add (optional)

We write the following formula to total the Amount column:

=SUM(data__2[Amount])

The sum matches the expected total of 32,400. Diff is zero. Every time we refresh, these checks update automatically. If something is off, we know immediately.

Now that the data is verified, we can build reports right on top of it. A formula-based report or a PivotTable both work great, because they sit on top of the Power Query table and refresh right along with it.

When the next period’s export arrives, we drop the new file into the folder and go to Data, Refresh All. Power Query pulls the fresh data, the control totals update, and the PivotTable report is done. Mission accomplished!

Summary

In this tutorial we covered three exercises: importing a CSV file with Power Query instead of copy-paste, applying transformations in the Power Query Editor so our data arrives clean and shaped correctly, and building control total checks to verify the import. We also saw how a PivotTable built on a Power Query table refreshes with a single click. The result is a repeatable, reliable workflow that eliminates the manual work every period.

If you have any suggestions, improvements, alternatives, or questions, please share by posting a comment below … thanks!

Sample File

FAQs

What version of Excel do I need to use Power Query?

Power Query is built into Excel 2016 and later on Windows. If we are on Excel 2010 or 2013, we can install it as a free add-in from Microsoft. It is not available natively on Excel for Mac, though Microsoft has been adding limited Power Query support to newer Mac versions.

Does the CSV file have to keep the same name for the refresh to work?

Yes, by default Power Query stores the exact file path, including the file name. If we save each period’s export with the same name in the same folder, the refresh works with no changes. If the name or location changes, we click the gear icon next to the Source step in the Power Query Editor and browse to the new file.

What if the CSV file has extra rows or a header row I do not need?

No worries, we can handle that in the Power Query Editor. We can use Remove Top Rows or Remove Bottom Rows under the Home tab to strip unwanted rows, and we can use the filter dropdown on any column to exclude specific values. These steps are saved and reapplied on every refresh.

Can I load the CSV into a PivotTable directly without creating a table on the worksheet first?

Yes. When we choose Load To after the import, we can select PivotTable Report as the destination instead of Table. This loads the query directly into a PivotTable without placing the raw data on a worksheet.

How do I refresh the data automatically when I open the workbook?

We can set a query to refresh on open. We right-click the query in the Queries and Connections panel, choose Properties, and check the Refresh data when opening the file option. From that point forward, every time the workbook opens it pulls the latest data from the CSV.

What happens if the CSV has more or fewer columns than last time?

Power Query is sensitive to column names. If a column is renamed or removed in the source file, the steps that reference that column will throw an error on refresh. We would need to open the editor, update the affected steps, and save. This is a good reason to keep source file structures consistent from period to period.

Can I combine multiple CSV files from the same folder into one table?

Absolutely. Instead of choosing From Text/CSV, we choose From Folder under Get Data, From File. Power Query can pull all the files in a folder and stack them into one table. This is perfect for situations where we get one CSV file per month and want a full year of data in one place.

How do control totals help me trust the import?

Before the import, we note the expected row count and dollar total from the source system. After the import, we use COUNT to check the row count and SUM to check the total. We then compute the difference. If the diff is zero, the import is confirmed. Because these formulas reference the Power Query table, they update automatically on every refresh.

Can I use Power Query to clean up data types, like fixing dates stored as text?

Yes, and this is one of Power Query’s best features. In the Power Query Editor, we can click the data type icon at the top of any column and change it to Date, Number, Text, or any other type we need. The Changed Type step is recorded and applied on every future refresh, so we never have to fix data types manually after a paste.

Is the Power Query table the same as a regular Excel Table?

It looks similar, but it has an external data connection attached to it. We can use structured references like table[column] in formulas just like a regular Excel Table. The key difference is that when we click Refresh, the Power Query engine runs its steps and updates the table automatically, which a regular Excel Table cannot do on its own.