Import Data from CSV Using Power Query or Get & Transform

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data. But what if your data source is not in your Excel spreadsheet but located on your desktop? If it’s inside a CSV file – Comma Separated Values which is denoted by a .csv file extension & where the columns are separated by commas – it’s very easy to import data from csv and right into Power Query!  It’s very common nowadays to get data in the comma-delimited format.

Key Takeaways

• Streamlines CSV Data Import – Power Query (also called Get & Transform) makes it easy to import CSV files into Excel with just a few clicks.

• Clean and Transform Data Automatically – You can filter rows, remove columns, split text, change data types, and much more—all during the import process.

• Supports Automatic Data Refresh – Once set up, your query can be refreshed with one click to pull updated data from the CSV file source.

• No Manual Copy-Paste Needed – Power Query eliminates repetitive tasks by saving your transformation steps as part of the query logic.

• Combine Multiple CSV Files – You can even combine data from multiple CSVs in a folder, ideal for batch processing monthly or weekly reports.

Our Data Setup

Let’s suppose you have this set of data from the csv file:

How to Import Data from CSV Using Power Query or Get & Transform

STEP 1:

Using Excel 2016 (screenshot below)

Go to Data > New Query > From File > From CSV

Using Excel 2013 or Excel 2010

Go to Power Query > From File > From CSV

 

Select the csv file that contains the data.  Click Import.

 

A preview of the csv data will be shown.  If it looks good, press Edit.

 

STEP 2: This will open up the Power Query Editor.

Go to Home > Transform > Use First Row As Headers. 

This will give your table the correct Column Headers.

 

STEP 3: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the imported table.

You now have your new table from the csv file!

Frequently Asked Questions

Where can I find Power Query in Excel?
Go to the Data tab in Excel 2016 or later and look for the Get & Transform Data group. In older versions, Power Query may be a separate add-in.

How do I import a CSV file using Power Query?
Click Data > Get Data > From File > From Text/CSV, choose your file, and follow the preview window to load or transform your data.

Can I modify the data before importing it into Excel?
Yes! Click on “Transform Data” instead of “Load” to open the Power Query Editor and clean or reshape your data as needed.

How do I update my data if the CSV file changes?
Simply click “Refresh All” on the Data tab, and Excel will re-run the query and pull in the latest data from the CSV file.

Can Power Query handle large CSV files?
Yes, Power Query is designed to efficiently process large datasets, and it performs better than loading large CSVs directly into Excel sheets.