Earth's Only Hope vs. Alien Excel Invaders, w/ Ken Puls

Ken Puls is the epitome of an Excel and Power BI guru by being an incredible guide, a teacher, and "Remover of Darkness" of the secrets of these tools. Born from a fusion of Accounting and IT, Ken's mastery of Excel includes VBA programming and Power Pivot modeling, data cleansing and reshaping, and financial modeling. We highly encourage you to check out Ken's website, Excelguru.ca, if you desire to be enlightened in the ways of Excel and Power BI. References on this episode: Download Rob's Reference Card Episode Timeline:

• 7:20 - Mr. Excel is in the house, Ken's Origin story is born from an IT and Accounting fusion, and Rob reveals some little known Excel facts
• 33:50 - Who will protect us from the Alien Invasion? And some scenarios that may start a fight!
• 1:10:45 - Microsoft 365 VS The Perpetual Version, Are Array Formulas still widely used?, and Lambda Functions
• 1:25:35 - Ken shares something that ties Rob to a cool add-in called Monkey Tools

Episode Transcript:

Rob Collie (00:00:00): Welcome friends. Today's guest is the famous Ken Puls. I've known Ken for over a decade through the Microsoft MVP program, but you might know him as the author of the Power Query book, M is for (Data) Monkey. Or as the driving force behind the website, excelguru.ca. It's Canada A. Or perhaps most recently as the creator of the Monkey Tools add-on for Power Pivot and Power Query.

Rob Collie (00:00:28): A conversation that I had with Ken about 10 years ago was really the critical turning point in me deciding to write my first book. If you want to know what those conversations were, I won't spoil them, you'll just have to listen to the episode.

Rob Collie (00:00:42): So, Ken has been a part of the landscape and certainly a part of my life and career for a very long time. If this happens to be your first time hearing of Ken, for some reason, here's the best way to describe him. Imagine the land of Power BI and the land of Excel where those two landscapes meet. There's this hazy, like demilitarized zone between the two, and Ken stalks those hills like an apex predator. Of course, whenever I describe him in terms like those, he's always really quick to remind me, "Hey, I'm actually pretty nice." And he is. He's the nicest apex predator you'll ever meet.

Rob Collie (00:01:15): As a program note, Tom couldn't make it to this recording session, so we just subbed in Bill Jelen. What do you think about that? I took advantage of the rare opportunity of having Ken and Bill in the same place to try to resolve a very important question, which is, if the fate of the Earth hung in the balance, who would we nominate as our Excel champion to represent us? I also confronted Ken with the unwinnable Kobayashi Maru scenario of you can only pick one, Power Query or DAX. Forced him into an answer. Did he get it right? Did he get it wrong? You'll decide. So, let's get into it.

Announcer (00:01:50): Ladies and gentlemen, may I have your attention, please.

Announcer (00:01:57): This is the Raw Data by P3 Adaptive podcast with your host, Rob Collie. Find out what the experts at P3 Adaptive can do for your business. Just go to p3adaptive.com. Raw Data by P3 Adaptive is data with the human element.

Rob Collie (00:02:18): Welcome to the show, Ken Puls. The long awaited Ken Puls, how are you today?

Ken Puls (00:02:23): I'm doing well, Rob. How are you?

Rob Collie (00:02:25): Fantastic. Fantastic. Well, I said this a few times with a few different guests, but it had been so obvious if we'd kick this podcast off and like just gone stampeding to Ken in the first few weeks. We had to kind of play it cool a little bit. Kind of like circle our way around like, "Oh, right. Yeah, Ken." As opposed to the obvious choice like from the very beginning. So, I appreciate you being patient with us. I'm sure you've been sitting there going, "When is the phone going to ring? When is it going to ring, damn it?"

Ken Puls (00:02:54): You flatter me, Rob, honestly. I don't know what to say to that. Thanks for having me. I'm not offended. It's all good. It's all good.

Rob Collie (00:03:03): You're an MVP, Microsoft MVP. Are you still an Excel MVP? Or if you come to the dark side and gone data platform, Power BI MVP?

Ken Puls (00:03:12): I've been from Excel to the dark side and back again. Although they don't call us Excel anymore. Now they call us Office. I know, It's shocking. So, I was an Excel MVP, I guess, for 10 straight years, and then flipped out to Data Platform after that and spent some time there. And now, I'm back in the Office apps and services, I think, is the technology platform officially that looks after Excel and all the other Office apps. I still play in both spaces, though, obviously. And I've always actually kind of found it confusing that Excel and Power BI aren't in the same category, but it is what it is.

Rob Collie (00:03:46): I know, man. Different P&Ls, different vice president P&Ls. That's why your MVP program is so fractured.

Ken Puls (00:03:53): Yeah. No, I figured that out. But yeah, it is odd. The softwares play well together. You kind of think there would be a close relationship there.

Rob Collie (00:04:02): Hey, look, the software playing well together is also a relatively new thing. So, we'll take that. If we had to choose between the MVP programs making sense and the software making sense, I trust the software. I mean, it doesn't mean that the software always makes sense. It's not round to 100%.

Ken Puls (00:04:20): Yeah, I think we've worked with software long enough, we know that there's a heck of a lot of places where it doesn't make sense.

Rob Collie (00:04:25): There was an MVP summit recently, right?

Ken Puls (00:04:27): It was.

Rob Collie (00:04:27): It was conducted virtually this year.

Ken Puls (00:04:28): It was indeed. Yeah. All over Teams. Lots of video, lots of chat.

Rob Collie (00:04:31): I wonder if that's harder or easier on the Microsoft program managers and engineers who are presenting to you. I'm familiar with the murderers' row, that is a room full of Excel MVPS.

Ken Puls (00:04:46): You think so highly of us, man. The murderers' row. Wow.

Rob Collie (00:04:51): Yeah. And you know who's usually sitting in the front murderers' row?

Ken Puls (00:04:55): I can't, actually. No, I sit in the back.

Rob Collie (00:04:59): Oh, you sit in the back.

Ken Puls (00:04:59): I sit in the back. It's easier to heckle from the back.

Rob Collie (00:05:02): Yeah. Well, you got that voice, man. Your voice, when you picked out your voice at the store, you found it on the not effing around dial. It is a powerful voice.

Ken Puls (00:05:12): It had to do a lot of things. I mean, yelling at Microsoft PMs is only one of the jobs that it's had. I mean, I was a soccer coach for a few years and trying to get 12, 11 year old girls and break through all of their communication that's going on in the field. You got to have voice, man. It's important.

Rob Collie (00:05:26): I think we determined that I was not on the Excel product team. I had left the Excel product team by the time you became an Excel MVP. I think we determined that through detailed forensic analysis, but I don't remember it that way. I remember it as if you were there the whole time, because apparently ...

Ken Puls (00:05:42): You must have been in the next room over, right?

Rob Collie (00:05:45): That's right. I do remember being there as an MVP later, and witnessing your criticisms of the product team's plans delivered in that ... I don't know. Can you say piercing and deep at the same time? It just kind of cuts through the room and it's like three times as devastating. And I'm just sitting there in the back of that room going, "Oh, yeah. Get them."

Rob Collie (00:06:09): I think I kind of put myself in their shoes too. And so, I got traumatized a little bit by remote control my proxy. And that's how it sort of like retroactively worked into my head that you are always there.

Ken Puls (00:06:20): For reference, Rob, my hope is not when I go and I'm giving feedback there that I'm traumatizing people. That's not the goal. It is actually trying to help make things better. I mean, I understand they all got really hard jobs and have to make difficult decisions. I totally get that. But at the same time, I mean, there's obviously some passion that goes into this stuff as well from my side and whatnot too.

Rob Collie (00:06:40): I don't want to paint you as a villain. You are just fundamentally one of the nicest people I know. That is absolutely true.

Ken Puls (00:06:44): Okay. Well, that's a lie.

Rob Collie (00:06:46): Oh, come on. Okay, maybe it's a little bit of a fudge, but it's not. I mean, you're actually a really, really nice guy, like that is true. You just have a very crisp directness about not suffering foolishness that I think in that room in particular, that's like your moment in a way. It's fun to watch. I wish I could sit in as a fly on the wall.

Rob Collie (00:07:06): But also, honestly Ken, I would expect that you probably find yourself less frequently in that position at those MVP summits these days. Maybe you do. I don't know. We were still turning a corner at Microsoft at the time that I'm talking about when I first got to know you from a lot of ideas for the product, starting out with the sentence, "Wouldn't it be cool if ..."

Rob Collie (00:07:29): That sentence being uttered by someone who really didn't have a whole lot of experience in the business world using the tools. So, a lot of times, what sounded cool in the fires of Mount Redmond, wasn't really all that cool out in the real world. And oftentimes, those MVP summits was the first time that those ideas would be checked by that reality. And it's a valuable service provided.

Ken Puls (00:07:51): Yeah. I mean, I think we saw a couple of things that did happen. I mean, I've been an MVP now for 15 years. I mean, we've seen an evolution in the program from the side that came from the MVPs and Microsoft together. I mean, we've grown a lot, I think, on both sides of the table.

Ken Puls (00:08:04): My first MVP Summit, in retrospect, I've grown a lot since then, and I certainly wouldn't approach things the same way that I did back then. I mean, you get newly minted as an MVP, and you go walking in the door and you're thinking, "I mean, look, I use this program every day. These guys don't know what they're doing." And you try and get that, I guess, almost chipped out there and across.

Ken Puls (00:08:20): At the time, the first summit that I was involved in was the first time that we were given a ribbon. It was 2007. So, we had a whole new user interface that we couldn't customize. And it was a painful time to be an Excel user, and certainly to be sort of biting on the developer side of things and whatnot.

Ken Puls (00:08:39): I felt that at the time, it didn't seem from where I was. And this may or may not be true that a lot of the PMs actually had real world experience in working with the product. They were designing the product, but they weren't necessarily coming from the trenches.

Ken Puls (00:08:53): When you fast forward to now, a lot of the PMs have spent time very much working in the business. I mean, that's sort of where they've been cutting their teeth before they actually get on the product team. The decisions that are made are not incubated outside of talking to people. We know that. We get asked all the time about what our ideas are, and we still hear, "Wouldn't it be cool if ..."

Ken Puls (00:09:11): But it happens quite early in the process, and we get a lot of feedback. And the team is really open with us on a lot of designs. Honestly, I would never go back to what we had 15 years ago. I mean, this world is amazing now. The rapid pace of change that's happening, the evolution in the product. I mean, there's a lot of people that are on that team that I've known for a long time now that I just ...

Ken Puls (00:09:33): I mean, obviously, they're fantastic people that really, really want to make this stuff work really well and are suffering under the same challenges that we all face. Resources versus time and all the rest of it. It's a cool place to be able to be and play around with, for sure. I think I kind of drifted a little bit of side on where that started.

Rob Collie (00:09:51): That's exactly how we roll here on the Raw Data podcast. We get off to the side. That's what we do.

Ken Puls (00:09:58): I love the way you break that on your monster truck voice there, Rob. That's just gorgeous.

Rob Collie (00:10:02): That's how we roll. Bill, do you remember the first time you met Ken?

Bill Jelen (00:10:08): Yeah. No, that was early back in the ... The summit is probably when we were in Seattle instead of Bellevue.

Ken Puls (00:10:15): It's the last time Bill got to talk in a session.

Bill Jelen (00:10:18): Yeah. Back then, there was this older fellow named Charlie who could bellow from the back of the room. And then all of a sudden Ken shows up, and now we have two of them. God forbid, if Ken and Charlie would ever not agree, it would be a very loud experience.

