Import Data from Text 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? If it’s inside a text file, it’s very easy to import data from text using Power Query or Get & Transform! I’ll share the exact steps I follow, some helpful tips I’ve learned, and explain the “why” behind each action so you understand more than just which buttons to click.

Key Takeaways:

• Power Query (Get & Transform) makes importing text data into Excel fast and repeatable.
• You can clean, transform, and shape data before loading it into your workbook.
• The “Use First Row as Headers” feature ensures proper column names.
• A connection is created, so you can refresh data without re-importing.
• Consistent file paths and clear query names prevent errors later.

 

Why Power Query Is a Game-Changer

Before I jump into the steps, let me tell you why I love this feature. Traditional importing in Excel works fine, but it’s usually a one-time action. If the source file changes, you have to redo the entire import process from scratch. Power Query changes the game because it creates a connection to your data. That means if the text file gets updated, you just refresh your query in Excel, and your data updates automatically—no re-import needed.

Power Query also lets you:

• Clean and transform your data as you import it.
• Rename columns and reorder them instantly.
• Handle huge datasets much more efficiently.
• Apply the same transformations repeatedly without manual effort.

This is especially useful if you work with monthly reports, large logs, exported ERP data, or machine-generated text files.

Import data from Text using Power Query

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

Select the text file (with extension .txt) that contains the data.  Click Import.

A preview of the text 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 text file!

 

Tips & Tricks

Best Practices I Follow

Over time, I’ve learned a few tricks that make this process even smoother:

• Keep File Paths Consistent – If I move the original text file to a new folder, the connection in Excel will break. To avoid headaches, I keep my data files in a consistent location or use a shared network folder.
• Name Queries Clearly – In Power Query, I rename my queries with descriptive names like “Text Data” instead of leaving them as “Query1.” This helps when I have multiple imports in the same workbook.

• Watch Your Data Types – Power Query sometimes guesses the wrong data type. For example, a column with numbers might be imported as text. I always double-check the data type icons in the header row and fix them if needed.
• Use Applied Steps Wisely – If I make a mistake, I can delete just that one step without undoing all my work. This is way more forgiving than traditional Excel editing.

• Refresh with Caution – Refreshing pulls the latest data from the source file. If the structure of the file changes (e.g., columns are added or removed), I might need to adjust my query.

 

Troubleshooting Common Issues

Here are a few hiccups I’ve run into—and how I solved them:

Issue: Columns aren’t splitting properly.

Fix: In Power Query, use Split Column by Delimiter and choose the correct separator (comma, tab, pipe, etc.).

Issue: First row is missing.

Fix: Check if the text file has a hidden first line (like metadata) and remove it before using “Use First Row as Headers.”

Issue: Numbers show up as text.

Fix: Change the column data type to “Decimal Number” or “Whole Number” in Power Query.

 

FAQs

1. What is Power Query in Excel, and why should I use it for text files?

Power Query, also known as Get & Transform, is a tool in Excel that lets you import, clean, and transform data from different sources, including text files. The biggest advantage is that it saves your steps so you can refresh the data anytime without repeating the process. This is especially helpful for recurring reports or large datasets. It also gives you better control over formatting and structure before the data enters your worksheet.

2. How is importing via Power Query different from just opening a text file in Excel?

When you open a text file in Excel using the traditional method, it’s a one-time import—you have to redo it if the file changes. With Power Query, you create a permanent connection to the file. That means you can refresh the data instantly when the source updates. It also allows you to apply transformations like column renaming, filtering, and type changes without manual edits each time.

3. What happens if I move or rename my text file after creating the query?

If the file path changes, Excel will lose its connection to the source, and the refresh will fail. This is why it’s best to keep your text files in a consistent location or a shared network folder. If you do need to move the file, you can update the source path in Power Query to restore the connection. It’s a quick fix, but avoiding the issue from the start saves time.

4. Can I use Power Query in older versions of Excel, like 2010 or 2013?

Yes, but it’s not built in—you’ll need to download and install the free Power Query add-in from Microsoft. Once installed, it will appear as a separate “Power Query” tab on the ribbon. The functionality is very similar to the built-in version in Excel 2016 and later, so you can still follow the same steps to import and transform text data.

5. How can I fix common issues like incorrect column splits or wrong data types?

If columns aren’t splitting correctly, you can use the “Split Column by Delimiter” option in Power Query and select the correct separator (such as a comma, tab, or pipe). For wrong data types—like numbers showing as text—you can change the type by clicking the data type icon in the column header. Power Query updates the entire column instantly, ensuring consistent formatting throughout your data.