Skip to content
002: Power Query with Ken Puls episode artwork

Jul 31, 2015 57 min

002: Power Query with Ken Puls

from Learn Microsoft Excel with MyExcelOnline

Ken Puls is an Excel MVP who is the founder of long standing Excel website & forum called ExcelGuru.ca He is an expert in Power Query and has co-authored the book M is for (Data) Monkey: A Guide to the M Language in Excel Power Query 👉 Join Our Academy Online Excel Course 👉 Show Notes: https://www.myexcelonline.com/podcast/002-power-query-with-ken-puls-from-excelguru/

Episode details come from the podcast's RSS feed.

Embed this episode

Ready to play

002: Power Query with Ken Puls

0:00 57:44
of matches

Transcript Auto-generated, may contain errors

This is the MyExcel Online Podcast, episode number two. And to me, I was like, oh, wow. I mean, this thing changed your life. Welcome to the MyExcel Online Podcast, the need to know Excel insights, knowledge, and tips brought to you by the experts that know them best. Are you ready to explore your full potential and get better at Excel? It's time to stand out from the crowd with your host, John Mikalutis.

Good day, guys, and welcome to the Microsoft Online Podcast with your host, John Mikalutis. In today's episode, we have an Excel MVP. He was an expert in Power Query. He started his career as a public accountant and worked in several companies, honing his Excel and IT skills. In 2004, he started a website called ExcelGuru.ca, which hosts a large knowledge base of Excel help articles, a blog, an active forum dedicated to helping users with their Excel issues. He was awarded the prestigious Microsoft MVP Award in 2006 and has held it every year since. He has also been recognized as one of Vancouver Island's top 20 under 40 business and community leaders.

He's co-authored the book M is for Data Monkey, a guide to the M language in Excel Power Query. Today, he works as a full-time Excel consultant teaching the Power BI tools to the masses. Let's welcome from British Columbia, Canada, Mr. Ken Pools. Hi, Ken. How are you, buddy? Hey, John. How's it going? I'm doing well, man. How are you? Good. Thank you. Good. Mate, I've told our listeners a little bit about your background. Let's go into a little bit more detail of your journey into becoming an Excel expert. All right. So I started working in public practice accounting way back when I was in industry after that. And I guess the piece that really was the telling day for me, which kind of got me a little bit hooked in Excel, was...

And we always had to use spreadsheets as we were going through and doing our accounting work. I mean, that was no surprise. But when I started working with a company called Fairwinds, and this was a fairly big place at a golf course, a marina, and property, food and beverage establishments, and all kinds of stuff. It's kind of an amusing thing. We got purchased by a pension fund, and they sent their IT department in, and they gave us one version of Excel across every desktop. And what happened at that point is that I was the IT guy there, if you like, and the network administrator at the time. So I had to phone Toronto and say, listen, guys, our macro broke when you put this new version of Excel in. And they said, okay, great, send us the file. So I sent the file, and then they phoned me up, and they said,

can you tell us a little bit more about this macro i said well okay well it did this i said the first thing you did was you press slash shift and down arrow to the right three times and For anybody that worked with Excel back in the day there, you know that by hitting the slash key first, you actually kicked off a different series of keystrokes than what we were sort of normally used to doing. As it turned out, I didn't have a macro in the file at all. We thought it was, but what had actually happened is that the users that I was working with had learned the Lotus keyboard shortcuts, and we thought those were a macro for doing our job. And when they gave us Excel 97, the IT department had not turned on the Lotus keyboard shortcuts, so we thought everything broke. So that was where things sort of started with it. It was a little bit out of ignorance. And they said, you know, look, hey, we'll build you a macro, and we'll send it to you. So it'll get your job done here. What do you need it to do? So we explained the whole thing, and they did build a macro. They had one of their summer interns do that.

And then as summer interns do, they leave. And we had to make a change to the macro. I, being the IT guy, got the job of doing it and opened it up and sort of looked through and figured out how it worked and whatever else and just tinkered and played around. And I made a change to it and it worked. And I thought, oh, this is kind of neat. So I started playing around and realized we could record macros. And it took a couple of years, I think, of tinkering back and forth with this. And then we had a really monumental change that got pushed on us. Our asset managers came into us and said, listen, guys, we've decided you're overstaffed. You need to cut your staff burden by 33% by next week.

And by the way, we're going to ask you to do more with less. And we want it on the same time schedule. Actually, no, let's make it a little bit tighter. We need a couple of days earlier. And it needs to be just as accurate as it was before. So all the reasonable demands you get from upper management, less resources, tighter deadlines, and the same request for accuracy. So at the time, I had just started playing around or been tinkering with VBA and automation in Excel. And I told my boss, look, man, this is the answer. We're going to have to do this.

And so I kind of threw myself into Excel and started to live in it for, I mean, probably a couple of years, actually, I guess, by the time it was all done. It seems a long ago now. And I really kind of became an addict for the program and seeing what it could do with automation and playing around with it. I started asking questions in forums. I started feeling guilty about how much knowledge I was sucking out of those forums. So that's where I started my own website and started to put things, you know, different techniques that I'd done out there onto my website, which has grown over the years into something that I'm pretty proud of. And yeah, I mean, it just kind of grew granularly. I mean, the key for me was need. I mean, we had a need to get stuff done. And, you know, that was the catalyst that kind of kicked it off.

You know, from there, I got to say, I mean, I've seen some pretty cool things happen in my life from the MVP side. I mean, I got an email from NASA one day. I thought this was wild. I actually thought it was spam and I replied to it anyway. And it turned out that they were adapting one of the techniques I had posted on my website and they were using it to automate the production of PDF reports that monitored the International Space Station's power levels to make sure that it wasn't going to prematurely fall out of the sky. So pretty neat stuff. I mean, it shows you where Excel can go. So it's pretty amazing stuff, isn't it? When you get NASA, you get a call, isn't it? You know, I was out with my daughter a year ago. We were actually at the school and it was a night gazing thing because we were there to watch the International Space Station fly overhead. So I got to tell my 10 year old daughter, look, there goes my project.