Rob Collie (00:10:37): Yeah. And everyone caught in the middle.

Bill Jelen (00:10:39): Yeah, right.

Ken Puls (00:10:41): I think that only happened once, didn't it?

Bill Jelen (00:10:43): I don't know. Yeah. I probably blocked it out. PTSD and all.

Rob Collie (00:10:45): Yeah. They implemented safeguards after that. Yeah, it's what control rods are for.

Ken Puls (00:10:54): Exactly. Yeah.

Rob Collie (00:10:57): You hear that, ladies and gentlemen? So, Tom LaRock couldn't make it today, but we're such a powerful organization here, this podcast, that when we go to the bench, we pull Bill Jelen off the bench.

Bill Jelen (00:11:08): Thomas LaRock Jr.

Rob Collie (00:11:09): It's like LeBron James going to the bench and Michael Jordan coming in. I mean, what are you going to do? That's the quality. That's the standard that we try to hold ourselves to here.

Rob Collie (00:11:17): You mentioned, Ken, about the people on the team having a sort of a higher, at least a higher percentage of real world experience today versus back in the day. I was just talking to Carlos Otero, the other day on the Excel team. It's awesome, right? He was working on a finance team at Microsoft. He was in the finance function. It wasn't part of the product team at all.

Rob Collie (00:11:40): This is, by the way, one of the places we would visit a lot. The Excel team would learn a disproportionate amount of our real world sort of exposure like customer visits were internal customer visits. It's really kind of a neat full circle that someone from that background. If you're building race cars, you need some race car drivers on the team.

Ken Puls (00:12:04): Oh, absolutely. Carlos was working in finance. I mean, another guy that I think is important to call out is someone like a gentleman date. I mean, he was out working in the auditing sphere. I mean, he's writing his own add-ins to build a better formula bar for Excel and whatnot.

Ken Puls (00:12:18): So, it's not just like Microsoft actually pulled people with experience from their internal teams and not a disservice to Microsoft, they're a huge company. And there's obviously a lot of practical work that goes inside that's not around software development. But being able to pull from industry as well I think is super important, because we all know that ... I mean, there's 700 different ways to do everything in Excel.

Ken Puls (00:12:40): And getting that variety of exposure and different data points, I think, is super, super important for this. And more and more as time goes on, we see people that are being brought in and have that variety of experience. And they're taking on areas that they're passionate about. I think sometimes they change in something different for the sake of getting different, being fresh ideas, which is also important.

Ken Puls (00:13:00): But the most important thing is that they're taking the real world experience, they're comparing it to what you have, and they listen. And that's the big part that I really value from this stuff is that they actually listen to us. Sometimes, I don't know how the heck they actually managed to do that, because the voices that we deliver it with are not always the friendliest and they're often very loud. And yet, they'll still take what we say and say, "Okay, let's explore that a little more. Cool." May not take your route, but at least they're paying attention and trying to get a different perspective, which is fantastic.

Rob Collie (00:13:30): Part of me wants to say something like, "Amateurs. Dave, listen. That will be their undoing."

Ken Puls (00:13:38): Well, there you say. I mean, I felt like back in the 2007, 2010 era, it was like, "Hey, guys, we have a beta for you. We can't change anything. It's already done." And that was the change. Whereas now, you get the, "Here, we've got something new for you. Oh, we don't like that." And guess what, it changes before it absolutely comes stock and whatever else. And I mean, that's just the new way that things have changed, which is awesome.

Rob Collie (00:13:59): Yeah, an aircraft carrier doesn't turn on a dime, but we were starting to turn the wheel back then.

Ken Puls (00:14:05): It's tough, though, right? I mean, it's a big ship. When you look at how complex the software is, and how many things it has to do for how many people balancing that with, "Let's ask people what they expect to happen, but we also have to innovate and come up with things that they never thought of." And that's the other side of it that where the heck did Power Pivot come from? It sure, didn't come from somebody coming in and saying, "I need this." I mean, that was something that was more, at least to me, was presented as, "Look at this really cool thing that we've done. What kind of impact do you think this will have on your life?" It's like, "Oh my god, where's this been all my life?" Power Query, same thing.

Rob Collie (00:14:36): Yeah, we weren't asking for Power Query, were we?

Ken Puls (00:14:39): My favorite stories I like to tell with Microsoft. I remember sitting down with Eric Patterson at one point and showing him a solution that I built in the real world. He asked me, he says, "Why don't use pivot tables to go and report all this stuff?" And I said, "Well, because I got to get my users to enter data in a format that's familiar to them, and that isn't conducive to a pivot table layout. So, I can't report it. I'm doing 180,000 unique formulas that are ridiculous amounts of lines long in order to get this thing to report." And I said, "If you guys could just give me some kind of a tool that would get this into an unpivoted format, that would be amazing.

Ken Puls (00:15:12): And Eric looked at whoever he was with and he goes, "Geez, somebody should build a tool for Ken like that. That would be amazing." And what I didn't know is that I think it was before the next summit, they said, "Here, we want to show you this thing called Power Query." I'm like, "Oh, you jerks. You knew this was in process and you didn't tell me. That's just cruel."

Rob Collie (00:15:29): That's payback.

Ken Puls (00:15:31): Could be.

Rob Collie (00:15:32): Those ugly things you said about the ribbon.

Ken Puls (00:15:36): But again, that was where the things returning, right? I mean, today, I think that, that conversation would have been like, "Hey, listen, under NDA, we're building this thing. You can't talk about it, but this is an idea we'd like to explore some things and get your feedback." Where back then, it was more of a concern. We're not sure what we're allowed to tell you yet. That's where things have changed so drastically inside the MVP program with the trust levels that they've given us, which is game changing. Really close to that.

Rob Collie (00:16:00): I want to circle back to something you said earlier, which is you kind of bounced back and forth. You went from Office MVP to Data Platform MVP, and back. There and back again, like Bilbo Baggins. What sort of triggered both of those moves?

Ken Puls (00:16:13): What triggered the moves? The word category I was put into. In all honesty, what triggered the move is this. When I was playing with Excel originally, I was doing a lot of work with VBA, and blogging, and forum posts and whatnot.

Ken Puls (00:16:26): When Power Pivot came out, I obviously was trying to get started with that and working through it. And certainly, I mean, no small credit to you on this, Rob. I mean, he helped me get over some of the pretty big hurdles along the way in that. And then Power Query also sort of came in. I think anybody who knows who I am knows that I pretty much dove into Power Query. It was more like a cannonball than a dive. I just went straight into it.

Rob Collie (00:16:48): I didn't notice. I didn't notice any of that.

Ken Puls (00:16:50): Yeah. No. Nobody noticed that. For me, I was playing around in the Power Excel stack very, very early. When Power BI came out, we tried it in v1, but that didn't work so well. But v2, when we finally got into a Power BI that sort of grown up to be what it was today, the original tool was basically just, "Hey, give me a visual layer on top of Power Query and Power Pivot."

Ken Puls (00:17:13): I mean, I started playing around with blogging with these things go, and this is really cool. I mean, we've got these technologies and Power Query and Power Pivot. You can learn one, it works for another tool. So, you're learning for two products at the same time. This is really, really cool. So, I sort of started blogging about that, and everything that I was doing fit into both camps.

Ken Puls (00:17:30): I'm not entirely sure why I was moved from Excel to Power BI. I think that to begin with, they were wrapping up and bringing in new MVPs into the Power BI product, and I was one of those who said, "Look, I mean, this guy is on board, we're going to crash to here."

Ken Puls (00:17:46): But even in the time that I was a Power BI MVP, per se, I always kept one and a half feet in the Excel camp. I mean, Excel is where I grew up. It's the product that I absolutely love. I use Power BI not infrequently, but I use Excel. It's a very strange day if I don't have Excel open. I mean whether it's on my phone on the weekend or whatever else. I only open Excel on days that I didn't [lie 00:18:12].

Ken Puls (00:18:14): That's why I ended up moving back into the Excel category after a while, because, honestly, I'm still more excited about the things that are happening inside the Excel world than in the Power BI world. Even though the Power BI world is cool and exciting on its own.

Rob Collie (00:18:26): I'm going to let you know a little behind the scenes secret in the dark, smoky corridors of Microsoft. There is a derivatives trading market on MVPs. And the rights to Ken Puls were sold by the Office team to the Power BI team for $1.5 million. And then the Power BI team said, "Oh, we're going to give them back."

Ken Puls (00:18:47): I can accept that and everything else, but isn't the player supposed to get a portion of the transfer fee?

Bill Jelen (00:18:52): Yeah, I would say you should get a cut of that, for sure.

Ken Puls (00:18:54): Absolutely. If that's the case, listen, trade me every freaking month. That's all good. Just send me my cut.

Rob Collie (00:19:02): Yeah, I don't think you understand the rules. That might work over in Europe. But here in the States ...

Ken Puls (00:19:13): This is why I felt real football. The one with the round ball, you play with your feet, because that's the kind of rules that they follow there.

Rob Collie (00:19:20): Oh, yeah. Well, even they, they've been infected by some very American style thinking lately.

Ken Puls (00:19:25): Oh, haven't they though?

Rob Collie (00:19:25): The Super League. I've never seen a sports league exist with that sort of a half life. It's just like, "Oh, the 12 biggest teams are leaving. Oh, wait, nevermind. No, we're not. We apologize."

Ken Puls (00:19:41): I've been following that over the last couple days. It actually fascinates me though. I'm very curious to see what the fallout is longer term on this because it's not like this was a secret and it's not like the different soccer leagues and FIFA came out and said or hadn't said already. "We don't want this to happen." So, they go ahead and they do this anyway. And it's the fans that lost their mind, and suddenly that's what made all the clubs pull out. And I'm looking at it going, "Wow, this is interesting."

Ken Puls (00:20:06): And I saw an interesting article yesterday from the perspective of someone in England who said, "This is absolutely the important thing needs to happen, because we need to take our game back." The history and the legacy of this game was actually built by the fans. The special part of it is that your pub team can end up rising all the way up to the Premier League, and it is now time for us to take control of our game back on other levels. I'm like, "Oh, wow. I wonder what they've touched off here."

Rob Collie (00:20:32): Either way, in sort of one group of well-moneyed elites fighting with another group of well-moneyed elites, right? Either way, right now, as far as I understand it, the dream of the little pub team working its way up is a myth, because you've got all these billionaire owners just absolutely sinking gigantic sums of money into players. It's like getting the money out of the equation is difficult.

Ken Puls (00:20:55): No, absolutely. I mean, I wouldn't expect that you're going to see a pub team that's going to work themselves off to the Premier League in five years or anything like that. There's players on the national team that were playing in a beer league not that many years ago, for England anyway.

Ken Puls (00:21:10): So, it's not unheard of that people will rise in there. I mean, honestly, one of the things that I love about football is the ... Clarifying. I call it football, soccer for North Americans. But one of the things that I love about it is the promotion and relegation system.

Ken Puls (00:21:24): And if you actually look around in English towns, you'll see that there's towns that have these big stadiums that really don't have a huge Premier League team anymore, because they've been able to actually rise and get the funds that they need in order to be able to build the infrastructure and the legacy that gets left behind when the team may go down a league or two or whatnot.

Ken Puls (00:21:41): It's really interesting. I wish we had that in North America, although you'll never convince a North American sports team owner to give up their right to stay in the league they're in, I don't think. Too much money at stake, right?

Rob Collie (00:21:51): Yeah. I paid a billion dollars for my franchise.

