Apr 27, 2021
- 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!