That was pretty funny. Nice, nice. And it didn't fall out of the sky, did it? No, no, not yet. Not yet. And as soon as it does, I'm going to say they adapted to code themselves and somebody else is sad.

Because you've been around when Excel version 97. Is that when you first started? Yeah. I mean, the first version of Excel I ever used, I think, was actually probably Excel 4 or maybe Excel 5. But you know what? I mean, at that time, I mean, it was one of those, you know, take your kid to work days. And my dad took me to work. You know, it's that long ago. And that's a long time ago now where I really started to make a change to it or make a change to the way that I approached it. We were using Excel 97. That was the big year for me that I saw that consistent experience. We had VBA and that was what really hooked me into the program. So it was from there on that I've been around playing around with every version since. One of the first websites when I first started out learning Excel was Excel.

ExcelGuru.ca and you have a lot of knowledge-based articles. You've got access, you've got formulas, general tips, pivot tables, you've got everything there. You've also got a forum where people ask questions and other users give answers. It's been around since 2004. How have you seen the evolution of ExcelGuru.ca and also how has it helped the users over the last 11 years?

Well, the evolution of ExcelGuru.ca, when I first started my website, I actually had a website under my family name domain, and it had two pages of Excel stuff on it. And then I thought, you know, I'm going to go and I'll register ExcelGuru.ca. And so I did that. And I think when it started, it was a static website. It didn't have a forum. I put on a content management system. I believe it had four pages to start with. So, I mean, when you look at the evolution of what's actually happened there, it's hard to believe it's 2004. I mean, it's 11 years. I can't believe I've had it that long, to be honest with you. It's something that's really grown over the past.

bit of time it's become more popular i think with the forum edition i mean that really helps in a lot of ways with with getting people answers to customizing things the way they need it helps with getting you know your google search rank too so that more people find it and can find those answers as well but i have some great volunteers on that site that spend a lot of time helping people out and giving them answers they're wonderful people i mean and you know i know they do it because they like helping they like learning but it's a neat community and i can't say enough about the forum world and how much knowledge you can earn there uh that's where i learned everything i know about this i mean i've never taken well i'm not sure i took one excel course in my entire life and i was bored um so you know you look at what's there and available for free with the articles that we've written and the help that people can answer. I mean, post your workbook, ask your question and get stuff up there to help people learn. It's amazing, isn't it? I mean, it's such a cool community kind of field that is out there and available. It is. And a lot of the Excel experts that I've interviewed have used forums as a base to learn Excel. That was before you had YouTube and all the video tutorials.

Yeah, well, going back even further than that, I mean, it used to be news groups, right? I mean, I was never a news group guy. I mean, I was always more of a forum guy. And honestly, I mean, YouTube and stuff like that hasn't been my bailiwick. I mean, I'm more of a guy that likes to write and read than watch video personally, which is ironic because I do a lot of training videos. But I don't know. I mean, I find it fascinating how many people sort of jump on these things. But the thing I'll tell you about forums, and this is where I find the real value in it, is that people can show up. They can ask their questions with their own business problems. I mean, they have their own goal to solve. And it's always better when you're trying to learn to be able to use and work with data that you understand and you identify with. And then the second part is, I mean, when you say like, you know, hey, look, a lot of

of the Excel experts that we've interviewed have gone to forums. Well, my feeling is that, I mean, you know, there's two ways to learn. The best way is to try and teach somebody. And that's what I love about it is that I would go to a forum and I would try and help somebody out. And when I was really new at it, I'd post a solution and I'd get one of the other MVPs or an expert at the time would come along and say, you know, Ken, that's a good solution, but this one's better and here's why. So not only are you actually working through the problem yourself and working out what somebody else couldn't necessarily do because they didn't have enough background knowledge, but then you're getting somebody else that comes along and says, well, hey, look, I mean, let me expand on that and show you another way. And this is the part that I love about Excel. What makes it so fantastic is that I'm fond of telling everybody when I'm teaching classes, there's usually at least

three ways to do anything in Excel. That's right. The question is, which ones have you discovered and which ones are most efficient for you? So that's what's so neat about it, is that you see all these different feelings and different ways of looking at stuff when you get multiple people involved. That's why I love Excel, because you have different ways to skin a cat, as I say. And I'm having a look now at the stats there. You have threads, just over 4,000 threads, 18,000 posts, nearly 40,000 members. And currently, there are about 200 users online. And if one of our listeners has an issue with their data and wants to post, what is the process that they should go through?

Well, basically, I mean, you head over to ExcelGuru.ca and you register for the forum. It'll ask you for your email address and a user ID. It's free. Oh yeah, it's absolutely free. And the forum will always be free. So when you put in your user and email ID and username and whatnot, it does run you through a verification process. And the reason that we do that is that we're trying to make sure that we don't get spammers on the site. They're a real pain and they don't add any value. So once you've signed in, the nice part, what I love about my forum is that you can actually upload a workbook with your sample data. We do ask that you try to make sure that it's not sensitive because we like to leave those posts around so others can learn from them. And then basically ask your question. The kinds of questions that get the best answers or the most immediate answers are ones that somebody has come along and they've clearly explained, this is what I'm trying to do. I've got an example file here.

that I'm working with and I've tried to do this, but it isn't working for me. And how do I make that work? Those are the best because what we see in that, and here's the secret to getting good help from a web forum is when we recognize that somebody wants to learn, those are the people that we bend over backwards to help. If we get somebody that comes in and says, my boss needs this now, we kind of don't tend to focus on those as much because why is the boss paying you and not us? If that's the case.

It's a free place to get help and it's a free place to get answers and whatnot. But the goal that we have is to help you learn how to do this stuff on yourself and hopefully encourage you one day to come back and actually help other people out in the same way. I mean, that's the end game of it. So key pieces to recognize, I mean, everybody there is a volunteer. And basically that, I mean, you're posting your stuff and they work on their schedule. So don't come back and say, hey, I need an answer now because that doesn't work very well. But if you want to learn and you're willing to take the time to do it and work with people, I mean, I've seen threads that have gone back and forth like 90 times to try and get their user the answer that they need and help them work through their business problems. I mean, it's pretty simple.