Ken Puls (00:21:55): Yeah, I don't want to risk it.

Rob Collie (00:21:56): I plan to keep it. Yeah.

Ken Puls (00:21:57): I get that too, but how cool is it if you see your home team suddenly get promoted up an entire tier? That's just awesome.

Rob Collie (00:22:05): And just as exciting as the team that's going the other way, making the spot for you is the titan that has fallen on a rough patch. Everyone likes that too, don't they?

Ken Puls (00:22:16): I mean, it depends on whether or not you're supporter of that team, I guess.

Rob Collie (00:22:20): I suppose. Yeah. The other sort of standard element that we haven't really covered yet, we got to get to this, is the origin story. Where does Ken Puls come from? You probably didn't emerge from the womb, writing Excel add-ins. What's your professional career arc? Where does it all begin?

Ken Puls (00:22:40): Classically, I'm trained as an account. Although, I have friends now that joke that I don't do accounting anymore, so maybe I shouldn't be, but I very much a Chartered Professional Accountant in Canada. I've been recognized as a fellow by my accounting organization. So, theoretically, I know something about accounting.

Ken Puls (00:22:55): The interesting part with my Excel and Power BI journey is that the whole sort of reason that I learned to use these tools to the degree I did is because there were tools that I needed to do my job. And if you asked me what I do today, I'd tell you that I'm an Excel Pro. And that's not something that a lot of people would tell you. They'd tell you, "I'm an accountant." It's a very, very different way of looking at things.

Ken Puls (00:23:14): I've always had a passion for technology ever since I was growing up. I mean, my first programming that I ever did was on the Commodore 64. I got a book and wrote every line of code to make a Star Wars game. I don't know that I learned a darn thing in the process of copying pages and pages of code. But then we did some coding and basic in high school and Pascal and things like that.

Ken Puls (00:23:37): But where things kind of became, I guess, important for me is when I started doing my accounting training, and we had to do certain work and whatever else. I mean, every time I had the opportunity, I would just reach to a spreadsheet because it was just logical for me to be able to fill it out. I was doing my school assignments on spreadsheets. I worked in public practice for while. I went into industry.

Ken Puls (00:23:58): I ended up eventually taking the job as the accounting supervisor and system administrator at the Fairwinds Community Resort on Vancouver Island. So, we were a golf course. We had a three food and beverage facilities, beer and wine store, hotel, property development, marina, we had all kinds of things going on. The job I took was to look after the IT and work on the accounting department, looking over the accounting team.

Rob Collie (00:24:18): Slowdown. See that right there, what they did? Not Ken. You didn't sneak anything in, but they sneaked it in. Accounting and IT.

Ken Puls (00:24:26): There is no sneaking there.

Rob Collie (00:24:28): All in one. Quick sentence.

Ken Puls (00:24:32): But it wasn't unusual, right? The previous job I had, I actually worked at, believe it or not, an orchid greenhouse as the accountant and IT support role there too. I mean, this was really common, particularly in Canada anyway. I don't know about the US, but it was really common in there that accounting and IT got put into the same kind of bucket.

Ken Puls (00:24:49): I mean, I joke with this when somebody says like, "Well, how the heck did you get into your IT experience?" I said, "Well, I was the person that didn't step back fast enough." I wasn't. I was the guy who stepped forward because I love computers.

Rob Collie (00:25:00): I'm Spartacus. Yeah.

Ken Puls (00:25:04): For me, when somebody comes back and goes, "Do you know anything about Windows NT 4?" "No, but I'll learn. What the hell. Why not?" When I went to Fairwinds, I'm looking after a very fragmented network and I'm looking after the accounting department, and we ended up doing a lot of work with Excel to do our work to tie different things together, because in those days, nothing was integrated.

Ken Puls (00:25:26): I mean, we had a network at the hotel, we had a network at the golf course, a network at administration. None of it was tied together, it was all tied together with people in spreadsheets, that's what it was. Every system was tied together that way. And then we got this offer from our head office where they said, "Look, we know that you're doing a lot of work on this. We're going to get somebody to build you a macro, so that all you have to do is click a button."

Ken Puls (00:25:44): And it actually came up because I told them that when they upgraded us to Office 97, our keyboard shortcut is broken. They said, "Okay, send us the file." Or our macro broke. They said, "Send us the file." So, I emailed them the file, and they phoned me up, they said, "How did you trigger this macro?" I said, "Well, you press alt control shift, down arrow, control C, X number, whatever else." And they're like, "Dude, that's not a macro." I'm like, "What are you talking about?" Like, "You've memorized the Lotus transition keyboard shortcuts for 75 commands to do your macro." I'm like, "Yeah." They're like, "We'll build you a macro." I'm like, "Okay."

Ken Puls (00:26:18): So, they built me a macro in VBA, which was awesome, because then you could click a button, and it just did everything. I was like, "Sweet." And then the person that wrote it left the organization, so who got to maintain it? Me. So, I'm like, "Okay, cool." So, I started playing around with this stuff, and tinkering a little bit with VBA and whatnot. And I was like, "This is interesting. I'm enjoying this." So, I started playing around and recording some macros, found a forum, got an answer that you can actually start writing your own code. You don't have to record everything.

Ken Puls (00:26:45): And then one day, our head office came to us and said, "Look, we decided you need a staff cut. You got to cut 33% of your staff out by Monday morning." This was Friday. "And we're going to give you more work to do, tighten your reporting deadlines, and you get no budget to hire anybody. So, figure it out."

Rob Collie (00:26:59): A few things in there that I want to dig into. First of all, circling back to the IT and accounting thing. You said it happens a lot in Canada. It happens a lot everywhere in my experience. Maybe not at the enterprise level, but it certainly happens in the SMB space.

Rob Collie (00:27:15): I think the reasons why it happens, why these two get bundled so frequently. The reasons why it happens are not good reasons. The reality there is that very often, it's just like two things that the rest of the business is just like, "Oh my god, I don't want to think about that. It's arcane. We know that it's a cost of doing business, and we want nothing to do with it. We don't really value it, but we can't go without it. Just make it all go away in this one corner." There's a lot of that attitude.

Ken Puls (00:27:47): It hurts me that you lump in the accounting with the, "We don't really value it. We don't understand it, but we got to have it."

Rob Collie (00:27:54): Yeah. Well, am I speaking the truth?

Ken Puls (00:27:59): I'm not going to accept or deny that.

Rob Collie (00:27:59): I see. Okay. I'm not saying that's the right thing. I'm just saying that, that's a very common sort of brutish ... I mean brute-ish, not brood. Brutish attitude towards these things. It's not a smart way to view the world, but it's still a very common way to view the world, unfortunately.

Rob Collie (00:28:16): At the same time, though, lumping these two things together ends up working out in a lot of cases because first of all, those are the waters in which can pose coalesces, right? That's the primordial goo where someone like can actually spontaneously self-organizes, or better off for it. And I've known so many people now over the years. Nothing remotely. Like exactly the same story, but very similar stories. And some amazing things have happened as a result. These end up being sort of an accidental, great pairing. Second thing I wanted to say, though, or actually ask for Ken Puls. Which came first, VBA macros or VLOOKUP?

Ken Puls (00:28:57): VLOOKUP.

Rob Collie (00:29:01): Okay.

Ken Puls (00:29:01): Absolutely. Actually, honestly, HLOOKUP, which is just weird. Who the hell starts with HLOOKUP over VLOOKUP? That's bizarre.

Rob Collie (00:29:08): I don't know. It must be something Canadian.

Ken Puls (00:29:10): Yeah. Maybe it is. Bill, you seem like you wanted to say something to that.

Bill Jelen (00:29:16): Yeah, I know. That's the first. I haven't heard someone who started with HLOOKUP.

Ken Puls (00:29:20): All I can say is our data was screwed up, but ain't that the way? So, I started with lookup functions. I'll say before I started programming, I knew a working knowledge with Excel. But honestly, I didn't know enough to be programming. There's no way that somebody should have let me into the Visual Basic Editor at that point in time, certainly not start writing code.

Ken Puls (00:29:38): And yet, when head office came back and said, "Look, we're going to cut all your staff and we need you to keep on doing things." Basically, what happened is I went into my boss. I said, "Look, Jim." I said, "I think I have a route that we might be able to leverage to get through this. I've been experimenting with these macros, and I think that this might be the way that we can actually do it. If you'll just give me time to sit down and really focus on learning this language, I think that I'll be able to automate this stuff so that we can automate a lot of the work we were doing, and hopefully don't feel a huge amount of the brunt of this over the longer term."

Ken Puls (00:30:05): And my boss, Jim, was mentor of mine, fantastic guy. Actually, he works for me today, which is really cool. He said, "Look, Ken, I trust you. I'm not sure what other way we're going to go with this. Let's give it a go. Let's see what happens." And he gave me license to go and actually explore that.

Ken Puls (00:30:20): I would say for the next year, I basically lived inside the Visual Basic Editor. I was learning to code, I was recording, I was understanding, I was taking a lot of things out of forums, asking a lot of questions in forums to try and get my practices better, and understanding what I was doing with this stuff. And within a year, we had actually automated every single position that we had, had to let go. And that was a huge, huge thing.

Ken Puls (00:30:44): I mean, for me, that's really what kicked off my passion for Excel was the fact that I no longer worked with my computer, my computer worked for me. And that was a very, very different experience that we had. And if it weren't for that ... Honestly, that move by head office cutting our staff changed the entire trajectory of my life, 100%.

Rob Collie (00:31:05): Isn't that weird?

Ken Puls (00:31:06): Yeah, I know, which means that obviously, head office coming in and telling us to cut our stuff was the right thing to do, even though it sure didn't feel like it at the time.

Rob Collie (00:31:17): For me, I don't know if I've ever told you to review this. Macros came before VLOOKUP. In fact, for a while, for a number of years before I ever worked on the Excel team, I kind of viewed the Excel grid is just like a place to store values and manipulate them with macros.

Rob Collie (00:31:35): My first fantasy football spreadsheet that sort of changed my life was 100% VBA. There weren't any formulas anywhere in the damn thing. I was even doing the basic arithmetic of like what the score should be. I believe, in VBA, I wasn't even using, which is just unconscionable to me today. Like looking back, I'll be like, "What was I doing?" I do the vast majority of that with formulas today instead.

Rob Collie (00:31:57): Here's the really funny story that, again, I'm pretty sure I haven't told you this, is that actually my second job at Microsoft was as a test engineer on the Windows Installer. Darwin is the codename. MSI, I was a test engineer on that. And the test lead on that project had decided, before I was ever hired, that all of the automated testing, all of the automated tests that we wrote on that team were going to be written in VBA against the Darwin Windows Installer object model. And we were just going to use the Excel environment as the place where we wrote all of our test code.

Rob Collie (00:32:34): We would just load Excel, flip over into the Visual Basic Editor, add the references to something had nothing to do with Excel whatsoever. And our spreadsheets, which had nothing in them other than macros, were the test suite. And can you imagine how confusing this was for me fresh out of college? I had no idea that Excel had this in it. It was just so discombobulating. "Why are we using Excel? Is there some important relationship between Excel and this Windows Installer?" It was just some crazy decision.

Ken Puls (00:33:05): Correct me if I'm wrong, but didn't Microsoft own Visual Studio or something?

Rob Collie (00:33:09): Yeah, I think the test lead was just really comfortable with VBA in Excel.

