Importing Data from a Text File

Quick reference

• 00:04 One of the realities of data is that it's not always stored in Excel files or
• 00:09 nice clean databases.
• 00:10 In fact, there's a lot of systems still that kick out their data in text files, so
• 00:15 it's important to know that Power Query can also be used to import text files and
• 00:19 automate that process.
• 00:21 Let's go to the Data tab again, and
• 00:23 I'm going to Get Data > From File > From Text/CSV.
• 00:27 You'll also notice that it has a handy place as a one click
• 00:30 transform on the ribbon as well.
• 00:33 When I do this,
• 00:34 it'll take me to browse into the folder where I am actually keeping my examples.
• 00:37 I have already navigated there, so I can actually see now
• 00:40 that I want to find sales for November, and I'm gonna say Import.
• 00:46 And at this point,
• 00:47 it pulls up a nice little preview of the data that's actually in the file.
• 00:51 It gives us some options we could change.
• 00:54 I've never had to change the delimiter,
• 00:55 Power Query's always really smart about picking that up.
• 00:58 It gives us some data type detection and a nice little look at what's there.
• 01:01 And the nice thing here is if you decide that you don't want this,
• 01:04 you can always click Cancel to make it go away.
• 01:07 I'm gonna choose the button here, which is Edit, and
• 01:10 we're gonna go in and actually modify this data
• 01:12 because virtually all data needs to be modified before you can actually use it.
• 01:17 Now you'll notice when you get in here that Power Query records a source and
• 01:21 immediately applies data types to all the columns.
• 01:24 Now, what I'm looking at here though, these first three rows, don't really seem
• 01:27 to have a lot of use to me, so I'm gonna go to Remove Rows > Remove Top Rows,
• 01:33 and I'm going to remove the top three rows from this data set.
• 01:37 And this doesn't do anything to the underlying file,
• 01:40 it just removes them from this data set when I pull it in.
• 01:43 The next thing I'm gonna do, is use the first row as headers,
• 01:46 because that's what they look like, and
• 01:47 it pops up my nice headers of Item, and Fred, and Jane, and John, and Mary.
• 01:52 It looks like I have some grand totals.
• 01:54 Don't need those, we'll delete that.
• 01:56 And in my Items, I can also see a Grand Total row here, so
• 02:00 I'll get rid of this as well.
• 02:04 And now I'm left with the data set which, for all intents and
• 02:08 purposes, is pivoted, so why not?
• 02:10 Right click > Unpivot Other Columns, and
• 02:13 now I've got a beautiful little column here for my SalesPerson, and
• 02:18 another one for my Units.
• 02:22 And the final step before I go and load my data, is I'll just force my data types to
• 02:27 be Text, and Text, and Whole Numbers in this case.
• 02:34 And then, I think the last thing I'm gonna do, is go and rename this query to Sales,
• 02:39 because this is the name that it will create when I create my Excel table.
• 02:43 So now I can say Close & Load To, and
• 02:47 I'm going to load this directly into my primary worksheet here, and we'll say OK.
• 02:52 And at that point, I've got a nice set of data that's been created with 48 rows, and
• 02:59 I can very quickly go again, of course, and summarize this with a pivot table.
• 03:03 We'll go and we'll put this on the same worksheet,
• 03:05 we'll drop it right here beside, and I'll just quickly go and
• 03:09 summarize by SalesPerson the number of units that are sold, and there we go.
• 03:15 But now, the real benefit of working with Power Query
• 03:18 is what happens when next month comes along.
• 03:20 We get a new file.
• 03:22 Well I don't wanna have to go through the job of cleaning all this up
• 03:25 all over again, and I wanna reuse this pivot table.
• 03:28 So here's what we're gonna do, if you open your file and your Queries & Connections
• 03:31 pane is not showing, you go to Data > Queries & Connections to pop it back up.
• 03:37 This will show us our query.
• 03:38 We can double-click on it to go back in and edit it inside Power Query.
• 03:44 And now the key is, whether we're in here for debugging, or whether
• 03:47 we wanna repoint a file path, we can step through every one of these components.
• 03:51 I'm gonna click on Source, and next to Source,
• 03:55 you'll see that it actually brings me back to the original view of my data,
• 03:58 with all the steps that I've done down below.
• 04:01 I'm gonna now click on the gear icon.
• 04:04 And that gear icon is special, it takes me back to the configuration steps, and
• 04:07 this is huge, because now I can say Browse,
• 04:11 find the December file, and say Import > OK.
• 04:18 Notice that it's now looking at a December 2018 file, Close & Load.
• 04:23 This is going to reload 48 rows from that particular data set, but
• 04:27 when I hit refresh all, you watch what happens to the pivot table.
• 04:30 My 12818 suddenly becomes 13432, because that's what's in the December file.
• 04:37 As long as the data is within the same structural format,
• 04:40 which it will be if you're getting it from a text file on a regular basis, and
• 04:44 you get those rules right, you'll be able to reuse this any time you want.
• 04:47 All you need to do is edit the query, repoint the source, or
• 04:50 even just save over the file with the same name, refresh it, and
• 04:54 away you go, you're reusing your business intelligence.

Lesson notes are only available for subscribers.