staggering when you see that happen. It's cool. All right, guys. So excelguru.ca backslash forums. Go on there, sign up, and I'm sure you're going to get lots of value out of the forum and some free help as well. So the only caveat is don't tell them that you need your answers straight away because you need to shut down. All right. So awesome. A free learning tool there online and a great way to increase your Excel development. Now let's go on to your subject of expertise. Tell us how you become an expert in Power Query. Trial and effort.

That's probably the most honest answer. You know, honestly, I mean, I've done a lot of exploring of data. So we should probably circle back, I guess, just for those people who aren't familiar on what Power Query actually is. Power Query is a free download that you can get. You can just throw it into Google or Bing or whatever your favorite search engine is and say, download Power Query. As long as you have Excel 2010 or 2013, you can install this thing. But the nice piece is this adds a whole new set of functionality to Excel. If you look at your Excel without Power Query and you go to the data tab, you've got about four or five icons on the left-hand side that say, can you get your data from Access or can you get it from different places? Power Query is essentially a...

monster application to help you get and transform and clean up data into an easy to use format. So it basically lands into an Excel table or into PowerPivot. You're using PowerPivot. Why this is so important and how did I become an expert in this thing actually sort of go hand in hand is that what I know working as an accountant is that it's often very difficult to get your hands on good, clean data that's ready to be used and ready to be consumed in a pivot table or in a charge or whatever. Instead, the data tends to float around the world in text files or in Excel files. We've got all these massive databases, but a lot of times the business user inside the organization doesn't have access.

To get to that database, whether it's for political reasons or technical reasons is irrelevant. The reality is that they just can't get there. So when they're looking for something, it comes to them in the shape of a text file. My favorite text files are always the ones that have, they look like they were stylistically drawn in about 1980. So they use the minus sign for underlines and the equal sign for double underlines. And they've got repeating rows with blank header and garbage, all kinds of stuff in them, non-printing characters. And until Power Query came along, the solution to dealing with this thing was twofold. You had two choices.

You could either try and import it through that postage stamp size import text file window in Excel. So you could do a certain amount of cleanup or a certain amount of breaking it apart into columns or whatever else. And then you could land on the table and you'd have to do more work to clean it all up and get it done. And then next month you'd get to do the entire process all over again. So, you know, when you look at it, you've got these guys that, I mean, accountants and business analysts are spending all this time reliving the joy of cleaning their data every single month. It's a waste of time. Let's be honest.

Well, they do, but it doesn't add value to the organization. If I look at it in accounting terms, I look at it and go, that's an expense. I mean, plain and simple. It's a cost of doing business, but it's not earning us anything as far as business intelligence goes. So, you know, the other opportunity, if you started to work into Visual Basic for Applications, which is the programming language inside Excel, you could write an interface using VBA code to import the stuff and clean it up and whatever else to get it into that format. Now, that was good because now we would invest some time, but we would have to invest into a learning curve on VBA first. Once you have that, then you could invest some time and you could build the solution and then you could click a button and have it import. That was great. But those solutions were, depending on how you programmed, it could be a little bit fragile. So then Power Query came along and Power Query is...

I mean, it's really interesting because I think it's actually one of the most well-designed user interfaces because you can do so much with a very little knowledge. And yet with a lot of knowledge, you can do amazing things. But the learning curve on Power Query is not very steep. So what I was able to do is I was able to take a text file and I said, OK, well, I'm going to import it. And right away, I got a full-size window, which is so much better than the postage stamp I was working in before. And you could go step by step and do these transformations to clean out your header or to promote your header rows and clean out garbage rows and split things up into columns and change the data types to what they need to be. And then it would land in an Excel table. But where the real beauty came in is that it was faster to do that in Power Query than it was the classic Excel route. And not only was it faster to...

do it in power query but when you were done you'd right click your table next month so you'd get your new text file you'd save it over top the old one you'd right click your table and you'd say refresh and it would open up the text file and it would run it through all the same steps you did last time and land in your table so you're done in like 10 seconds and to me i was like oh wow i mean this thing changes your life because now all that time you've been wasting your organization cleaning up data suddenly became a holy cow i did it faster and i can reuse it so i mean this became a big big piece So once I saw that, I was hooked. And then I started playing around. I mean, you know, that's a text file. But you can import CSV files and database files and web pages and so many other pieces. You can bring them in. You can start changing them. You can flip. There's so many different pieces you can do. You can merge different data sets together. It really started to open the world into data. Where this became extremely impactful is that most.

excel users deal with dirty crappy data on a regular basis and this to me became really really obvious when i actually worked on a consulting project with a guy in australia and he was building a massive power pivot model it would take him like three weeks to do iterations then we'd hook up online and i'd give him some recommendations then he'd come back he says hey i rebuilt it again and i spent two weeks doing it this time i went holy cow okay um so then he'd ask for some more feedback and i'd give him some more he came back he says yeah i rebuilt this one this one it only took me nine days this time that was great and then we talked about power query and then he phoned me up he says hey i need to talk to you about power query i said okay great he said i rebuilt my model again i said really how long did it take you this time he said 16 hours and i said how did you go from like Nine days to 16 hours. He says, you know what? He says, I reshaped all my data with Power Query. And he says, and once I did that, all the crazy DAX formulas I was writing in Power Query, I didn't need them anymore. My longest formula is one line long now. Wow. And I went, holy cow. And this is what the really important piece of this came out is that why it was taking him so long is because his data wasn't in the right format to be used properly. And then Power Query, he could basically record and thereby accidentally automate.

the process of bringing his data in in a format that Excel wanted to use in order to serve up its data properly. And that was the big thing. No more wild backflips to try and get this into a usable format. Instead, we've actually got it into a format that Excel is designed to work with to start with. That's what the power of Power Query is and why I got so incredibly hooked on it. And I'm sure there's millions of people out there that are facing this issue every day and are not using Power Query. They're probably doing the manual way or they're using VBA macro. And for me, Power Query is VBA on steroids and even bigger than that, isn't it?

