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.

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

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!

Import Data from CSV in Excel