Getting Basic Data

Quick reference

• 00:04 Let's being our Power Query journey now.
• 00:07 The first thing though, we got to figure out where are we even going to find
• 00:10 the button to go and pull in the text file that I wanna work with here.
• 00:14 Well, if you are in Power BI desktop, you go to Home and Get Data.
• 00:18 If you are on Excel 2010 or 2013, you go to the Power Query tab.
• 00:21 In Excel 2016, you're gonna go to the data tab.
• 00:26 Now if you're on subscription, you'll have a big Get Data button over here like I do.
• 00:31 If you're not, right about where the Refresh All button is,
• 00:34 you'll see a New Query button.
• 00:36 Regardless of which way you come in, we're gonna start that new query,
• 00:39 and we're gonna say From File, From Text or CSV.
• 00:44 When we do that it'll take us to a place where we can browse.
• 00:47 We can drill in to find out where we stored our GoSkills data,
• 00:50 and we're gonna drill into the data from CSV folder.
• 00:54 And I'm gonna choose the 2018-01-23 CSV file, and we'll say Import.
• 01:00 At this point,
• 01:02 you're gonna get a preview window that's gonna pop up that looks like this.
• 01:05 And it does a couple things.
• 01:06 It talks about the file origin that nobody ever really cares about.
• 01:09 It tells us that it's a comma delimited file.
• 01:11 Now, Power Query's pretty good at detecting delimiters for
• 01:14 us, which is great.
• 01:15 And it also tells us it's gonna base its type detection for
• 01:18 the different data types based on the first 200 rows of data.
• 01:21 All of that's fine, one thing that concerns me a little though,
• 01:24 it looks like the Load button is highlighted.
• 01:27 But we're never gonna load data without actually manipulating it
• 01:29 anyway because that's what we need to do.
• 01:32 So let's click Edit.
• 01:34 This is gonna launch you into the Power Query editor window.
• 01:38 And there's a couple things I wanna call out here real quick, and
• 01:41 they're on the right hand side.
• 01:43 Notice that we have a name.
• 01:45 This name will default to the file name.
• 01:47 But this is gonna be the name of the table that's created when we are finished.
• 01:50 So we're gonna change this to something that's a little bit more logical
• 01:53 like transactions.
• 01:56 Underneath the name area, you'll notice this applied steps window.
• 02:00 And this si where the real good stuff happens in Power Query.
• 02:03 You notice if we click on Source, it changes the view on the left-hand side.
• 02:08 This is looking at the raw CSV file that we actually imported here.
• 02:12 Now, it is broken into columns based on the comma separated values,
• 02:15 that's what CSV stands for.
• 02:17 So that's already set up, but you'll notice that the first row
• 02:20 looks like headers, and there's a whole bunch of data underneath it.
• 02:24 Now Power Query identified the same thing you did, that looks like headers.
• 02:27 So it said, you know what, I'm gonna take the liberty of promoting those to headers
• 02:31 for you and make those the headers for our columns.
• 02:34 It then also makes a guess based on that first 200 row preview
• 02:38 of what the data types are and sets the data types for
• 02:41 each individual column as you can see on the top with the ABCs and the 123s.
• 02:46 At this point, I'm gonna make some manipulation to this data.
• 02:49 I'm gonna quickly scan the columns I'm gonna say, you know,
• 02:52 I don't know if I'm doing a sales analysis that I really need average price.
• 02:56 I've got units sold, I've got gross sales dollars, don't need this.
• 03:00 So I'm gonna right click and I'm gonna say Remove.
• 03:02 Now, I wanna be clear.
• 03:04 This does not remove anything from the underlying file.
• 03:07 This simply means that when Power Query brings the data in,
• 03:10 it's gonna say, don't bring me that call,
• 03:12 and it's not gonna bring it into Excel to begin with, which is great.
• 03:16 The next step is we can start actually cleaning things up a little.
• 03:20 Notice this first column, POS chit hour, stand for point of sale chit hour.
• 03:25 That's kind of like the invoice hour in food and beverage.
• 03:29 That's what a chit is an industry specific term.
• 03:32 But I don't really care for this thing.
• 03:34 I just want to see hour.
• 03:35 So I'm gonna double click on it, and I'm gonna change this to just be hour.
• 03:40 There we go.
• 03:41 The rest of the column names, they actually look pretty good, except for
• 03:44 maybe this gross dollars.
• 03:45 I'm gonna try another way here.
• 03:47 Right click, Rename.
• 03:48 And we're gonna change this one to gross sales,
• 03:52 because that's a little bit more descriptive.
• 03:55 Now I'm gonna scan the footprint of my data.
• 03:57 I'm gonna say, you know, hours, they look good.
• 04:00 Item name, well you know what?
• 04:02 If I go and look at this column here, course break don't make fire order.
• 04:06 These are actually all commands that get sent to the kitchen to tell you whether or
• 04:11 not the server wants to actually have a rest for the people to enjoy their meal,
• 04:16 or maybe they don't want something to be made because it's ringing and
• 04:19 something to correct something else.
• 04:21 Or the fire order says hey kitchen let's get on it and
• 04:23 get this meat on the grill so we can get out to a table.
• 04:26 But I don't really need to analyze any of these sales.
• 04:28 None of them are gonna have a price.
• 04:30 I don't care how many there are, so I'm gonna get rid of them.
• 04:33 And to do that, I can click on this little filter arrow and I can say, there's
• 04:38 three of them here, but I don't know, because it says list may be incomplete.
• 04:42 Let's load more.
• 04:44 Well, that load more message went away, so it has scanned the entire data set and
• 04:48 it's found that these are the only three items that start with a star.
• 04:51 But I don't know what might get added later on.
• 04:54 So, to be future cautious, I'm gonna do a text filter,
• 04:57 and I'm going to say, I don't want rows that begin with.
• 05:02 So we're gonna remove all rows here, we're gonna keep, rather,
• 05:06 all rows that do not begin with a star.
• 05:09 And now it's gonna remove those.
• 05:11 Again, it doesn't compromise anything in the underlying file.
• 05:14 It just says, when Power Query goes to load this file, don't bring those in.
• 05:18 One other thing I wanna change, when I look at category,
• 05:21 when I click the filter here.
• 05:22 You'll notice, again, a load more, let's click on that.
• 05:25 All right, that's all of them.
• 05:27 Notice coolers/ciders and notice soups/salads.
• 05:30 My manager doesn't like slashes.
• 05:32 He would rather see a space and space.
• 05:35 So I'm gonna make a nice little replacement here.
• 05:37 Right click > Replace Values, and
• 05:41 instead of using the slash, I'll use space and space.
• 05:46 And that's gonna change all of those as they come in so they look a little nicer.
• 05:50 And speaking of looking nicer,
• 05:52 how much do you like these capital letters in here yelling at what's going on?
• 05:55 I'm not really a big fan of that either.
• 05:57 So you know what, we also have the ability to right-click, Transform, and
• 06:02 transform to capitalize each word.
• 06:05 Now there's lots of things we can do in Power Query.
• 06:07 The key piece here, though,
• 06:09 is we've gone through and we've made changes to the data to get it perfect.
• 06:12 Now as a matter of a best practice, the last thing that we should do is
• 06:15 redefine our data types before we load our query, even if they're already set.
• 06:20 So here we have hour, it's set to whole number.
• 06:23 I'm gonna force it by clicking on the one, two, three, and
• 06:26 just force it again to be whole number.
• 06:29 Item name, category, and
• 06:30 group, I'm gonna actually click Item Name, hold down my Shift key, and
• 06:34 click Group because this way I can set multiple columns at the same time.
• 06:37 Right-click, Change Type, and we'll force it to text.
• 06:42 Unit sold right now is set to a whole number.
• 06:45 I'm not actually happy with that because in my time in food and
• 06:48 beverage I actually did see people sell half a muffin.
• 06:51 So if we use a whole number data type we'll lose half of that muffin.
• 06:55 So we wanna have a decimal number for that, just in case.
• 06:59 And our gross sales, well, you know what?
• 07:01 This one probably makes more sense as currency.
• 07:03 The only real difference between decimal number and currency is that currency
• 07:07 is always rounded to four decimal numbers like your foreign exchange.
• 07:11 And now that we have our change type step at the end and
• 07:14 everything is locked down with data types, we're in the perfect position to go and
• 07:17 say hey, let's go close a load.
• 07:19 And at this point, Power Query is gonna run through all the data in the file.
• 07:24 The original part we were working with was just a preview of about 1,000
• 07:27 rows to make it nice and quick for bigger data sets.
• 07:29 It's now loaded 4,530 rows.
• 07:32 Every single line in that file has been examined, modified,
• 07:35 changed, landed in this table and it is perfect and ready to go for
• 07:39 working with other things like pivot tables and charts and other Excel logic.

Lesson notes are only available for subscribers.