Well, it kind of is. I mean, I guess the one thing I want to say is, I mean, don't confuse Power Query with VBA. I mean, the programming languages are very different, although the, and that's the part where I kind of say that. I mean, call it semantics, if you will. But if you think you're going to take your VBA knowledge and port it into Power Query, think again, because they're totally different. And that's one of the reasons why we're writing the book, M is for Data Monkey, is to help Excel pros understand that programming language. But, you know, the key thing, though, is that it does have some of the same functionality that we have in the Visual Basic Editor. And the fact that in VBA, we hit record macro and we go do our steps. And if we go back and look into the Visual Basic Editor, we can see that it's recorded all of those individual steps. With Power Query, we don't even have the same.

record macro we just open the thing up and start working and it does actually record every single step that we do and we can step backwards and forwards in them extremely easily so for that side it kind of has that vba like feature set but the underlying language is inherently different i mean i would certainly say to call that out having said that when you work with vba you can record your macro and you have to go back in if you ever need to tweak it you need to learn how to read vba code Inside Power Query, you can record the steps of what you're doing. And if you need to go back and tweak them, you can go back in and you can actually select different step and you can make modifications without ever having to learn any coding whatsoever. Now, if you do learn coding, though, then it takes it to a whole nother level where you can do some amazing things. But that's sort of the difference between those things. I mean, honestly, I wish VBA had a user interface that was like Power Queries and recording steps in a way. That would be really cool because I think it would allow a lot more people to get into VBA. Power Query has a much easier bridge of entry.

Yeah, Power Query is just awesome. I got goosebumps talking about this. Yeah, no, I can feel it. I can feel it. And what I'll do, I'm going to put some links on the show notes on how to install Power Query because some people may not know how to install it. And it's pretty easy to do. So once you install it, you get this ribbon that says Power Query. And Ken, can you tell us just in a few words what each of the groups do? Now, the first one on the left is Get External Data. So Get External Data is how you would go and reach out to actually pick up a data source from a variety of different places. So if I'm looking at the Get External Data, we can pick things off from the web. We can pick things out of different files. I mean, there's all kinds of different file types that are actually set up inside here.

We've got Excel files we can pull from, CSV, XML, text files. One of the coolest features in there, though, is that there's the ability to actually import every single file in a folder in one shot. Now, I love that. If you've got a bunch of text files or a bunch of CSV files, you just point to that folder and you open it up and it gives you a list of all the files. If it's a CSV or a text file, you can combine them all by clicking one button, all of them. So if you figure that you've got your January, your February, your March transactions are in three separate files and you save them in that directory, you can import from folder and combine them by clicking.

one button, which is pretty amazing. So we got from databases, from Windows Azure, if you're using your cloud hosted services, from other sources, man, other sources, there's all kinds of stuff. I mean, I've seen Salesforce data combined. Actually, that was a cool story with Power Query. I can just share this. I was at the past business analytics conference.

And I had a gentleman came up to me and he says, hey, I need some help with this thing. I'm trying to work out these Salesforce reports. And I said, great, let's see what you're doing. So he showed me. And now he was working on a Mac and that was his first challenge. And the reason I say that is because unfortunately still today, Mac doesn't have any, the Mac version of Excel, even the most recently released Mac Excel 2016 doesn't have any power pivot, power query, power map or power view features. Anything that says power in front of it is dead, unfortunately, for the Mac. So we got him. I said, look, I'm going to show you something. So I booted up my Windows Excel and I said, look, I want to show you this power query thing. Let's connect to your Salesforce data. I said, I'm going to need you to put in your password, but we'll wipe it when we're done. And we did. But we basically connected to his Salesforce data set.

five minutes, we rebuilt the report that's taking him two weeks to build. Wow. Yeah, Darn writes wow. So he's running straight back to the office and say, I'm going to install Fusion or Parallels and get Windows Excel on this machine because I got to have this. This is crazy how much time I'm wasting. So pretty neat stuff there. I mean, you can, you know, there's Facebook data sources. Even you can pull email right from Microsoft Exchange or your contacts or calendars, SAP, other stuff like that. So that's pretty cool. All of those, I mean, it's a huge variety of different data sources. The web ones are kind of interesting because as long as you can get to a table or a page that has a table in it, those are the easiest to use, although you can parse raw HTML if you want. But I've actually used that to go and reach out to Weather Underground and actually pull weather forecasts and historical weather directly from an unmanned weather station.

on a rock in the middle of the Strait of Georgia, just off the coast of Vancouver Island. We, we use that so we can actually pull it in and put it together with our golf course data. So we had actual accurate weather to go with our sales statistics, which is pretty neat because it's crazy in a golf course. Oh, it's neat. I mean, you look at that and you go like a few years ago, you'd say, well, what's the number one driver to your business in golf? Well, it's weather, but we don't capture that.

But these guys do. So let's grab it and put it together. So yeah, some pretty neat stuff that goes on there. Lots of different data sources you can get at. It's just, it's your imagination is the limit on this stuff. Awesome. And now the other one, the other group, Excel data from Table. Yeah, so Excel data, you can, you got a one click button to be able to actually pull from any contiguous data range inside an Excel worksheet. So as long as it goes from, you know, top left to bottom right, you'll grab that. If you've got non-contiguous data, there's a couple of tricks that you can deal with that. You can pull in from named ranges or you can just, you know, save from Table and expand the range, you know, as well. But that'll allow you to pull in data directly from any Excel worksheet in your workbook. It prefers reading from Excel tables.

As I say, I mean, named ranges work. All right, cool. And they combine group. You've got merge and append. Yeah, those two are two totally different things. So for anybody that's used Power Pivot, what Power Pivot is great at is taking two tables, creating a relationship between them. So it's almost like they get merged into one great big flat table without having to actually do OV lookups to get through there.

