Importing from Text Files

Quick reference

• 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.