Ken Puls (00:33:14): But isn't that the funny part? When you go and you look at solutions today that we run into around the world, and it's like, "Why are you using Excel for this?" Because it's the tool I know best, and it's the Swiss Army knife that will do anything. I see stuff like that all the time. I mean, lord knows, I built a few solutions that probably shouldn't have been in Excel, and yet, why not? It's easy.

Rob Collie (00:33:33): Well, this brings me to one of the sort of the handful of things that I actually plan to do here, which is I have a couple of fights that I want to pick and see what happens. Here's the first one, Bill, and you're involved, so pay attention.

Ken Puls (00:33:44): Got it.

Rob Collie (00:33:45): So, for a number of years, I've told people that if the aliens came down from space and said, "Earthlings, nominate your champion, your Excel champion to come forth and do battle with our Excel champion with the fate of humanity in the balance." And it's going to be like a decathlon style Excel event. It comes down to the two of you.

Ken Puls (00:34:07): No. No, no, no, no.

Rob Collie (00:34:08): And hold on. And I choose Ken. Ken is our champion. As far as I'm concerned, he's the one I want getting in that flying saucer to save the human race in an Excel decathlon against the alien invaders.

Ken Puls (00:34:23): God, we are so screwed.

Rob Collie (00:34:24): That's what I want you to say. I want my champion to say that. I want my champion to say, "I'm not worthy." Because the one that says, "Oh, yeah, I'm worthy." That one is going to get asked kicked. We know how it goes. Bill is in a completely different category. We need like a logarithmic scale here, like the Richter scale. Maybe I'm an eight and Bill is a 9.8 and Ken is a 10.

Rob Collie (00:34:46): Bill, if it was going to be like a novelty competition, like make Excel, do something completely unexpected that you just did not ever think Excel could do. If I learned those are the rules, then we're putting Bill up there. But Ken, it just blows me away. You're just a heavy duty add-on author, like embrace the managed code, the C#, you're the full stack. Bill, I was hoping to get some friction here, but you're like already saying, "No, don't pick me. Don't pick me."

Bill Jelen (00:35:12): I wouldn't pick either. I wouldn't pick anyone. I wouldn't pick any of us.

Rob Collie (00:35:14): Who would you pick?

Bill Jelen (00:35:15): Some random person that I don't know who actually uses Excel 80 to 120 hours a week. I'll just start walking up and down Wall Street.

Rob Collie (00:35:22): This is a good contingency to be prepared for. You don't want to be scrambling, because I'm not going to give you a week to find your champion. They're going to need to know right now. So, we need to know this name. In the meantime, I'm pencil and cannon.

Bill Jelen (00:35:35): All right.

Ken Puls (00:35:36): Well, that's terrifying. I'm flattered, Rob. But honestly, I mean, I know that there's so much stuff that I don't know. The good thing for me is that I know who to call when I run into those kinds of things, so I would not claim to be the world's champion on that stuff, though. That's way too much freaking pressure.

Rob Collie (00:35:50): But it's a decathlon, Ken.

Ken Puls (00:35:52): Oh, okay.

Rob Collie (00:35:53): If you're 90th percentile at everything, that means your 99th percentile overall.

Ken Puls (00:35:58): I don't consider myself 90th percentile in all these things either.

Rob Collie (00:36:01): Well, okay. So, humility.

Ken Puls (00:36:04): Too much to know.

Rob Collie (00:36:05): All right, we're going to edit that out. We're going to edit that out, and we're going to have Ken say, "Yes, that is correct. I am the champion. [crosstalk 00:36:12]."

Ken Puls (00:36:11): I don't think we're going to have that footage.

Rob Collie (00:36:15): Ladies and gentlemen, don't listen to Ken. He's our guy. All right. I'm not very good at picking fights apparently. So here, let me up the ante a little bit and say, all right, here we go. Ken, you can only have one style of pivot table forever. Compact access or tabular?

Ken Puls (00:36:32): Tabular.

Bill Jelen (00:36:32): Tabular. Yeah.

Rob Collie (00:36:33): Jesus.

Ken Puls (00:36:34): Look, Rob, the decision that you made to put in compact was just the wrong one, man.

Bill Jelen (00:36:39): It was. And Howie agrees with us on that.

Rob Collie (00:36:41): Howie agrees?

Bill Jelen (00:36:42): Yes.

Ken Puls (00:36:42): Yep.

Rob Collie (00:36:44): I don't know. There seems to be a little bit of a common thread between Bill, Ken and Howie, right? You're all just too smart. You need the mouth breather impression of tables. That's the standard. The person that you put it in front of runs in fear, doesn't understand what it means or like goes, "Oh, this is so pretty, so beautiful." We didn't do it for you. You could say we did it for us, but ...

Ken Puls (00:37:11): It gave me pretty useless. Yeah, I get it.

Bill Jelen (00:37:13): You forced it upon Ken. You didn't give him any way to change the defaults.

Ken Puls (00:37:19): That's not true. I could write some code.

Bill Jelen (00:37:20): You will use this and you will love it. Or click eight clicks every time you create a pivot table.

Ken Puls (00:37:25): On the other hand, there was an insurgence that Bill led that actually got us the ability to change those defaults, so we appreciate that.

Bill Jelen (00:37:30): Six years. Six [crosstalk 00:37:32] undo year.

Rob Collie (00:37:35): Does the ability to change the default exist in Mac Excel?

Ken Puls (00:37:38): Good question. Don't know. Don't use Mac Excel.

Rob Collie (00:37:41): Self-respecting. Excel MVPs typically don't wield the Mac. I understand. I was on a call yesterday on a meeting yesterday with someone who's also been a guest on the show, Brad Miller. He's one of our first guests. And he was working with a pivot table on screen. I was laughing about the filter drop-down on the row axis of the pivot table. And I'm like, "Yeah, and you see, you got this drop-down." And then like, "Oh, no, you don't have the drop-down to choose to field. What's going on there?" I'm like, "Oh, you're in tabular mode."

Rob Collie (00:38:08): And he goes, "Yeah, I'm always in tabular mode, my pivot tables." And he goes, "You can't even set it as a different default." And I'm thinking to myself, "I'm pretty sure that they added that setting recently. But oh, you're on a Mac." So, I just held my tongue. So, on this particular topic, it sounds like the aliens would have us. Cannon Bill were like, "I don't know if you can even change it on a Mac."

Ken Puls (00:38:30): Yeah. If the Mac is part of the decathlon, we're done, dude.

Rob Collie (00:38:33): The question is like, "Are these like Silicon Valley aliens?" Because then it's a Mac. Then we're screwed, aren't we?

Ken Puls (00:38:41): It depends on whether or not it's running Parallels and running real Excel on the background.

Rob Collie (00:38:45): There's always that hope, isn't there?

Ken Puls (00:38:46): There is.

Rob Collie (00:38:46): Shall we continue the pick a fight conversation?

Ken Puls (00:38:50): Yeah, go for it.

Bill Jelen (00:38:51): Yes.

Rob Collie (00:38:52): All right. You can only have M or DAX.

Ken Puls (00:38:55): Wow. That's a tricky, isn't it? Why do I have to pick? Because they're both different, and they both do different things.

Rob Collie (00:39:02): I know.

Ken Puls (00:39:04): I love me some M. I'll reshape my data if I have to, to get into the part where all I got to do is use a sum or an average. If I got to pick, it's going to have to be M.

Rob Collie (00:39:12): Wrong.

Ken Puls (00:39:12): Well, I knew you were going to say that, but that's okay.

Rob Collie (00:39:19): Yeah. No, I respect that you wrestled with it.

Ken Puls (00:39:23): They do different things. I mean, there's calculations that I could never do in M. Or at least if I did, would be extremely difficult, and DAX would eat them for breakfast, right? I mean, if I got to go and hit refresh and wait three minutes to get my answer out of everything, instead of just clicking a slicer and seeing it, no, that would not be acceptable. So, no, they have different pieces. I believe that conversation to be fair, Rob, is about as useful as INDEX, MATCH versus VLOOKUP.

Rob Collie (00:39:47): At least INDEX, MATCh and VLOOKUP kind of do the same thing. I completely agree with you that this is a completely contrived scenario. These things absolutely do different things, but I just wanted to know if you had to go without one or the other. It's still an interesting conversation. We can talk, yes?

Ken Puls (00:40:03): Oh, yeah, absolutely. I have a philosophy when I'm working with my data that basically, if you reshape your data with Power Query that the DAX becomes a heck of a lot easier, and that's why I would actually pick M over DAX in that scenario is because I can spend a lot of time reshaping things to get it to where I just got to sum it. I don't even have a DAX to do a sum.

Rob Collie (00:40:19): You know that my stance on this is that with DAX, you get more leverage, more organizational leverage than you do with M. Now, the two of them together ...

Ken Puls (00:40:29): It's the holy grail.

Rob Collie (00:40:30): That's what you should do.

Ken Puls (00:40:32): Yes, absolutely.

Rob Collie (00:40:32): You also know, we tend to be very careful in terms of the order in which we introduce things to people, because we introduce M and Power Query to people as their first sort of piece of the power stack. We have a really hard time reeling them back in to get their attention for data modeling and DAX. It's like, "Oh my god, I'm going to go wallpaper my house with M."

Ken Puls (00:40:52): Well, it's kind of interesting on this one, right? If you're asking me what language should you teach people to actually be able to write from scratch, I would say DAX, not M. The reason why I say M first is because M is the underlying thing that's going on when you're clicking buttons in Power Query. I'd prefer that people honestly if ... I think Power Query has done a phenomenal job of this is that the better they build the user interface, no one should ever have to learn ever.

Rob Collie (00:41:17): I agree. I was talking about this briefly with Sid and Miguel when they were on that I don't think their user interface is done. There are a lot more buttons that were owed. We have a sort of background conversation going with them about, "Here's some buttons that we think you should add." Because writing the M for this is hard. Remove duplicates, control which dupes you keep, every time I need to do that, I'm like, "I'm back on your blog, Ken."

Ken Puls (00:41:42): Wait, does my blog have an answer for that?

Rob Collie (00:41:45): Yeah.

Ken Puls (00:41:45): Oh, okay. Dude, I forgotten some of the stuff I've written.

Rob Collie (00:41:48): Of course. People used to tell me all the time about something I had written. I'm like, "Oh, that sounds kind of like me, but maybe yeah."

Ken Puls (00:41:55): I had one lady asked me in a conference one day how I would do something. I said, "Oh, geez. I haven't really thought about that. I think I would do this." And then I got back to my room that night and I had a comment on the blog post that I wrote almost exactly a year before, which was almost verbatim what she asked me. And I'm like, "That was only a year ago there I wrote that?" And I had totally forgotten about it. I mean you're doing something right when you search for something and you find an article in Google and you look and go, "Wow, that's really ... Wait, I wrote that?" Yeah.

Rob Collie (00:42:21): It happens.

Bill Jelen (00:42:24): That happens all the time. Like we knew it two years ago when we wrote it.

Ken Puls (00:42:27): I mean, I write it and put it on my blog so that I can Google it later and find out what the heck I wrote, but it's always amusing when you find something you look at and go, "I forgot I wrote that. And how the heck did I ever come up with that solution?"

Rob Collie (00:42:40): Yeah. Sometimes I think there's almost like nothing smarter than a previous version of me, in a way. I got to look back and go, "Oh, of course, at the same time, there's also nothing dumber than a previous version of me." You look back and go, "Oh, I can't believe I did that."