And that's great. PowerPivot is incredibly good at that. Well, you know what? Power Query can do that too. It can actually merge those two tables together. And it actually really does create one big flat table. So if you hate VLOOKUP, well, you should really learn it and get better so you don't. But if you're really not a big fan of VLOOKUP and you need to join two tables together, Power Query can actually do that for you without a single VLOOKUP involved in the equation. It can take two tables and basically stitch them into one master table. So that's kind of nice. But what Power Pivot could never do for us is it could actually take your January table, your February table, your March table, and stack them into one long table. That was something Power Pivot could not do. Power Query will absolutely do that. So we can now start taking...

multiple individual files or multiple tables. And instead of having to copy from one and paste data at the end, Power Query can actually put them all together into one really, really long table, which you can then feed to Power Pivot, which is what Power Pivot really wants to eat. So that's a good thing. All right. And how about the append? What does that do? The append portion, as long as the column headers are the same, when you append them together, that's good. If it's not, it'll actually create new columns for you for the other ones. So no big deal there at all.

And also so that if you have data and you have a company named like MyExcelOnline.com and the other one is MyExcelOnline and you have a third one called MyExcelOnline LLC, there's a way where you can group that within Power Query. Absolutely. Yeah, you need to get into Power Query from there. So once you've loaded that original data into Power Query, whether it goes into or whether you pull it from file or whether you pull it from a web page, inside Power Query itself, you can group data sets up so that you can actually group all the records so you'd only end up with three that are totaling things up. Or if you're grabbing a table of data that is pivoted, you can unpivot it. And man, that is so cool when you see that happen for the first time.

You can unpivot. You can transpose data so you can flip it on its ear. You can fill into open areas if there's a particular record you need to fill all the way down a column. There's so many things that you can do. It's really cool. Look, I've just...

dive into Power Query, the possibilities are endless. I see the possibilities are endless and it's just, it's a new tool and not many people have used it. Well, it's been around for a bit, but not a lot of people have used it. And I know that a lot of wasted time out there on just cleaning up data, on data cleansing. And Power Query does that, doesn't it? It cleans everything up. And then the next time you download the information, the data, bang, within one click, it puts it into a clean data set. Yeah, it's a gorgeous thing. I mean, you know, the other thing I think we should say with Power Queries, I mean, you know, I don't know whether or not you've covered off in some of your other webcasts with Power Pivot. I mean, you know, when Power Pivot first came out for 2010, it was so important to the product that they rolled it in.

into Excel 2013 as part of the software. Power Query, when they released it, it was actually after 2013 was released, but they made it backwards compatible for 2010. And then they also had it available for 2013. It also was going to be rolled into Excel 2016. It's important to the future story of Microsoft Excel. It's a big piece of the go-forward piece because it's going to make life so much easier for so many people.

Have you seen any weird or cool things done with Power Query? Well, actually, I can tell you one weird that I saw. I saw actually the fellow that I do Power Query workshop with tried to go and actually take a specific song and split it into a whole bunch of components so that he could measure the beats and the notes and whatever else in the song, which was... It's not a data set I would have chosen. That's not cool, right? So, I mean, that was a little bit different. I've seen, I mean, you know, cool things. Man, I just think the whole darn product is cool. But I was playing around with doing bank reconciliations using Power Query to identify, you know, what transactions are outstanding and what transactions have been cleared. The stuff that I think that's really cool is, I mean, it's cool from a technical and geeky perspective because I was actually able to build stuff that I wasn't able to do in the past. I think that might have even been one of the big catalysts that got me so excited about Power Query is that, you know, when I was working at the resort, we had a specific set of data that we always wanted to get. It came out in a format that we couldn't use.

though and with power query and a little bit of experimentation it took me about i think i've been chasing this particular data set trying to figure out how to unwind it for the better part of five years and after playing around with power query it took me about an hour but after an hour i realized that if i could bring it in and unpivot it make some changes repivot it make some more changes and unpivot it in a different way it actually served up the information that we needed to be able to drive forward and use that in our business intelligence. And it sounds kind of weird, but I don't know how long it would take. I mean, I'm an expert in VBA. I mean, five years, we never did solve that problem with Power Query. It took us an hour. I mean, and at that point, we could copy the code that it generated and send it to everybody in the organization going, here's the answer. That was really cool, but it's hard to visualize without an actual example. But unfortunately, since I left the organization, I couldn't take that one with me. So that's fine. That's fine. Now, all this stuff, you know, people need to learn this. And you have...

a course out there. You have a workshop and let's talk a little bit about that because I know it's great value and I am going to enroll into your next workshop. Now it's a Power Query training workshop. Let's talk a little bit about the course and what it's about. So the course itself can be found at powerquery.training. So Power Query being all one word. So basically what it is, it's a three-day online live workshop. There's myself and Miguel Escobar is my partner in that workshop. And basically what we do is we run the meeting through GoToTraining and people sign up and they come in to take advantage of this particular workshop. And what we do is we sort of break

it down into about 15-minute blocks that we go through. And we start off from the very beginning of, let's go through and import a single CSV file. And then we import another CSV file. And then we show how to append those two tables together. And then we say, well, that's too much work. And we start to import multiple files at the same time. So we start working at it from the very, very beginning. In all the cases, I think there's only about two or three spots where we actually don't use real live data sets. We've actually gone back to an AdventureWorks sample. But wherever we can, we're trying to use real-world data to try and illustrate why Power Query is so important as we go through cleaning up dirty data and the text files that I talked about with all those repeating.

garbage rows and whatnot. We show how to import one of those guys and how to automate that process. Forcing import formats and stuff like that becomes critically important because, as you know, being a little bit more internationally based than myself, I mean, most of the data that I get to work with is all in a U.S. date format, even though I'm Canadian. But as soon as you start, you know, branching outside of that, this becomes a huge headache for people because dates don't import right when you bring them in. So we look at how to force those kind of things, you know, pull data from the web exchange, you know, transposing data, unpivoting things. And we've got like four different varieties, I think, of different data sets that we use that are increasingly complex that need to be unpivoted that we sort of work through. Conditional logic using like if

statements, combining all your Excel files together in one shot. So what I would sort of talk about is kind of the basics. And then we go into, we have about a half day where we actually start really looking at the advanced side of things. Let's talk about the M query language. What is this language that Power Query is actually recording as it goes along? How to understand what it does? And the different advantages that that gives us, we look at creating custom functions. And this is something I'm really particularly proud of is we actually implement what we have called dynamic parameter tables. And these parameter tables allow us to actually start building dynamic solutions using Power Query so that they're always pointing to a file that's hanging off the root of whatever folder we're in. We can actually import all.

files in a subfolder then at that point in time, no matter where it's stored or what folder path somebody has to it, we can then link that into PowerPivot. We can create dynamic calendar tables. So those are the first two days where we go through from start to finish to say from knowing nothing through to like some pretty monster crazy stuff at the end. All of the example files that we use are provided. We also provide six practice assignments as well. Once the course is done, we send everybody the full HD recording for each day of those things. So you've got that to keep as well.

But I think one of the most important pieces about this course is actually day three, because in day three, we actually have a hosted Q&A session where Miguel and I go through and we invite people after the end of day two. There's a week break between that and the Q&A session. We invite people to try the Power Query, try the different techniques that they've used, and then send us in data sets that we can work with and show how we would solve the issues with their own data. So the class attendees get to send in the issues that they're having problems with. Miguel and I work through those things, and then we actually use those and demonstrate that to the attendees on day three so that you can see, hey, I was having some real problems with this. How do the experts actually go through and deal with this? And then, of course.

The solution gets to be recorded and kept so that you can look back at it afterwards as well. So to me, that's one of the big, big, really helpful pieces is not only do you get to learn all the different techniques, which I think is really important, but you get to see them in use on your own work. And that's the part that sometimes can really drive that nail home to say, holy cow, yeah, this is going to be so impactful of what I do on a daily basis. Well, that's awesome. And also, you don't have to go out of your way to travel on site. This training is done online. So all you got to do is connect at a certain time in the US and you get access to the online training, correct?

Yeah. And I mean, you know, having said that, I mean, if a company wants us to come on site and actually lead a hands on session, I mean, at that point, then that whole day three becomes a full Q&A working with their data, which is really, really handy. So we'll do that. I mean, if that's of interest to somebody. But for the most part, yes, I mean, it's all it's all conducted online. As I say, there is a recorded video of the entire thing for each of those days that is passed on to the to the attendees at the end as well. So we set up a time for the individual workshops. So we let people know when they are. And then basically, they just attend online. I can tell you that last time we had attendees for the last workshop we did from Ireland, from Germany, from the US, you know, around the world, there was people there online with us.

as we were going through and doing the examples. So it seems to work relatively well. And for those people, I mean, there were some that couldn't stay for the whole day, but they got emailed the download link for the video afterwards so they could catch up on the pieces that they weren't able to be there for. So we think it worked out really well and the feedback that we got for it was very positive. And how much is the cost of this course for the three days? The list price on the course is $595 US. So that gives access to the full three days there. The days are, just for reference on these things, that they're a five-hour day, but they do have two 15-minute breaks and 30-minute lunch in them so that people can catch up on their emails. And we're conscious of the fact that people that are coming are business professionals and can't just turn off their work.

So you get three days, which is a five-hour commitment each day, essentially, with some breaks for $595 US. Again, all the materials are passed on to you at the end of it as well. And I believe that we have arranged a discount code for your listeners, John, which you'll throw into the notes afterwards here. All right, fantastic. It costs you about $550 to get in for the three days. I appreciate that, mate. And look, for someone to go on site and do some training, the flight alone will cost $200, $300, depending on where you are. And then you've got the hotel expenses and you've got food expenses. All up, it'll cost $2,000 for a three-day training. And you're getting this for $500. And this is the future. Online workshops is the future. And this is a great, great way to learn and cheap as well, isn't it?

Yeah, well, I mean, overall, relatively speaking, I mean, I know we we have put together quotes for companies that have expressed interest on site. And certainly, you know, I mean, at the time when you look at it, I mean, those those costs do end up getting pretty high. And I mean, some companies are OK with that. I mean, you know, if they want to get somebody on site. But, you know, for a lot of us, we look at it and go, man, I mean, you know, that's out of my budget. And yet, you know, the fact that you've got access to world class trainers and really, I mean, at the end of the day, I mean, I think, you know, when you look at it, you go, geez, I mean, if it's, you know, 550 bucks for for three days and you start doing the math backwards.

It's not that bad at the end of the day. I mean, it's not like it's a one-day class. I know, you know, when I go and teach with different organizations, I mean, you know, even when I'm doing a hands-on course, sometimes people are paying, you know, $400 to $500 for a one-day hands-on class. So, you know, overall, I mean, we think that we're pretty good value on this. And we work very, very hard to make sure that the people that come agree with that by the time they're done. That's a big important piece to us is that we want to make sure people are getting value. And you know what? At the end of the day, honestly, I want to make sure that if you paid your money to come into this, we are going to change your life. You're not going to import data the same ever again. No question.

And that's a small investment, 500 bucks, because once you learn Power Query, it's going to save you hours, days, and maybe months and maybe months. So all the extra overtime hours, they're going to be gone. They're going to be gone. And it's a small price to pay if you have that luxury. So now the website is powerquery.com.

And on there, there's a PDF, correct? If people want to download it and have a look at all the different benefits of that course. Yeah. If you go to powerquery.training slash course, C-O-R-U-S-E, you can download the brochure there and take a look through what we cover, who we are, all that kind of good stuff. Yeah. I mean, if there's any questions, I mean, obviously bomb them through the contact form and we're more than happy to try and answer those as well. All right. When is the next workshop? The next workshop will be on August 26th and 27th. Every time that we put up a new course offering, it will be there. So even if you've got listeners listening later on.

after this one's closed. Keep checking the site for more because we're going to keep on doing this stuff. It's really important that this message and this tool gets out there in the hands as many people as possible. Mate, I'm sure. And I'm going to be on that training date. So make sure you reserve a spot for me. I will do so. Fantastic. And is there a limit on the amount of people that can join up? One of the big things that we want to make sure is that we're guaranteeing the quality and the attendance that we need for our attendees. So at the moment, we're trying to limit it to no more than 40 people in a session at one time. I mean, you've got both Miguel and I working on things. We're trying to make sure that, you know, while one of us is presenting, the other one is answering questions in the Q&A and stuff like that. But at the same time, I mean,

We don't want to get to the stage where we're just pushing numbers through as big as we can and not allowing us to deliver the one-on-one when we need it, especially in the Q&A session. So right now we're limiting to 40. We're playing by ear and seeing how that goes. I mean, things are still working really well after we've done this a few more times. We may decide to up it a little bit, but the number one piece to us is that we don't want to compromise the quality and being able to answer those questions in the Q&A and in the chat as we go along. All right, guys, you can find the links to this course by visiting the show notes on myexcellonline.com slash podcast. Definitely worth your time and money and go on there, sign up because it'll be sold out.

There's going to be some more dates down here, and we're going to have that on the show notes as well. Now, let's take a short break, and we're back with Ken's favorite Excel tips. Can you analyze this spreadsheet using Excel pivot tables? Sure. If you can show me how to. Introducing the Extreme Pivot Table Online Course, which teaches you how to analyze business data and highlight key metrics so you can make insightful business decisions, create interactive analytical dashboards that will reach top management.

and set you apart from your peers, which will make you more important and increase your chances of a promotion or pay rise. Developed over 15 years through extensive professional use and including real business case studies is the ultimate pivot table course. With over 200 short downloadable video tutorials accessible online anywhere 24-7. 12-month personal support if you ever get stuck and a 30-day money-back guarantee. Visit myexcelonline.com today and give your professional development the boost it deserves.

As a listener to this podcast, I'm giving away a special discount to my Extreme Pivot Table course. All you have to do is apply the coupon code PODCAST in the checkout area. That is coupon code PODCAST for a special listener discount. We're back with Ken's Excel Favorites. All right, Ken, I know that you use a lot of keyboard shortcuts every day, but what's one of your favorite and what does it do? My favorite has to be Control-Alt-V-V. So Control-Alt-V will get you into the Pay Special dialog.

And when you press your next V, that gives you values. I don't know why, but I seem to use that all the time. And I find it incredibly fast, much faster than going to the ribbon. So, you know, Control-C to copy something and Control-Alt-V-V will lock it down to values and I'm good to go. I've never used that and I'm going to start tomorrow. Thanks for that. All right. What's your favorite Excel tape? Besides Unpivot and Power Query?

Yeah, another one. Yeah. Oh, anything else? Anything else? You know what? I like one of the one of the cool things that I get to show when I'm out teaching Excel is I like to mystify people by committing the same formula to multiple cells at the same time. Control enter to lock that formula down in multiple cells is always magic. And it always gets everybody kind of going like, why didn't you just do that? So, you know, for people that haven't done that, if you're trying to write the same formula down, you know, every row or every cell in a block, if you highlight your block first, so you've got your contiguous selection of cells and then you type your formula and press control enter.

So make sure it's not Control-Shift-Enter because that gives you an array. But Control-Enter will actually lock the formula into all of those cells at the same time, which just makes you look like a genius. I mean, isn't that what it's all about? That's right. That's an awesome tip I want to use every day. Now, I know that you've used a lot of functions throughout your Excel life. What's one that really stands out and why? My favorite function to use is EO Month.

That is the function that allows you to take any date and return the end of the month. It's always been in Excel, but it was always in the analysis tool pack, so you had to turn it on. But in Excel 2007, that changed, and you could actually use the analysis tool pack functions by default. So it's quite simple. It's just EO month, which stands for end of. So end of month, you open your brackets, you throw your date in there, comma, and then at that point, you can either put in, it's a number. So you either go with zero, which will give you the end of the month for the current.

date that you provided, or you can go minus one, which will go back one month, or you can go positive one, which will push you forward one month. And obviously you can make that number as big as you need. But working in the accounting industry, a lot of the data that we had to deal with was always tied back to, we don't care what day of the month it is. We want to get to the month end because that's where all the reporting happened. So EO month is a great function to allow you to advance to the end of the month. And it works beautifully, whether it's February 28th or 29th, it'll actually give you that kind of information and you can shift it back and forth. So that's definitely got to be my favorite, even though it's maybe not one of the ones I use every day. It's totally my favorite. That's because you're an accountant.

Yeah, you know, don't hold it against me. I'm an accountant as well. So there you go. So what's one Excel add-in that you use constantly? And it's not Power Query. I actually wrote my own Excel add-in called XLG Favorites. This was based on the work of a fellow named Daniel Klan, who wrote his original favorites add-in. And I retooled it for a bit for Excel 97 through 2003. And I also wrote another one that was called Use Templates. And in Excel 2007, when I was rebuilding my add-in to deal with the ribbon, I actually merged the two of them together. So XLG Favorites allows me to basically...

have a most recent files list, most recent folders list, but it shows up on a tab on the ribbon. You can download it for free for my website. I love this because it lets me bookmark files. It lets me bookmark folders that I can open things in so that, you know, when I'm teaching a course or whatnot, and I'm trying to get into the example files for the folder, I don't have to browse the entire folder directory structure to get down there. I can actually bookmark my favorite folder and hit, you know, open for this folder to open right there, which is great without having to go into pin files or anything like that. So it gets a little bit, click a little bit closer. I can set up multiple databases so I can keep all those different guys, you know, set up for individual courses and flip back and forth between them. Excel feels naked to me if I don't have this ad installed. I mean, it's that much. I use it every single day.

I can bookmark Excel files, text files, and I can open them up right from Excel. It doesn't matter. It is far and away the atom that I cannot live without. It's available on my site. The only thing is if you're Googling it, you have to spell favorites correctly. Favorites has a U in it. Although I did actually add a switch in the setting so that you can actually change it to favorites if you prefer US English. So I was trying to be conscious of my neighbors down south here. Good stuff. All right, buddy. If you could recommend one Excel book.

For our listeners, what would it be? The Excel book that I loved and wore out and whatnot was called Power Programming for VBA by John Walkenbach. I love John's work. He's written more Excel books than I think anyone else on the planet at this point in time, although Bill Jelen is catching him up pretty quick, I think. I see Bill's just doing his 40th book right now. But Power Programming with VBA, it was an absolutely fantastic resource for me when I was learning VBA. That's the way I think books should be written and structured and whatnot. And I mean, as I go through and I write my books, I mean, I'm always conscious of the way that John did his work in those kind of things. And I hope to be, you know, half the author he is when I get my stuff out there. So great, great stuff. John is a legend.

in the Excel world. And I'm going to get him on this show one day if I can track him down. All right, guys. So you can find the links to this book and everything we've been chatting about today, including tutorials on Ken's favorite shortcuts and tips by visiting myxlonline.com slash podcast. All right, Ken, we've come to the last question of the show. Now, let's say it's my first time ever seeing Excel. So I'm a beginner. I could be an intermediate user. And I've got an important job interview coming right up. Now, with all the Excel knowledge that you know today and the online and offline resources available, what would be the quickest way for me to start learning Excel?

Wow, that's a tough one. All right. I'm going to plug a course that I did, if that's okay with you. I've got a course that I work with with a company called GoSkills. There is a Excel basic and advanced course at GoSkills, which was actually built specifically for that purpose. The idea was to take somebody who has little to no knowledge of Excel and get them schooled up and ready to go so that when they're in that job interview, they can feel confident enough answering the questions and being able to use Excel on a regular basis. So you can find that at GoSkills.com. It's the Excel basic and advanced course, although you can go into just basic or just advanced if you wanted to as well. But that'll give you a really broad brush approach to Excel on the macro level. So it gives you lots of different features.

gets you up and ready to go. That's by far, I think probably the fastest way to skill yourself up. If you want to take Excel to an incredibly deep level, then you start looking for, you know, when you, this generally happens when you're out there in the workforce, that's where you find your own business problems you're dealing with and find a forum to try and help bring that knowledge along. I think those two things really go hand in hand though. You know, the basic training to get yourself started and then, you know, the self-actualization part is where you actually start, you know, trying to take that further and become much deeper. But certainly, I mean, if you're looking to start off with the basics, check out that GoSkills course. It's very good. We designed it specifically with that focus in mind. Now, out of all the Excel features out there, what would be the best?

Excel feature that I should focus on to get ready for this job interview? You know, that's a great question. And honestly, John, in that one, it's a tough one to answer because it really depends on what the job is. You know, if I'm going into going into the accounting world, I mean, when I used to do job interviews and tried to hire people, one of my favorite questions, what's the final parameter if you look up and what does it do if you forget? And, you know, to me, that was I was able to immediately separate somebody who knows a lot about Excel from somebody who doesn't based on that one question.

But at the same time, I mean, you know, that's if you're looking for somebody who I'm trying to bring in to be an analyst or I'm trying to bring in to build solutions. When I was hiring a junior clerk for accounts receivable or whatnot, you know, the question was more along the lines of what's the most complicated thing you've ever done in Excel? Tell me about what you built and what did it do? Because I want to know, can you write individual formulas and whatnot? The biggest key piece, I guess, when you're if you're working into an entry level job is I'd like to see that somebody has a good command of formulas, you know, some formulas and averaging formulas and stuff like that. I mean, nothing extremely, extremely detailed and whatnot. You know, do you know how to create a table? Not just blocks of numbers, but an actual official table, because that means that you can probably.

data and your formulas aren't going to go astray on you if you're working into an analyst job well then the questions are going to become a lot harder because you know look i mean there's more that you need to know so it's a tough one answer i mean it really depends on what the job role is what you're expecting to be done and whatnot um you know great answer nonetheless and i appreciate that Ken, mate, you are an Excel guru, and that's why you are called Excel guru. And thanks to people like you, a lot of Excel users are going to learn and appreciate Excel. And I'm going to be one of them as well. I'm going to learn so much from you in terms of Power Query. And mate, I appreciate you coming on the show. And before you leave, what's the best way for our listeners to connect with you? You know what? You can get me on LinkedIn. There's an Excel guru Facebook fan page. You can get us at our forum. I'm on Twitter as well, at K-Pulse. I'm all over the place, man. Good stuff. That's the way to be. Yeah, the best way, honestly, I mean, if you want me as a connection on LinkedIn, fire it through.

I'm happy to accept those. And then, you know, I basically, I mean, I'm blogging weekly on Power Query at ExcelGuru.ca slash blog. That always goes out on my Twitter page, on my Facebook page and on my LinkedIn page. So if you hit any of those particular mediums, you'll get notified of updated blog posts and stuff coming out. And, you know, on my Facebook page, I occasionally post other stuff as well when I'm out doing courses and stuff like that too. So any of those mediums is good to connect with me. Thanks, Ken, mate. You're a legend. You're an Excel legend. You should change your name from Excel Guru to Excel legend because in my eyes, mate, you're on a different planet and I appreciate you coming on and I'm going to get you on another time and hopefully you can teach something else rather than Power Query.

maybe Power Map or Power View? Well, maybe not the last one, but maybe the other one. Yeah. Thanks, John. I appreciate the time and the flattery is great. I'm just a guy out there that likes to learn and share my knowledge. That's all it is. So thanks for having me on. I appreciate it. We hope you enjoyed this episode of the My Excel Online Podcast. Be sure to subscribe and share this with your colleagues so they can reach their full Excel potential. You can also visit myexcelonline.com slash podcast for show notes as well as John's online Excel courses and free tutorials to help you stand out from the crowd. Until next time, keep excelling.

Frequently asked questions

How long is this episode of Learn Microsoft Excel with MyExcelOnline?

This episode is 57 minutes long.

When was this Learn Microsoft Excel with MyExcelOnline episode published?

This episode was published on July 31, 2015.

Is there a transcript available for this episode?

Yes, a full transcript is available for this episode. You can read the complete transcript on the episode page.

Can I download this Learn Microsoft Excel with MyExcelOnline episode?

Yes. Use the download control on the episode player to save the publisher-provided media file.