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?

If it’s inside a text file, it’s very easy to import data from text and right into Power Query!

Let’s suppose you have this set of data from a text file:

Import Data from Text Using Power Query or Get & Transform

DOWNLOAD EXCEL WORKBOOK AND SOURCE FILE

STEP 1:

Using Excel 2016 (screenshot below)

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

Using Excel 2013 or Excel 2010

Go to Power Query > From File > From Text

Import Data from Text Using Power Query or Get & Transform

 

Select the text file (with extension .txtthat contains the data.  Click Import.

Import Data from Text Using Power Query or Get & Transform

 

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

Import Data from Text Using Power Query or Get & Transform

 

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.

Import Data from Text Using Power Query or Get & Transform

 

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.

Import Data from Text Using Power Query or Get & Transform

 

You now have your new table from the text file!

Import Data from Text Using Power Query or Get & Transform

 

Import Data from Text Using Power Query or Get & Transform

 

 

HELPFUL RESOURCE:

728x90

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn