The Value is in the Refresh

Quick reference

• 00:05 I honestly believe that Power Query is one of the most amazing tools to come out in
• 00:09 20 years for Excel.
• 00:10 But I'm also not oblivious to the fact that it's got a pretty big problem.
• 00:14 And that pretty big problem is that when you
• 00:17 start building new queries against datasets It hard codes the file paths.
• 00:22 And that's really unfortunate, because it makes sharing files very difficult.
• 00:26 As a matter of fact, all the files that I've shared with you through this course,
• 00:30 if you just open them up and try and refresh them, it's gonna work.
• 00:34 Because all of the data sources are hard coded to the path on my PC, not yours.
• 00:39 So unless you've emulated my file path exactly the same,
• 00:42 it's just not gonna work.
• 00:44 And the same problem's gonna happen when you start to share files inside your
• 00:48 organization, so we need to show you how to fix this.
• 00:51 Now the first thing I wanna do is I wanna show you how to bring up the queries pane
• 00:54 on the right-hand side.
• 00:55 This is not necessary for Power BI desktop, but it is in Excel.
• 00:59 In 2010 and 2013, you're gonna go to the Power Query tab, and right around
• 01:03 the middle of that tab, you're gonna find a button called Show Queries or Show Pane.
• 01:07 On older versions of Excel 2016, it's gonna be on the Data tab,
• 01:11 right around here, there'll be a button that's called Show Queries, and
• 01:14 in newer versions, you'll have Data Queries and Connections.
• 01:18 No matter which way you go, it's gonna pop up this little pane on the right hand
• 01:22 side, that shows a certain list of transactions.
• 01:24 Now this will show us all of our queries, and one of the things that's kinda
• 01:28 interesting here, is when I now go to Data Refresh All and it fails to find the file
• 01:33 path, it'll actually indicate that with the download did not complete.
• 01:38 Which is great, but we need to fix it.
• 01:40 So let's right-click on this guy here and say Edit, in Power BI desktop,
• 01:45 you just go to Home> Edit Queries and you're good to go.
• 01:49 Now you'll notice at this point, that I actually have a preview that's telling me
• 01:54 it couldn't find the file, you may or may not see this.
• 01:57 If you don't, if it still shows data and
• 01:58 it won't refresh, you need to click the Refresh Preview button, and
• 02:02 that'll refresh everything in here so that you can get the exciting error message.
• 02:07 But how do we fix it?
• 02:07 Well, there's a couple things I want you to call, or want you to notice here.
• 02:13 The gear icon on the right hand side exists to about four steps here,
• 02:16 and this is a very special little icon.
• 02:19 What we're gonna do is we're gonna click on the Source step, and click that gear.
• 02:23 And you'll notice that when we do,
• 02:25 it's gonna pop up the step that configured all of this stuff that happened.
• 02:30 So this is really cool, because now we can actually start changing things.
• 02:33 If we know what the problem is, in this case here I'm missing a space in my sales
• 02:37 name, I can just hit space, and I can say OK, and everything is gonna update.
• 02:43 If I didn't know, I would obviously hit the browse button and try and
• 02:46 find the file.
• 02:46 Once I've replaced it and got everything fixed, you'll notice that I can now step
• 02:50 through all of the individual steps, all the way to the end.
• 02:54 And I can go and hit Close and load, it will reload the files and
• 02:59 everything is good to go, we've now got our pivot tables
• 03:03 that are available to be refreshed, by hitting Data Refresh All.
• 03:06 You'll notice it reloads Power Query my pivots are up to date.
• 03:09 But these were the sales for January 23rd,
• 03:13 I wanna see where the real value in Power Query comes in.
• 03:17 I've already got two pivot tables built,
• 03:19 I've built a bunch of logic around this and I've got sales for the next day.
• 03:23 They've been exported from my system to a CSV file, and
• 03:26 what I can tell you about this is because they've come out of my system,
• 03:30 using a specification to generate a CSV.
• 03:32 That specification was written by a programmer in, I don't know, 1980 and
• 03:36 he's never coming back to change it.
• 03:38 So, we know the files are consistent, that's the beautiful thing here.
• 03:43 As long as the data is consistent when we build our patterns, if we've read them
• 03:47 correctly, we will be able to just point to another file and refresh it.
• 03:51 So, lets go check it out and do just exactly that.
• 03:53 We are going to right click on Transactions we'll say Edit,
• 03:58 we're gonna go back to the Source step.
• 04:01 We're gonna click the gear icon to bring up the configuration step and
• 04:05 we're gonna choose browse.
• 04:07 And here's the January 24th sales,
• 04:11 we'll click on it and say Import and we'll say OK.
• 04:15 And now if we've got our patterns all done correctly,
• 04:18 you'll notice all of the data comes in it's changed.
• 04:21 If I go on step through I can see exactly what's happened all the way through
• 04:24 the file again at a consistent columns and consistent patterns of data which is good.
• 04:29 So now I go all the way back to the change type one, now I don't have to I could just
• 04:34 change the data source and call it a day and say Close and Load.
• 04:38 And you'll notice that my 4530 rows get refreshed, the new file had 4552.
• 04:41 It runs through the same process with every line which is really cool.
• 04:46 If I go and look at my sales by category now the sales are the same, but
• 04:50 you know what?
• 04:51 That's because sometimes pivot tables are disconnected from the data, so
• 04:55 we actually need to do a refresh all sometimes unfortunately twice.
• 04:59 And if we Refresh All now, you'll notice that we have $4.4 million of
• 05:03 the sales coming from this transaction file.
• 05:05 So this is beauty, this is the value of Power Query right here,
• 05:08 all that hard work we did before to manipulate those files and
• 05:11 all the hard work we did before building our business intelligence of pivot tables.
• 05:16 Now we can just go in, change the source file,
• 05:18 click that gear icon, point it somewhere new and hit Refresh.
• 05:22 Of course, if we don't wanna do that, if we save yesterday's file over today's file
• 05:26 with the same name, then we just hit Refresh and we're good to go.
• 05:29 That's the value of Power Query, is that automated refresh so
• 05:32 you don't have to relive the job several times.

Lesson notes are only available for subscribers.