Rob Collie (00:42:52): When you stumble on a technical solution, or novel solution, or a creative solution to a problem, a difficult problem that you were working on, when you were really in the zone. At one point, you're like really locked in. And you kind of just randomly stumble across it later when you're not deep in that focus. It is really impressive what the brain can do when it really locks in relative to on a day to day basis.

Ken Puls (00:43:15): I agree with you 100% on that. And then the other side is when you get into the part where ... I mean, I work and I battle against a problem for six months on and off and on and whatever else. And then finally, one day, you trip on something and you get it done. And then you look at after and go, "Why the heck was I so stupid? Why did this take six months to figure this out?" It was right there the whole time, and yet, you can't see it if you're looking. Everything is easy when you know how, but until you do, you're kind of stuck.

Rob Collie (00:43:41): That's true. I had to name a completely new product today. I've now had to go through the naming game so many times and fail at it. And this time, I finally felt like I could do it. Confidently, I knew which things to avoid, which things to ... What I'm trying to do with naming a product. What are you doing there? What are you even trying to achieve? What can you hope to achieve?

Rob Collie (00:44:05): And like end to end, I'd come up with a name that I liked. Quickly focus grouped it, registered the URL. This all happened in the space of about, I don't know, 90 minutes today. I turned around and said to my wife, I'm like, "Oh my gosh, this would have been six months before." And I still wouldn't have liked the outcome of that six months, nearly as much as I like the outcome of this 90 minutes. There's something to growth, isn't there? Like repetition and learning over time.

Ken Puls (00:44:32): Write a blog post, man, so you remember for next time.

Rob Collie (00:44:35): No, I'm going to keep it up in my head.

Ken Puls (00:44:36): Because that's a reliable source.

Rob Collie (00:44:38): Oh, so reliable. I just tweeted earlier today that Alt+Tab has become sort of an extension of my compromised short-term memory. I find myself in the browser going, "Wait a second, why am I in the browser? What was I doing?" And I go, "Oh, I know. I'll just hit Alt+Tab. It's just magic." I don't even know what app is going to come up when I do Alt+Tab. It's like, "Oh, look, there's where I was. That's what I was doing. That's why I was in the browser. Okay." It's like popping the undo on your brain.

Ken Puls (00:45:13): I might have to steal that because I get lost in my browser many times.

Rob Collie (00:45:16): That's like, "Why was I here?" If only that worked when you're upstairs to your house, and you go down to the first floor, you go down the stairs, you're like, "Why am I down here?" I wish I could Alt+Tab at that moment and then I would know.

Rob Collie (00:45:32): I even tried to Alt+Tab between paragraphs, like when I'm writing an email. Whatever that last thought was, that's what Alt+Tab represents to me mentally. And it's really disappointing when Alt+Tab doesn't just magically take me to the last thing if it happened to be in the same window that I was ... Anyway, that's me and my complicated relationship with Alt+Tab.

Rob Collie (00:45:51): Something that ties the three of us together is the book, the original DAX formulas for Power Pivot that evolved into Power Pivot and Power BI. Bill is the publisher, the author, and Ken was the inspiration. You originally told me I should write a book, because you had said that you weren't quite getting what DAX was about yet.

Ken Puls (00:46:12): I was struggling with it. It wasn't that I didn't realize that there was potential in there, I was struggling with the syntax and being able to translate from the goal to action. I could not wrap my head around the way that the language is put together, which seems odd to me today.

Rob Collie (00:46:29): In hindsight, yeah. And it seemed odd to me at the time when you told me that, because you were not the first person to tell me I should write a book. You were just the first person I listened to.

Ken Puls (00:46:40): I appreciate that.

Rob Collie (00:46:42): Others had said, "You should write a book." And I'm like, "Nah, whatever." When you and I had that conversation, I remember it vividly. Not like word for word, but I remember the sentiment of it where you were saying to me like, "Rob this Power Pivot thing, DAX, whatever, is going to be a really big deal for us. It's going to really open some amazing doors for us, but I just can't crack through this invisible wall on it." And that's what I went, "Oh."

Rob Collie (00:47:09): seriously, hearing you specifically say that really opened my eyes, because I already knew you to be someone that this didn't happen to. This is not how I knew you. I knew you as the guy that had a degree of almost mastery. I know that you aren't going to like that word, but almost mastery of so many different things. And for you to be struggling with that, I'm like ... I actually understood it. I was like, "Oh, so there is something I can do for the world."

Ken Puls (00:47:36): I appreciate you have such a huge amount of respect for the skills that I've built up and everything else. I mean, what you don't see is you don't see the journey that I go through in order to learn those skills. One of the reasons that I know everything that I know today is because I was too cheap to pay for someone else's software, so I decided I was going to figure out how to build it on my own, and I was going to go through the struggle in order to make that happen.

Ken Puls (00:47:54): I mean, I have not had training in any computer programming that I've ever done outside of my stuff in college. Well, I took one Excel class in my entire life and I knew more than the instructor. By that point, I was bored out of my mind.

Ken Puls (00:48:07): What you don't see is that the way that I learn is by taking zero documentation and throwing myself at the problem to figure out where I'm going to go. And I remember, if this sort of explains to the level of what I do, because I know we've had some conversations, and if you don't get to it, I will on one particular part around calculate. I already know you're going to go there, but I remember when I was first starting to get started with VB.NET, and I showed up at the Extreme VB Forum.

Ken Puls (00:48:32): And one of my buddies, Mike, he says to me afterwards, he says, "This was amazing." He says, "You walk in." We're like, "Who the heck is this guy? We've never seen him in the forum." And he says, "I'm a complete beginner, and yet, I want to do this." And he said, "And the thing that you asked for was a 12 on a scale of one to 10 in difficulty." And he goes, "And that's what you want to start with as a beginner?" And I'm like, "Yeah, dude, I had goals. I wanted to get somewhere." He's like, "Yeah, but you need to go through the steps one to seven before you try to jump at that last part." I'm like, "Nobody got time for that shit. I got to get stuff done."

Ken Puls (00:49:05): And that was I think part of the struggle that I had with DAX is that the stuff that I needed to do was complicated stuff, at least to a beginner. And yet, I kind of needed to learn stuff about what an iterator function does. Well, what an iterator function is, to start with. What is filter context? What are these things mean? I had no background for that. I mean, I'm sitting there trying to write calculates and some X statements and whatever else, not even really understanding that at the time, you don't actually sum A5 to D10 anymore, you sum everything and then you take away the stuff you don't want. It was a total mind shift for what I was doing, and it wasn't obvious. And this is still true today. There was no good tools for stepping through the thing to actually see what was going wrong.

Rob Collie (00:49:50): That's true. No, evaluate formula.

Ken Puls (00:49:54): Exactly. I've never been good with array formulas because while I get the concept I find it very ... I'm not a good chess player. I find it very difficult to visualize things without actually seeing it in front of me. So, being able to break things down step by step is what I needed to do, and that just wasn't there in DAX. It's getting closer now today with dynamic arrays, at least being able to spill to the grid. But boy, oh, boy, it was a tough thing. And you think I'm super smart. I'm no smarter than the average bear, but I don't think I'm dumb. But man, oh man, this was just kicking me, and it was driving me nuts.

Rob Collie (00:50:26): Well, I've always given you like the subconscious credit for being an array formula master. That shows you how extensive your reputation. It carries some momentum.

Ken Puls (00:50:37): Like I said, man, that's why you're placing your hopes of humanity on me as a tenant. Yeah, I know.

Rob Collie (00:50:41): Yeah, I think you're fired.

Ken Puls (00:50:43): That's what I'm telling you.

Rob Collie (00:50:43): Yeah, I think you're fired. Array formulas are definitely going to come up with the aliens. You mentioned stepping through array formulas is always a really dissatisfying experience, isn't it? You start the first step. The second step is like, "Okay, maybe this is going to work." And then you step one more time, and it's like, "Ta-dah, here's your answer."

Ken Puls (00:51:05): No. More like, "And it didn't work." It's like, "Okay. Well, why didn't it work?" Yeah.

Rob Collie (00:51:09): Stepping through an array formula kind of reminds me of the South Park meme. Step one, steal underpants. Step two, dot-dot-dot. Step three, profit. That's kind of how debugging an array formula works. Good luck. Part of what you said is also how I learned. Our recent guest, Ryan Bergstrom is a read the manual type. And I was like, "What? What?"

Ken Puls (00:51:33): What's a manual?

Rob Collie (00:51:35): I don't understand. I learned DAX by exactly the way that you did. It turns out, there really wasn't a manual at the time. I had an excuse for not reading it, but if there had been one, I probably would have let it sit on my desk like ballast. But I had two advantages over you in my life at the time when I was doing that.

Rob Collie (00:51:52): Number one is that I didn't have necessarily a business case for it yet. I was just screwing around with football stats. I could sort of naturally progress my curve of complexity rather than try to do the most incredibly advanced thing first, which often happens in business. Your first need doesn't care about the tech learning curve. It doesn't.

Ken Puls (00:52:13): If it was easy, they wouldn't ask you.

Rob Collie (00:52:15): So, I had that nice learning curve experience. I could build up over time. Plus, that's the only thing I was doing. That's the only thing I was doing was screwing around with Power Pivot and blogging about it. I can absolutely sympathize with that. So then I wrote the book.

Ken Puls (00:52:29): I would say you had one more advantage in that case is that you actually worked with the engineers on that team. So, if you got into a place where you were really stuck, you had a phone a friend that you could use. Whether you did or not is different.

Rob Collie (00:52:41): No, I did not have that advantage. I did not call them. I guess it was kind of a pride thing that I didn't want to ask them, but I was learning remote. I wasn't really part of the team anymore. And the DAX engine really had not come out yet when I left Redmond. I was already living in Cleveland when the DAX language was first available in builds.

Rob Collie (00:53:01): I had, had such an in-person style. If I wanted to ask a question to someone, I would usually go to their office physically. I felt like an outcast. They didn't do anything to make me feel like an outcast. I just felt like that. And so, I kind of, in a way, sequestered myself. I did learn as an outsider, even though I still had the Microsoft address, all that kind of stuff. Yeah, maybe others would have played that advantage better.

Ken Puls (00:53:25): This starts to explain your comment years ago on your blog about how all data pros live on islands. Now I get it. You just, man, oh man, just find your own island.

Rob Collie (00:53:33): I just wanted to live the authentic experience of being on an island. I had my volleyball, I painted it, called it Wilson. I wrote the book. And the way I tell the story is I grabbed the first copy off the assembly line, because I was there as it was being printed. I was following it. Thanks to Bill's connections, I was following it along the assembly line. I grabbed that first hot, still warm cookie from the oven copy of the book, and I run it outside the building and I hand it off to a courier who's just been waiting with the engine idling to take it to the FedEx place and get it to you overnight in Canada, and you read it, you said. You read it cover to cover immediately and said, "I still don't get it."

Ken Puls (00:54:21): Yeah, that happened.

Rob Collie (00:54:22): It did. It did happen. Yep.

Ken Puls (00:54:24): Except for the cover to cover part.

Rob Collie (00:54:26): Yeah. I'm starting to get that impression.

Ken Puls (00:54:31): Yeah. I've read some of it. As you identified a few years later, I have a specific reading style when it comes to reading technical books as well.

Rob Collie (00:54:40): That's right. Are you comfortable with me telling people what I think your reading style is?

Ken Puls (00:54:43): Go for it. I'll correct you if you're wrong, but I don't think I have to.

Rob Collie (00:54:43): Okay. Here's the only thing that's a fact. The fact is that I have told thousands of people the following story. That's a fact. So, thousands of people believe this, is that Ken Puls, which I didn't at the time really. First of all, Ken doesn't read books.

Ken Puls (00:55:03): That's actually not true. Ken does read books, he just tends to read them in a slightly specific fashion.

Rob Collie (00:55:11): All right. Okay. So, let me talk about what that fashion is. Ken is so smart, ladies and gentlemen, that when he reads a technical book, at the top of the page, he already knows what the bottom of the page is going to say. He just scans to the end of that page to get that payoff, that confirmation, that dopamine shot of like, "Oh, look at that. I was right again." And then he flips to the next page. And it just so happens that every now and then, the middle of the page doesn't say what he thinks is going to say, and he kind of goes right past it.

Ken Puls (00:55:41): All right. So, I'm going to change that narrative just a little bit. I'm a skimmer when it comes to reading. I wouldn't say that I open up and read the first sentence and know what the bottom is going to say, but I will say is that if there's five paragraphs on the page, I usually read about five sentences of that page, because I hit the topic sentences of these things and [crosstalk 00:55:57] whatnot.

Rob Collie (00:55:56): He hits the beats.

Ken Puls (00:55:58): Absolutely, the thing that I'm doing is that I will typically skim the paragraphs until I find the section that is actually important, and I will try and read that section through in a little bit more detail. Sometimes I read it five, six, seven times because I just don't get it. Because, guess what, I probably should have read the pages that lead up to it, but that happens. I mean, it's just the way that I sort of go through things. It's actually one of the reasons why, for me personally, I actually cannot learn by video, because I can't skim. It doesn't work that way.

Rob Collie (00:56:25): Yeah, I get that.

Ken Puls (00:56:26): So, what I ran into with the book is ... I mean, this is where we're going with this one is at one point in time, I was working through a calculate measure. The first version of the book that Rob put out had a really, really cool little flowchart in it that explained how calculate worked and modified filter context.

Ken Puls (00:56:42): So, I decided to be very helpful in my MVP style feedback granting personality that I am, and I decided to write Rob an email to tell him how his flowchart didn't work. But one of the things about sending in bug reports and whatever else to Microsoft, you're got to build a good one. What you really have to do is you have to be very, very specific. You got to be going through and you got to make sure that you hit all the technical points, you look through the process, you take it from start to finish.

Ken Puls (00:57:08): This is probably the first time with DAX that are really sat down and really analyze this to the depths of the degree that I did, and said, "Look, Rob, the way that you said this, this is what should happen. If your filter context is this and this and this, which is what I have, this would happen. And then when you take this step here, what it would do is it should rip ... Oh, damn. Nevermind, Rob, the chart is absolutely fantastic. But I figured, hey, I'm already through this three quarters of a page email. I'll send it to you anyway. Good job, keep up the good work." And I hit send.

Ken Puls (00:57:37): Dude, I've taught so many people after that flowchart today. I mean, it's a fantastic thing. But the reality for me is that I never really sat down and actually looked at it in the degree that I had. And once I did, I mean, this is what I'll actually teach people today is it absolutely works. Feel free to send me an email to prove how it doesn't providing that you go through this amount of work along the way. But the big keys are you have to know exactly what fields you're working with, and that was part of the issue that I ran into is that I was actually looking at a field for one table, and I was thinking that it was working off of a related one, which it wasn't. So, I was picking up the wrong things.

Rob Collie (00:58:09): I didn't remember it that way. Your version of it, which I believe to be the truth. I'm not saying it's your version. It's the truth. That's better than I remembered. The way you describe that, when I really got down to it in its primitive level, really detailed. There it is. That's really the nature of DAX is that you have to get to that level. You have to get to that really primitive level with it. And almost like turn off some of the smarter parts of your brain. It's almost like a smart suppression game that you have to become one with DAX.

Ken Puls (00:58:42): It is. Part of the issue that we can get into with DAX too is that we can try and get too smart with things like renaming fields or whatever else that maybe now you're looking at things and you're going, "Okay. Well, I know that this is related field from this other table over here." So, I'll just assume that, that's part of this. It's like, "What did you actually write?"

Ken Puls (00:59:00): Because what we always know with coding and with computers is that your computer will always do exactly what you told it to. whether or not you want it to? Well, that's a totally different question. But in DAX, it really, really came down to that. Well, what did you actually tell it to do? Because it's doing that. It's just that might not have been what you expected or wanted to see.

Rob Collie (00:59:19): So, Bill, in the dust of this event, we were trying to figure out some sort of extra that you could get if you bought the book from Bill's website versus Amazon. What is it going to be? And Bill just goes, "How about that chart that Ken was going on about, the flowchart?" I was like, "Oh, my gosh, that's exactly what we should do. We should do that." It's so weird to me that even though I sort of had this light bulb moment through that process with you, I didn't connect it with, "Oh, that should be the separate laminated handout card that we make." It was so obvious that it should have been, right?

Ken Puls (00:59:55): Absolutely. I mean, that is DAX, right?

Rob Collie (00:59:58): Yeah. Bill is just sitting there on the sidelines just sort of casually going, "I don't know. I'm just a simple caveman publisher, but maybe we should do the flowchart."

Bill Jelen (01:00:08): It was a four-page flowchart, right? Because all the tip cards we've done, laminated tip cards were just front and back. I was like, "Oh, man. This got four pages here."

Rob Collie (01:00:16): Never ask me to do anything with brevity. It's since then expanded to eight pages, it's four front and back. But that one page has remained relatively unchanged throughout the 10 plus years we've been talking about all of this. We did add a second piece to it, which was a set of exercises of recognizing what your filter context is, like showing you a bunch of different pivot tables, and circling a cell and saying, "Hey, what's this one's coordinates? What's this one's coordinates?" And the answers are on there. That one kind of goes hand in hand with the original, but that was like ... Really, in terms of that flowchart, that's really the only innovation we've ever come up with over the years that was helpful. That was really necessary to add to that.

Ken Puls (01:00:56): I think that was a massive, massive thing. And I use a variant of that when I'm teaching my courses today that goes through and talks about how to ... Basically, it's how to understand it. I mean, once you understand that, you can actually work out what is going on in the calculation of any measure along the way.

Ken Puls (01:01:11): I mean, I suppose if you get into iterators, you got some extra pieces that add to that complexity or whatnot. But truly, I mean, for most beginners, once they can actually wrap their head around a calculate and how that is actually playing around with things, that unlocks the mysteriousness that is DAX. Absolutely critical.

Rob Collie (01:01:28): Some of my most satisfying moments as an instructor is when someone asked me an off script question. I just write a measure really fast and it doesn't do what I expect. And I'm sitting there, and I'm actually like deer in the headlights in front of a number of people going like, "Oh, why isn't this working?" Oftentimes, someone in the class will figure it out before me by doing exactly what I told them to do. But even I, over time, it's like I get back to taking those shortcuts.

Ken Puls (01:01:58): I laugh. I was building up some material for something and was going through and trying to build something up and whatnot. I came into this thing, and I'm like ... I was working with all to remove filter context in something, but I removed the filter context from a specific field, and it didn't change the result. And I'm going, "What the hell is going on with this thing?"

Ken Puls (01:02:18): After struggling with it for a bit, I ended up going back and I'm like, "Okay, I got to go back to this thing." I went back to the drawing board, I'm like, "Damn it." I went and removed the filter context from the year field, but I had a timeline that was filtering the date field. They're two different fields, right? The fact that the timeline had a different filter context that passed through, but I couldn't see it, because it wasn't part of the thing. And yet, it takes that going back, once again, I knew exactly what it was supposed to be doing. Except that it was doing exactly what I told it to.

Ken Puls (01:02:45): Until you actually break it down to that, as you say, right back to the nuts and bolts there to figure out what's going on and really carefully identify all the pieces, you cannot debug the thing. I mean, it's like an accounting. Every now and then, when we're trying to figure something out, we go back to the old classic T account that they taught you in bookkeeping back in 101 just to follow the flow. Sometimes you have to do it. You always feel like you're going back to elementary when you do it, but sometimes that's here, it just works.

Rob Collie (01:03:09): So today or tomorrow, let's say, you realize you've got to go build a DAX power data model for something. And yeah, you're going to be using Power Query to shape the data to feed to us. You're going to be using the two tried and true world beater engines here. You'll be using the M engine, you'll be using the DAX engine, whatever you want to call them, they go by a million different names.

Ken Puls (01:03:31): Okay. So, it's another day that ends in why. Okay, got it. Go on.

Rob Collie (01:03:34): That's right. It's one of those days. Are you going to reach for Excel? Or are you going to reach for Power BI Desktop?

Ken Puls (01:03:38): I reach for Excel, every time.

Rob Collie (01:03:40): Still the Power Pivot guy over the ...

Ken Puls (01:03:43): Yeah, absolutely. And I have some reasons for why I do that. It actually comes down to that I believe that Excel and Power BI both has some very strong overlaps in what they do. And yet, I still feel that each tool has some advantages over the other in certain cases. I love Excel in that I get all of the advantages of the tabular structure via Power Pivot and whatnot.

Ken Puls (01:04:03): Granted I don't have all of the newer formulas, and that hasn't really impacted my life in a massive way for the analysis that I've had to do. If I did need something that wasn't in the tin with Power Pivot that was in Power BI, I would move to Power BI. But the reality is that working inside Excel with Power Query and Power Pivot, I get pretty much everything I need from the DAX engine, I can make it work. And I also get the ability of all of the unstructured analysis tools that Excel has.

Ken Puls (01:04:28): I can dump stuff into the grid, I can flip things to OLAP formulas, I can drill deep into the data that I want, I can summarize it, report it, I can add new things, I can reshape it, I can do whatever I need to do in Excel. That's a lot easier to do than Power BI. Power BI is much more rigid because the data model is always in a tabular format. Always, always, always. Where Excel doesn't have to be.

Ken Puls (01:04:47): Having said that, once I've gone through and I've done my analysis, I find it easier to prove out that the data is good using Excel because of the flexibility that the tool sets. And once I've got that the data is good, what I would suggest to most people is that you can go and use the publish to Power BI. So, when you're actually going to build up your reports that you're going to be sharing with people, you should be sharing through Power BI, because it actually has a security layer that can prevent it from leaking out across the organization in different areas.

Ken Puls (01:05:13): The visual story in Power BI is so much sexier than what we have in Excel. I mean, there's just no question in that regard at all. There are definitely things that exist in Power BI that we can't do in Excel, things like row-level security, that would cause me to go to Power BI Desktop right off the bat, because I can't do it in Excel. But if I can do it in Excel, I'll start there first. And then I'll publish. I mean, I have models that I use every day, where the source is an Excel workbook. It's published to Power BI, we feed our different reports and analyze an Excel through that. But the actual underlying data model structure is and probably always will be an Excel workbook.

Rob Collie (01:05:51): Interesting. So, I don't think that I've ever published a Power Pivot Excel workbook.

Ken Puls (01:05:56): Dude, you're missing out.

Rob Collie (01:05:57): To the Power BI service.

Ken Puls (01:05:58): Why not?

Rob Collie (01:06:00): I think I just figured I just didn't trust them to do a good job of it.

Ken Puls (01:06:04): What's interesting is when you build your model with Power Query and Power Pivot, and when you go and you hit file, export. Essentially, what it does is it takes those. I mean, the way that I like to visualize it is basically it's the same thing as you go on over to Power BI and saying import into Power BI Desktop. It takes those components, and it basically just publishes them to the server.

Ken Puls (01:06:21): You can schedule the refresh, I do that. It works as long as everything is going. The part that would frustrate you, if you're more into the DAX side is that you don't have some of the new DAX capabilities that exist in version 1200 that are in Power BI.

Rob Collie (01:06:37): Yeah, I don't really care. Whenever I'm talking to Microsoft engineers and the product team or whatever, they're always more bothered by the Power Pivot subset of DAX, like it not being current, then I think matters. I'm always like, "Are you kidding? I don't care." Don't really aren't that many things that I care about. I eventually have just folded. I don't start an Excel anymore. I now only start in Power BI.

Ken Puls (01:07:00): Yeah. I'm too freaking stubborn.

Rob Collie (01:07:02): I can't believe that I'm the one saying that. I think it's not so much I didn't trust them to do a good job of it. It's more like I just didn't want any sort of extra uncertainty in my system. I don't want to be jumping at shadows like the ghosts in the machine going like, "Is that because I did it in Excel?"

Rob Collie (01:07:19): And I end up going down this long route of trying to figure out whether that's the case or not, when it wasn't. I just didn't want that extra degree of variable in my story. Look, it's a Power BI service, it's probably expecting me to start with the pivot's file, so I'm just going to conform.

Ken Puls (01:07:35): I don't always do a good job of conforming to things. Call it a strength or weakness, I don't know. But maybe it's just that my love for Excel runs so deep that I don't want to give it up to Power BI in that place. As I say, I firmly believe that there are areas where you have to start in Power BI. If the features don't exist in Excel, you really don't have a choice.

Ken Puls (01:07:50): But at the end of the day, if you don't care about the new DAX functionality from my experience, and I'm not going to say that there aren't some niggles along the way somewhere that are going to cause some problems. But in my experience, that publish to Power BI works pretty darn seamlessly. So far as if all you care about is manual refresh, simple, publish Power BI, done. It's just the same as refreshing in Power BI Desktop and republishing over the old one.

Ken Puls (01:08:14): If you care about scheduling and refresh, well, then you got to worry about as your data source cloud hosted, or do you have a data gateway in place. But you can schedule the refresh there, because it basically just turns it into the Power BI dataset anyway. And then you're building your reports online instead of in desktop. I don't think there's too much stuff that's in Power BI Desktop for reporting stuff that's not in Power BI online, at least certainly not that I'm looking for.

Rob Collie (01:08:39): Yeah, I think. They've done a pretty good job of parity there.

Ken Puls (01:08:41): They really have.

Rob Collie (01:08:43): Do you have variables in the latest versions of Excel in DAX?

Ken Puls (01:08:45): As in VAR?

Rob Collie (01:08:47): VAR, VAR function, whatever.

Ken Puls (01:08:48): Yes, you can.

Rob Collie (01:08:49): Of all the new stuff, that's the newish. [crosstalk 01:08:51].

Ken Puls (01:08:52): That was the big one for me. That makes working with calendar patterns the heck of a lot easier. For me, I'm not building houses out of DAX. I mean, there's no question there. So, for me, I haven't missed the functionality of the new components there. I mean, there's new things with calculated tables, and there's other new wonderful things that are out there in the DAX language in the newer versions, but they haven't impacted my life. I haven't tried to chase them because I haven't really needed to. I mean, I joke with my kid. I mean, I'm an accountant. My math skills are sum. And then I subtract one thing from another too to get a variance. My math is pretty easy in my world.

Rob Collie (01:09:29): Help me out here guys. How do I get cannon Bill arguing? How can I throw raw meat in the shark pool?

Ken Puls (01:09:36): I don't know. I'd say Bill and I agree on most things, I think.

Bill Jelen (01:09:39): Yeah, we've been around too long. Microsoft has warned us down. The spiky edges that we had when we showed up, they've been dulled off.

Rob Collie (01:09:49): Good.

Ken Puls (01:09:53): The assimilation is complete.

Rob Collie (01:09:55): Yes, it is all going according to plan. Seriously, you're interchangeable in your opinions.

Bill Jelen (01:10:02): More of a go to. Yeah, I don't have to go to that Ken has.

Rob Collie (01:10:05): So Bill, I feel like we've wasted 100 minutes on your time.

Bill Jelen (01:10:10): No. This is fine. Because when I was on your show, Thomas LaRock said three sentences. And so I'm trying to live up to that. I think it's perfect. I'm just playing Thomas LaRock today.

Rob Collie (01:10:19): I'm trying to get Tom up to like 15 sentences. I don't want him to talk too much, because I got a lot to say. And then there's the guest. The poor guest needs to talk to, right? But the early Tom LaRock, we've been pulling him up a bit. We've been getting him up into higher percentages.

Bill Jelen (01:10:33): So, Ken, what's your opinion on Microsoft 365 versus the perpetual version?

Ken Puls (01:10:37): I can bite into that one. I'm 100% on the M 365 component. Back to the the managing of IT, I mean, I used to give up probably about two days of my work life for managing 50 licenses for our company in order to try and figure out which of the 950 license combinations we needed for people in order to be properly licensed for Office and Windows client access licenses. I don't know if you remember this, Rob, but Windows DVD writer licenses so that we could get the proper Excel Power Pivot SKU and other things like that. I mean, it was a waste of time.

Ken Puls (01:11:08): Once we moved to the M 365 or Office 365 at that point in time, the job became a lot easier, because it was just basically like, "Look, let's put a named user on there." Nice and simple. It was taken care of. So, that was the first part. We were no longer playing the game of, "Is there going to be a new version within three years? We pay Software Assurance, should we not? Oh, I'm sorry, you can't upgrade your Office Suite this year, because it's going to be a big capital expense versus with Office 365." Boom, we're now into a regular sort of cash flow, it just goes into operating expenses, made life a lot easier.

Ken Puls (01:11:41): So, for us in the corporate world, there's a couple of components. Number one is to cut down my time on licensing. The second part was it actually meant that I could keep my software current because there wasn't these massive lumps coming through cash flow that we had to justify as capital costs every three years. Instead, it was just an operating cost, it was always there.

Ken Puls (01:11:59): The part that really made that work, though, was when it first came out, and we first got the Office 365 license, it was always a numbers game about the cost. That was all it was, because we still weren't getting regular monthly updates. Once they moved into the whole regular monthly update scenario, at that point that just blew the whole doors off the thing. And now, to me, it's not even about cost anymore. It's about what's going on with the functions and the features and whatever else.

Ken Puls (01:12:23): We see something new come through virtually every month, even if it's just bug fixes and small things that maybe aren't super visible. But the fact that we're seeing things like dynamic arrays coming through. We're seeing new formulas that are coming in. We're seeing updates to Power Pivot and ... Sorry, not Power Pivot. I shouldn't say that one, but Power Query. We need to update the Power Pivot, that would be nice. But updates the Power Query, new connectors and different things there. And the occasional button being moved around, which drives me crazy. But hey, whatever.

Ken Puls (01:12:50): At the end of the day, the product is getting better on a regular basis, and people are sort of clamoring for these components, and asking for them in the real world. As an IT guy, what I love is when I install Office, it just updates, I never have to have IT go and visit the desktop. The cost per month for the most important software tool you use on a regular basis is pretty small. It's easier, it's more up to date. There's just so many things about it.

Ken Puls (01:13:18): Going back to perpetual man, I would never ever, ever go there. It drives me crazy. I mean, I joke with people and they say, "Which of the old versions did you buy? Oh, well, I bought this one here when 2019 came out. I just bought 2019." I'm like, "Oh, way to go on your purchase of obsolete software." They're like, "But it's brand new." I'm like, "No, it's not. It's six months old before it even came out of the box."

Bill Jelen (01:13:39): There should be a warning label when they buy that. You are buying obsolete software that will never ... I don't understand why they still sell it. That's the thing that confuses me.

Ken Puls (01:13:48): IT, that's why.

Rob Collie (01:13:49): I think there's an argument can be made that the single most important and impactful piece of technology to come out of Microsoft in the last decade is the Office Click-to-Run infrastructure that makes all that possible. In the old days, when it was all MSI driven install, the last thing you would have ever wanted in your entire life would have been Microsoft upgrading your version of Office overnight without your input, because it's going to screw your life up. It's going to downgrade or upgrade some DLL somewhere that is going to cause you nightmares, and Click-to-Run bypasses all of that.

Rob Collie (01:14:32): Think about it, like what you just said, I completely get that, but it's not a cost thing anymore. It's the fact that we get new stuff all the time like new valuable stuff that actually helps us. That new functionality, that would have been the thing that wrecked you in the old days. You don't want a perpetual model that's a subscription that's delivering a new MSI install over and over and over again. Oh my gosh. The economic value created for the entire world by Click-to-Run is just jaw dropping.

Ken Puls (01:15:01): I would agree. And honestly, Rob, that's not something I ever would have thought of. Because, I mean, it's just part of the component that sort of happens underneath the scenes. It's not sexy, it's not appealing or whatever. In managing, whatever, like 30, 50 desktops or whatever else, and you sit there and you go, "Oh, damn, we got to do an upgrade of Office. Okay, so we got to go on. We got to come up with tools to deploy this thing out across all these desktops, or we have to physically go and visit each desktop with the media and install it." And then hope it works, it doesn't blow up a DLL or whatever as you're saying.

Ken Puls (01:15:37): And granted, obviously, influence to somewhat pandemic wise, I've got a small team right now of about five people. We installed the Office client on their software once. They're all working remotely, because of the COVID situation. Office is just updating on their machine every time there's a new update that comes out.

Ken Puls (01:15:56): It's magical. I don't have to visit their desktops. I know that they're on the most recent and latest and greatest features. I mean, we run on the beta channel, and all of my team do as well, because we're insane and we like to be seeing the new stuff and talk about and all the rest of it. But it's a phenomenal delivery mechanism. And the new stuff that comes through it is just absolutely incredible.

Ken Puls (01:16:18): It always just makes me die a little inside when I do the survey in a class and ask people like, "So, what version did you buy and whatnot?" And you get the, "Yeah, I bought Office 2019." I'm like, "Oh, I'm so sorry. You should have got Office 365." But I hear the other side of this too where you get IT departments that are very concerned about the fact that Microsoft is updating their software.

Ken Puls (01:16:38): It's like, "Well, you don't have any issue with your antivirus, guys, updating their engines, so why here?" I think it goes to a lot of worlds where there's a very heavy reliance on compatibility testing, and worried that we're going to blow things up. But to my mind, there's avenues for mitigating that too.

Ken Puls (01:16:59): I mean we have a different channel in Office that holds back the releases for about a year. We've got a current channel that gives the current monthly release. We've got an insider channel. And I think there's actually one between those two as well.

Ken Puls (01:17:11): So, my sort of thing is, look, find your people who are your biggest power users in your organization, the people that really put Excel to the test, put them on the beta channel. Just make sure that they know that there are features in office to report bugs back and get the bugs that are hitting your organization reported back to Microsoft right away, but they've got to be your power users who are fault tolerant.

Ken Puls (01:17:31): And then you say, "Okay, let's take our next level of people." Maybe it's who uses Excel as a global base all the time inside your company, your accounting department, your analysts, or whatnot, and put them one version delayed behind that, so that they're getting new stuff, it's a second round of testing. And then you put the majority of the users on just whatever the regular monthly channel is. And the people who are in an area that needs to be on the most patched, the most stable for whatever reason, put them on the deferred, just be aware that those guys on deferred, they're not going to get the dynamic arrays until they filter through the rest of the stuff.

