Locked lesson.
About this lesson
Importing data from a text file into an Excel table.
Exercise files
Download this lesson’s related exercise files.
Importing Data from Text Files - Begin.xlsx25.2 KB Importing Data from Text Files - Complete.xlsx
44.4 KB Sales - Nov.txt
599 B Sales - Dec.txt
598 B
Quick reference
Importing from Text Files
An overview of importing and cleaning data from a text file using Power Query.
When to use
When you use a text file as a data source for your query and must clean up the (often) messy results so the data can be loaded into Excel.
Instructions
Getting Started
- Create a new query --> From File --> From Text/CSV --> browse for the file to import
- When greeted by the preview window, click Edit
Helpful tips for cleaning up text files
Removing irrelevant rows
- Go to Home --> Remove Rows
- Can be used to remove Top x, Bottom x, or even alternating rows
Tips for removing characters from text
- Leading and trailing whitespace: right-click the column header --> Transform --> Trim
- Internal whitespace: right click the column header --> Replace Values --> replace 2 spaces with 1
- Non-printing characters: right click the column header --> Transform --> Clean
Other tips
- Use Promote First Row to Headers to give the columns decent names
- Rename column headers by double-clicking the header name and typing a replacement
- To change the data type, click on the symbol and choose the data type you want
Filtering
- Use the filter arrows to the right of the column header to remove rows
- Filters can be used to remove values (including nulls) but not errors
Hints & tips
- You can go back through the steps applied to the query in the Query Settings window
- Any query step can be deleted by clicking on the “X” to the left of the query step
- 00:04 Where power query gets super awesome is when you realize that you're not
- 00:08 restricted to using data from within the same file, but
- 00:11 rather you can get it from external sources.
- 00:13 So I'm going to go up to the Data tab right now and
- 00:15 on the Get and Transform data group, you'll find this big Get Data button.
- 00:19 And then here there are a list of a variety of different connectors you can
- 00:23 use to connect to external data sources.
- 00:25 We're going to start with from file from text and
- 00:28 I'm going to grab a text file and import it into Excel.
- 00:31 Now, you'll be asked to browse.
- 00:33 I've stored the example files for this lesson in the C GoSkills folder here,
- 00:37 and I'm going to grab the sales for November.
- 00:40 We're going to choose to import and at this point I'll be prompted with a nice
- 00:45 little preview window that shows you what Power Query sees inside this file.
- 00:49 You'll notice that Power Query is picked up this file actually is tab delimited,
- 00:53 something that we really don't care about but Power Query needs to know.
- 00:56 And it gets this choice right in about 99% of circumstances, which is pretty awesome.
- 01:01 But if it doesn't, you can change it here.
- 01:03 It also allows you to set up how many rows you're using for data type detection.
- 01:07 But for right now, we're going to ignore that as well.
- 01:10 The important thing is to recognize, this is a preview of your data, so
- 01:14 not all of your data will necessarily be showing here.
- 01:17 It gives you the option to load it directly to the Excel worksheet or
- 01:21 what you're usually going to do is go to Transform Data to actually open
- 01:25 Power Query to manipulate and get it ready for the analysis that you want to do.
- 01:30 Now, you'll notice at this point when it opens in Power Query,
- 01:33 we have a source step that shows us the raw import from the Power Query file.
- 01:37 Up the top we can see the file path that I pulled it in for,
- 01:40 the delimiter that was chosen how many columns it has and things like that.
- 01:43 And then Power Query goes and changes the data types on the columns.
- 01:47 Now, if the columns have dates in them, I highly recommend that you delete the step,
- 01:52 if it's named it's usually, okay.
- 01:54 When I look at this data now what I see is that these first three rows don't look
- 01:58 very useful.
- 01:59 So what I'm going to do is I'm going to remove them via the Remove Rows button,
- 02:04 and we're going to choose to remove the top three rows.
- 02:07 So I'll type in 3, we'll hit OK, and at that point,
- 02:11 they are just skipped during the import.
- 02:14 Remember, Power Query never compromises your original file,
- 02:17 never makes any changes to the original file, it's all about reading data in.
- 02:20 And you can see right here that it is skipping the three rows in that
- 02:25 previous step.
- 02:26 At this point, I look at the next row and I say, boy,
- 02:28 this looks a lot like headers for a table.
- 02:30 So rather than have column one, two, three, four, five, six.
- 02:33 I'm going to choose to use the first row as headers
- 02:36 it adds a new step called Promoted Headers that pop these up in place.
- 02:40 And then Power Query always after promoting headers sets the data
- 02:43 types again.
- 02:44 So again, just watch out for
- 02:45 those column names if their date based maybe delete that step.
- 02:48 For me this is okay, I'm now going to normalize my data, get it unpivoted.
- 02:54 So I want to get rid of my grand total column.
- 02:56 I'll select that and press Delete.
- 02:58 I'll also filter out from the item column.
- 03:00 I'm going to take a look for the word total.
- 03:02 And there we go, we have a grand total, so I'm going to uncheck that and
- 03:06 remove it from the dataset as well.
- 03:07 It's the last row and it disappears that we're now looking at just our sales items.
- 03:12 So at this point, I'm going to right click unpivot other columns.
- 03:16 Double click on this first column to rename it as Salespeople.
- 03:21 Rename this column here as Units.
- 03:25 And at this point, the last thing I'm going to do is I'm going to go and
- 03:29 just change this to be just Sales as the table name.
- 03:33 I now have the option of just hitting close and load in a brand new workbook.
- 03:36 This is just load the table to a new worksheet or
- 03:39 because I want to put it on a specific worksheet, I'm going to choose close and
- 03:44 load too, and I'm going to choose to load my table into an existing worksheet and
- 03:49 we're going to drop it right here into cell A5, and we'll say OK.
- 03:53 And at this point, what we can see is that we've now got 48 rows that have been
- 03:57 loaded from an external text file.
- 03:58 They're all cleaned up and ready to be pivoted because of course,
- 04:01 they're in a table.
- 04:02 So let me just go and quickly add a new pivot table.
- 04:06 You can see it's reading from the sales range, which is perfect.
- 04:09 We'll go and say okay, and I'm going to drag Salespeople onto rows and
- 04:13 Units onto values.
- 04:14 So there we go, we have 12,818 units from the November text file.
- 04:20 Now, here's the thing, the cool thing is next month when I get a new file,
- 04:25 I don't have to rebuild my pivot table.
- 04:28 Here's how this works.
- 04:29 I'm going to close the queries and
- 04:31 connections pane because when you open Excel after saving it and
- 04:34 closing it reopening it next month, this is how it's going to look.
- 04:37 So we're going to go to the data tab, and find the queries and
- 04:40 connections button to open this up.
- 04:42 Because in order to re-point this file to the data source,
- 04:46 what we need to do is edit the query.
- 04:48 So I'll just double click on it to open it up And then what I'm going to do is I'm
- 04:52 going to go all the way back to the source step where the data first came from.
- 04:55 And what you'll see is that over here there's this little gear icon or cog.
- 04:59 And if you click on that, indeed if you click on any of these gears it brings up
- 05:04 the configuration for that particular step,
- 05:07 which includes the file path that we actually pulled our data from.
- 05:11 So remember,
- 05:12 my completed example file will be pulling from this file path on my system.
- 05:16 If you want to open that up and actually play with it, you'll need to edit
- 05:19 the query and re-point the file path to where it lives on your system.
- 05:22 This is easy to do, we find the source step, click the gear, click Browse, and
- 05:27 then we find our new file.
- 05:29 We'll say import, we'll say OK, we could walk through the steps, but
- 05:33 it's actually really not necessary.
- 05:36 I'm just going to go and click Close and Load.
- 05:38 And what we should see here is that over in the queries and
- 05:41 connections pane is going to start spinning, it loads a new 48 rows of data.
- 05:46 Of course, it's refreshed the table but not the pivot table.
- 05:49 So let's just go and hit Refresh All a couple of times.
- 05:52 And we can see that our data has now updated to 13,432.
- 05:57 So we're now grabbing data from a new file,
- 06:00 reusing all of our business logic, which is awesome.
Lesson notes are only available for subscribers.