Ken Puls (01:18:04): I think there's avenues there, but I know some companies that have embraced that architecture, I know other ones that they just want perpetual, that's it. And I know still others who say, "Yeah, but I want to own my software." You don't own it anyway. It's just interesting.

Rob Collie (01:18:22): I actually have two questions for you guys. The two of you are both still and always were but definitely today are closer to the Excel community at large than I am. Do you still see array functions as like a really hot, well trafficked topic?

Rob Collie (01:18:39): My time on the Excel team, basically Wall Street ran on array formulas. Do you have any sense of that changing at all? Because really, what I'm wondering is I've always thought ... Not always, but for a long time, I've thought that DAX would be a godsend for the array formulas crowd, even in terms of just performance, much less capability. Do you have any sense whether the world has moved on from array formulas? Or is it still sort of the thing it used to be?

Ken Puls (01:19:06): I don't think that it's moved on from them. I think they're a tool. And I think with the dynamic arrays coming out that it actually starts to breathe new life into the array world. I can't say that I would have ever thought that, that world was specifically dying, though. I don't do a lot of work with Wall Street, so I can't really speak to what the engineers of those functions are working with out there.

Ken Puls (01:19:26): But in the community, when you start looking at array formulas and whatnot, if they the Ctrl+Shift+Enter style of array, it certainly takes a very high skill set to be able to actually put those together, and there's there's a lower number of people that would be comfortable in architecting, providing, and explaining those solutions.

Ken Puls (01:19:45): I think with the dynamic arrays coming out, though, that changes everything. Now, they're a lot easier to create. They're a lot easier to visualize in the grid. We're seeing formulas that are being built for arrays and whatnot. And now, with the lambda functions that are coming out, I actually see that they're probably going to end up getting more widely spread use, just because they're more accessible and a little bit more visible. That's just my thought. I think it's just a tool again like Power Query, like Power Pivot. But the nice thing with arrays is that they are leveraging the calc engine. They're super fast right in the grid.

Bill Jelen (01:20:14): Yeah. I think dynamic arrays are taking over, but there are still people who are using the old Ctrl+Shift+Enter arrays. We have books for both. We have books on dynamic arrays, and my current book on Ctrl+Shift+Enter. They're both still selling. The dynamic arrays is outpacing the old one, but people are still buying the old one. So, I still think there's hardcore people out there who are using the old fashioned array formulas. I think as the new dynamic arrays become more powerful, and they're adding more stuff regularly, I think they'll just take over.

Rob Collie (01:20:43): Isn't there a flavor of array formula, though, that starts to bog down on the same sort of performance problems that VLOOKUP has? It's the filter part of the ... If you think of the DAX engine, its filter and then arithmetic, it's the filter part that Excel has always been slow at. Arithmetic Excel is just as fast as DAX, but the filtering part is where Excel tends to sometimes kind of face plant in terms of speed.

Rob Collie (01:21:12): I guess I just have never really done that much serious array formula modeling. Model off folded, they're gone. And I got passed off to some other email list like Modeller's Miscellany or whatever. I don't know. I don't know what happened. That would be like sort of the arena in which we could see if anyone started to use DAX iterator functions instead of arrays in that competition, if it still existed. That would be sort of the place where we would start to see it peak above the waterline, I guess.

Bill Jelen (01:21:43): Yeah, I think that would take a special contestant that the people who are getting that level one model off are generalists. They have a lot of specific skills. And I don't think you would find that person bubbling up to the top 16 of model off if it still existed. But I bet there's people out there who are really, really into it. It would be interesting to have a YouTube video and make a statement one way or the other, and then people will make sure to let me know how wrong I am.

Rob Collie (01:22:06): This is, to me, like a really ripe field of the Excel community in terms of where there could be a massive breakthrough is introducing ... It's just a hunch. I haven't refined it clearly, obviously, but it's just a hunch that the array formula crowd, if they ever got ahold of DAX and iteration, they'd just be like, "Oh my god."

Bill Jelen (01:22:27): See, that's a book. That's a book.

Rob Collie (01:22:28): Hair blown back.

Bill Jelen (01:22:29): DAX formula is for the array formula crowd.

Rob Collie (01:22:31): There you go. Ken, you should write a book. Write that book, man. The world needs it.

Ken Puls (01:22:35): As Bill will tell you, I've already got a book I got to get finished.

Rob Collie (01:22:39): I promise to skim through it when you're done.

Ken Puls (01:22:42): I'll bet you do. I'll bet you do. Yeah.

Rob Collie (01:22:47): The other thing I was going to bring up was you actually mentioned it Ken. You know that scene on The Graduate where the father's friend at the pool party takes him aside and has given him some career advice, like the most important career advice you'll ever get? And he just looks at him and goes, "Plastics. One word, plastics." Dustin Hoffman said, "That's your advice?" Plastics. I feel like I want to just look at the Excel MVP community right now and go, "Lambda functions. That's where it's at.

Ken Puls (01:23:17): Lambda is a very interesting case. I think that it's going to be interesting to see when people start actually pairing up dynamic arrays and lambdas together and whatnot, and building stuff that is really perf intensive, or whatever else in the grid and does a lot of things. I mean, this is one of the things that's frustrating with Power Query, of course, is that everything's locked behind a refresh. And the thing with pivot table is, of course, they're locked behind a refresh, and that's what a lot of the dynamic arrays and potentially lambda functions could end up changing.

Ken Puls (01:23:43): I will say, though, that for myself, until somebody writes a really, really good book on lambdas, I don't anticipate that I'm going to end up playing in that sphere, mainly because it's got the exact same issues that I had with trying to debug my way through a DAX formula. So far, my experience with lambdas has been I've been able to start and get to where I can create myself an error. I'm trying to actually understand how to get past that is really difficult.

Ken Puls (01:24:07): For me, I've just kind of looked at and went, "Yeah, you know what, I think this is a battle that I'm going to let someone else fight and I'll work through and work on those. I'm going to stay in my Power Query realm at the moment." I do believe they're going to be incredibly powerful. Don't get me wrong, but there's some work that I think Microsoft still needs to do to give us a good debugging engine in that area.

Bill Jelen (01:24:24): Yeah, I agree with that. But I'm seeing a lot of people jumping on the lambda bandwagon, including MVPs and YouTube viewers. I'll do something on YouTube, and someone will say, "Oh, you could have done that with one formula. I'll put a lambda in there." The people embracing that new technology is just pretty incredible. Although, we're hitting some sort of a limit as far as the call stack that really needs to be increased before. I think you can get to real world use, but it's very promising.

Rob Collie (01:24:51): By the way, a guy that you both know, and I think have fond feelings for, Sam Rad. For a while, one of his characters that he played in World of Warcraft with us, his name was [Kal Stack 01:25:02], with a K.

Ken Puls (01:25:05): That's funny.

Rob Collie (01:25:06): He had another one called do events.

Bill Jelen (01:25:09): That's awesome.

Ken Puls (01:25:10): Yep. I remember that one actually. Yes.

Rob Collie (01:25:16): That's great. We're running to the end. Is there anything that either of you just been like sitting on like bottling up?

Ken Puls (01:25:22): I got a question for you that I'm curious to know.

Rob Collie (01:25:25): Okay.

Ken Puls (01:25:25): Years ago, Rob, when he left Microsoft, you emailed me and said, "Listen, dude, I want to PM an add-in, and I want you to write this add-in for me and whatever else." We did some work on that. And then eventually, you emailed me and said, "Look, I'm going to turn this over to you. I'm done. I'm moving on a different direction and whatever else." It took me a while, but I was curious, have you actually seen it?

Rob Collie (01:25:43): No, I haven't.

Ken Puls (01:25:44): You haven't. You need to do a little search and download Monkey Tools. I'll send you an email with a key for it and whatnot.

Rob Collie (01:25:50): Monkey Tools.

Ken Puls (01:25:51): Monkey Tools is an add-in that ties Power Query and Power Pivot together and does all kinds of cool stuff that you should totally be checking out.

Rob Collie (01:26:00): Oh my god.

Ken Puls (01:26:02): Since you're not aware. I mean, we do stuff like injecting calendar table pattern with Power Query. Because I remember the first version that we did once upon a time, I was actually building calendars in the grid, and you're like, "How are you going to update those?" We insert the Power Query to build the calendar table for you dynamically now, things like that. We do Power Pivot auditing, all kinds of stuff.

Rob Collie (01:26:21): Oh, yeah. You hear that Microsoft? If you don't put the button then, Monkey Tools will.

Ken Puls (01:26:27): I've already told them that on a couple of occasions, actually.

Rob Collie (01:26:30): I know this feeling. You talk to Microsoft and say, "Please, please take my idea that I'm planning to make money off of. Take it, put it in the product, I would prefer that." And they're like, "So, you just go build an add-in."

Ken Puls (01:26:42): We just recently added a functionality and it's a go and take your Power Query that you have and actually build a slowly changing dimension table for you. Like crazy stuff to help people with their modeling, audit reports to try and figure out how things are tied together, a DAX explorer, a Power Query explorer. We even have a little thing that you can click on your pivot table and say, "Show me the filter context," with what we call our pivot sleuth. And it will show you all the fields that are impacting that particular pivot table as well.

Ken Puls (01:27:06): There's all kinds of stuff in there, which was born out of the inspiration of the stuff that we worked on years ago. Dude, I'm hurt and shocked that you haven't seen it yet. Like what the hell?

Rob Collie (01:27:15): Oh, well, I don't consume anything. I'm pretty busy. Running this podcast empire, it's not all fun and games.

Ken Puls (01:27:28): Anyway, I'll send you a link, you should check it out. Because as I say, you were a big part of inspiring this thing, and we've had it up for sale now for just over a year, and we keep on adding new stuff to it. It's under active development. I think you'll find it ...

Rob Collie (01:27:42): Oh my god. Yeah.

Ken Puls (01:27:42): Make your Excel life easier.

Rob Collie (01:27:43): Yeah, that's awesome. If it's generating some of the harder M scripts for me. The M scripts generated by that, for example, are those like copy pastable? I could even use them in Power BI?

Ken Puls (01:27:53): You could absolutely use them in Power BI. I wish I could actually inject them directly into Power BI, but unfortunately, there's no back end hook to be able to actually write M to a Power BI model.

Rob Collie (01:28:02): Damn, dude. First of all, kudos. You know the old saying, right? 1% inspiration, 99% perspiration. Ideas are worth nothing, it turns out. You've gone and built something. Actually, that sounds a lot more ambitious than even the original idea, even in compost.

Ken Puls (01:28:17): It's turned into a heck of a lot more, absolutely. I've actually ended up hiring a guy to actually work on it. And now, I'm playing the role of program manager that you were going to play, but I'm also doing a lot of the coding and button scenes and whatnot.

Rob Collie (01:28:28): I'll try it on anyway.

Ken Puls (01:28:29): Absolutely. Try it on anyway. Beautiful. I'll send you a link.

Rob Collie (01:28:37): That's amazing.

Bill Jelen (01:28:37): Sounds great. Yeah.

Ken Puls (01:28:38): Yeah, it's fun stuff.

Rob Collie (01:28:39): Thank you so much, both of you. This is great.

Ken Puls (01:28:41): Yes. Good. See you guys again.

Bill Jelen (01:28:41): Thomas LaRock signing off.

Rob Collie (01:28:42): All right. Bye-bye. Bye, guys.

Ken Puls (01:28:45): Cheers, guys.

Announcer (01:28:46): Thanks for listening to the Raw Data by P3 Adaptive Podcast. Let the experts at P3 Adaptive help your business. Just go to p3adaptive.com. Have a data day!