Mar 16, 2021
It was an honor to sit down with two of the creators of Power
Query, Sid Jayadevan and Miguel Llopis. You get to hear the
history of Power Query from their perspective! These guys are so
busy it took quite a bit of coordination to get them together to
record this episode. We hope you enjoy it as much as we
did!
References In This Episode:
Reese's Peanut Butter Cups Old School Commercial
Episode Timeline:
- 4:45 - The Origin of Power Query, and the importance of M
- 19:00 - How the various Microsoft departments come together for a big project
- 30:15 - The many uses of PQ and the Power Platform, error handling in PQ, and some solutions to some common problems
- 53:30 - What's next for PQ and Dataflows
Episode Transcript:
Rob Collie (00:00:00):
Hello Friends, this week's guests are Miguel and Sid, the data
integrations team at Microsoft. They were both around and involved
when Power Query essentially emerged from the primordial ooze at
Microsoft. And that's what this week's episode is mostly about,
which is, what is the Power Query origin story? I've long been
fascinated by that question as an outsider and observer, since this
all happened after I left Microsoft. It's kind of hard to imagine a
time when we didn't have Power Query. But there was actually three
or four years in there, when we were building DAX data models for
clients without benefit of Power Query. And this is where I use my
old man voice. Kids these days, they have no idea how easy they
have it, back in our day, we didn't have fancy Power Query, you
just had to cobble the data together by hand. And so I'm not just
fascinated by the origin story here, I'm actually deeply impressed
and appreciative, especially as a former software engineer, who
knows how challenging it is, just how gorgeous Power Query really
is.
Rob Collie (00:01:06):
And because of that, along those lines, during this conversation, I
kept trying to get these two gentlemen to take the victory lap.
They didn't take that bait, too humble, too cognizant of the work
yet to be done, which of course, is really how we would want it,
isn't it? Because seriously, it's good to know that there are
people like this at Microsoft, who are stewards, really, of our
futures. They're the ones building, not just the tools that we
already use, but the tools that we're going to use in the future.
There are a few places in here where we geek out a little bit as
computer scientists, but mostly, the conversation stayed very
firmly rooted in the human beings, the human element again, which
is what it's all about. I'm sincerely honored that they took two
hours out of their busy schedules to spend speaking with me and
speaking to you on this podcast. I hope you enjoy it. I hope you
learn something from it. So let's get into it.
Announcer (00:02:03):
Ladies and gentlemen, may I have your attention, please.
Announcer (00:02:07):
This is the Raw Data by P3 Podcast, with your host, Rob Collie.
Find out what the experts at P3 can do for your business, go to
powerpivotpro.com. Raw Data by P3 is data with the human
element.
Rob Collie (00:02:23):
Welcome to the show, Sid and Miguel, how are you today fine
gentlemen?
Sid Jayadevan (00:02:27):
Very well, thank you. Many thanks for having us.
Miguel Llopis (00:02:30):
Yeah, doing great, Rob. Thank you so much for having us, it's a
pleasure.
Rob Collie (00:02:33):
Seriously, to get a hold of the two of you, and coordinate
calendars and make this happen, it's an honor for us. So this was
something that almost from the moment we launched the podcast,
asking members of our team at P3, what would be interesting topics.
One of them that just sort of keeps insistently coming up is where
did Power Query come from? And so I was on this mission to hunt
down some of the people who could speak to that. And there's a lot
of things we can talk about. What are your roles today at
Microsoft?
Miguel Llopis (00:03:06):
Sid, do you want to go first?
Sid Jayadevan (00:03:07):
Yep. So I am an engineering manager at Microsoft Manage, the team
that works on data integration, with Power Query being one of the
key elements, but we have a variety of other things we do with
connectors, gateways, data flows, all of which are very symbiotic.
Power Query depends on all of those things, and all of those things
depend on Power Query. So there's a larger space that we operate
within, but Power Query has been around the longest of all of those
things.
Rob Collie (00:03:39):
So I didn't know this going in. But I have some complaints about
this QuickBooks connector. I'm sure you know what those complaints
are too. They will not be ones that you've heard the first time
from me making a note of that.
Sid Jayadevan (00:03:55):
No points for guessing.
Rob Collie (00:03:56):
No points. Okay. Miguel, what are your responsibilities today?
Miguel Llopis (00:03:59):
I'm the program manager lead for Power Query, and connectors and
data flows. So a bunch of technologies and experiences that Sid was
talking about. Been up on the team for quite a while, maybe about
the same as Sid, maybe a bit less. He's older than me, so that
would show through this interview.
Rob Collie (00:04:17):
Oh, yeah. Well, we're only recording the audio here, you can't
really tell.
Miguel Llopis (00:04:22):
I meant from his wisdom, not from the look.
Rob Collie (00:04:24):
Oh, I see.
Sid Jayadevan (00:04:26):
That's Miguel's way of saying I'm Palaeolithic.
Miguel Llopis (00:04:30):
There you go.
Rob Collie (00:04:30):
Oh, yeah. I see. You're also a free range gluten free?
Miguel Llopis (00:04:35):
Of course.
Rob Collie (00:04:39):
So you both go back aways on Power Query specifically. So from my
perspective, from outside the company, I left Redmond in 2009. And
then I sort of formerly left Microsoft in February of 2010. So I've
been formally gone for 11 years, and sort of informally gone for a
little longer than that. It's really kind of hard for me to even
get back into the before mindset, when we had DAX, and data
modeling, and we had the VertiPaq Engine, they're all still so
incredibly central to Power BI today. And we had them in the Power
Pivot form, we didn't even have SSAS Tabular yet.
Rob Collie (00:05:16):
But we had nothing in this giant void that Power Query came along,
to me, basically, out of the blue. I had no advance notice of this.
My sources in Redmond, my spies, they hadn't hooked me up with the
information that something amazing was coming to come along and
complement the tools. There are so many times, guys, seriously so
many times where I or we would be working with a client and we
would know what the ideal data model would look like. But they
couldn't do it because the data that they were getting wasn't in
the right format to build the right data model. Even something as
simple as you need a lookup table or dimension table, and no one's
giving it to you.
Rob Collie (00:05:58):
So it was always like, "Oh, now you need to go find a DBA," that
you're either lucky or you're not, you either had one or you
didn't. And if you were unlucky, you were just out of luck, there
was no recourse. You were just going to have to make these changes
to these tables manually. There was no automatic refresh anymore.
It was really a tremendous limitation. And then suddenly, and at
the time when it first arrived, what was it? The first name? Was it
Data Explorer?
Miguel Llopis (00:06:27):
Yes, Data Explorer.
Rob Collie (00:06:30):
Even that initial name sort of conveyed a different mission than
what it sort of morphed into. I don't even really remember. It was
also a way to connect to lots of different things, wasn't it? And
Power Query is today. Were you around when Data Explorer, when that
name was chosen? What was behind that?
Miguel Llopis (00:06:46):
Yeah, that would be an interesting story. So actually, just going
one step before that, and I think we're getting towards 2012 at
this point, I think. The very first incarnation of what today's
Power Query in market was something called SQL Azure lab for data
exploration. It didn't even have an actual product name, we're
going to call it that way. It was SQL Azure Labs was a set of
initiatives across the SQL and national teams back in the day to
actually a spike different sets of technologies that will help in
different segments, like data exploration will be one, data
visualization. There were a bunch of things that came out that
way.
Miguel Llopis (00:07:22):
It was actually a full cloud based Power Query like experience to
actually connect to data, transform data, and then output data in
different ways. There's ways to actually get your data out as an
all data endpoint that you could use maybe to create an app, maybe
to consume from Power Pivot, to your point drop. The feedback back
then, and again 2012 was, hey, Pivot is working in Excel, we want
these experiences in Excel. So we repivoted all of that, no pun
intended with repivoting, towards a client base experience that was
actually an Excel add in that we initially released for Excel 2010
and 2013.
Miguel Llopis (00:07:59):
There was quite a bit of naming related discussions, but I think
the data exploration aspect, everyone got used to it. So we ended
up coming up with Microsoft codename Data Explorer for Excel. That
was a very first name for the Excel add in, which later than that,
a few months later, as we went into GA, they actually got renamed
to Power Query. And really the alignment there was with the power
family, the power tools, Power Pivot, Power View, Power Maps back
in the day as well. And then Power Query as a way to actually bring
data in. Maybe Sid remembers some of these discussions more than
me. I know there was also an alternative option, which was Power
Import, that really, we went for the term query because it really
reinforced the notion of repeatability and refresh ability of those
queries, no pun intended.
Sid Jayadevan (00:08:45):
I think we tested many, many terms, and import was just one of
them. But we felt like the essence of the product was that ability
to query ad hoc and at will, and so we really wanted to focus on
that. And so that's how we ended up with the query piece of it.
Rob Collie (00:09:03):
I think Power Query was a great name. Honestly, it didn't really
land for me what y'all were giving us when it was still called Data
Explorer. That name was actually a very large cognitive obstacle
for me, explorer, it sounds like an analysis tool. Now, knowing
where the roots were, you had a name, there's a more appropriate
name for what you were doing, probably when it was still that Azure
Lab thing. But it's so funny, this happens all the time, when your
mission pivots, certain parts of it still kind of leak through,
like the old name, by default.
Rob Collie (00:09:36):
Just like the old story, whether it's true or not about the
railroad tracks being the width that they are because the Romans
chariot was that wide. It's just like, what we did yesterday is
because we did it that way the day before and whatever. Certain
things just have a momentum that carried forward. When the name
changed to Power Query, suddenly, I was like, "Oh, okay, this is
awesome." But it was Data Explorer, it was just a really cool
novelty. I didn't have a sense of its purpose, or I didn't feel
like it was a serious tool yet. It's really kind of interesting the
power of naming, isn't it?
Sid Jayadevan (00:10:08):
Neither did we, to a large extent, we were trying to find that
identity. And I think as we got deeper into it, the query first
aspect became a lot clearer.
Rob Collie (00:10:20):
I also think the Power Query versus Power Import, I think the right
decision was made there. The repeatability, you know this, but I'm
going to say it anyway, if I only get five minutes with an Excel
person who's never been exposed to the power platform, I have five
minutes and I have to drop their jaw, I'm going to show him Power
Query. I'm not going to show them the data model, I can show them
DAX. Now, I think that ultimately, you absolutely need to be using
both. But Power Query is such an amazing life changer for the Excel
crowd and they can immediately appreciate what it's going to do for
them.
Rob Collie (00:10:57):
It's harder for them to appreciate what the data model is going to
do for them. Which is why, if I've got five minutes, I don't go
subtle. Isn't it amazing? You're talking about figuring out your
own mission over time, that was something amazing. It sounds like
from what you're saying that the M language, and the engine that
goes with it, and all the stuff that's really difficult to build
and to design, a lot of that was already kind of done before the
Excel crowd became a focus. Is that true?
Sid Jayadevan (00:11:28):
Yeah, that's true. Power Query is essentially a visual interface on
top of the M language. The M language is absolutely the essence of
the product, it's the foundation. And that foundation was built
before the product as was often necessary, you need the foundation
in place. And there is a long history around M that predates 2012,
by let's just say, several years. And we won't delve into all the
details, but much like we had to get clear about what Power Query
was for and who we were targeting, there was a similar process with
M. M was from a technology point of view, this very simple, yet
powerful thing, in that it was functional, it composed, in our
opinion, reasonably well. And so you could do lots of different
things with it. But we wanted to put it in the hands of lots and
lots of people who didn't necessarily have not even a programming
background, but necessarily a query background.
Sid Jayadevan (00:12:29):
And so that was the goal that we set for ourselves to bring all of
those people on board, to make things possible for them that
perhaps were a little harder in the past. And so M was really the
foundation, and it was well in place CIRCA 2012, we made some
changes to make it more friendly to the visual experience, to make
it just a little more designer tool friendly, if you will. But the
core of the language was already in place. And on the language
front, we had tried lots of different things. And many, many people
at Microsoft were involved in that effort at many stages.
Rob Collie (00:13:08):
Something you said there really struck me and I wouldn't have
thought about it this way until I heard the history. You made some
changes, just some almost cosmetic changes to the language to make
it more friendly to the visual composer aspect of Power Query. As
soon as you said that, I'm like, "Oh, my god, yeah, this tool is
super, super, super friendly to being edited and written from a
graphical tool." A lot of times, you can go back into the M code
and hand edit it and the visual editor is still completely okay. It
totally understands what you did. That sort of round trip of hand
editing and visual composer, exposing both to the user and still
having a language and a tool that survives that duality, that's a
challenge. That's a really big challenge. I've tried it multiple
times at Microsoft. And I think I'm old for a lifetime on having
designed a system that worked like that. So I can certainly
appreciate it from a software engineering perspective, even just
that one little detail of a language that was already pre built.
That's kind of amazing.
Sid Jayadevan (00:14:08):
We're still working on it, it's very much a work in progress. And
there are aspects that are a little more, what should I say,
language oriented, that remain an M that remain extremely powerful
that the visual interface doesn't leverage quite as much.
Rob Collie (00:14:25):
I've been talking to Miguel about this. And it's not just a deep
power, but I think the Power Query transform, there should be many
more tabs, because the language is so flexible. And I know that not
everything can be turned into visual, some things are just
absolutely going to forever remain 100% in the realm of I have to
hand edit the M. I'm really nothing but a fan here.
Rob Collie (00:14:45):
I told Miguel on a previous call that back in the early 2000s, when
I was on the Excel team, and I caught the XML bug. The first
feature set that I was a lead program manager for was the XML
import and export capabilities. Not the XML file format, but data
payloads, invoices or whatever, be able to move those in and out of
Excel. And I spent two, four years of my life chasing a dream that
I call Data Merge. There are huge, elaborate graphical mock ups of
all of this, it was a really ambitious. It's exactly the kind of
project you'd expect a young software engineer to get all amped up
about and geeked out about.
Rob Collie (00:15:27):
The thing I thought we needed to do was build some sort of
repeatable data transformation logic into Excel. And I tried so
hard to get budget, to get approval, to get greenlit to build a
team to do this. And I got shot down four times a year for multiple
years, every quarter, I make another run at it, like, "Come on,
let's do this." Now that I've seen what you built, I am so glad
that I never got approval to dive into that. Because once I've seen
what it looks, a complete solution to it, I realized, oh my gosh,
we were so overmatched. We would have never, ever succeeded, never
come close. The fact that you had to go build a language first,
makes sense to me now in hindsight, but it's really chilling. Oh my
gosh, imagine they let me do my passion project. Thank you, Richard
McAniff for never believing in me.
Miguel Llopis (00:16:24):
Well, I'm sure you-
Sid Jayadevan (00:16:26):
Yeah, not so sure about that, Rob. You might have done a lot
better.
Rob Collie (00:16:30):
I doubt it.
Sid Jayadevan (00:16:31):
[crosstalk 00:16:31] Well, we'll never know.
Rob Collie (00:16:33):
Well, I do, and I wouldn't have it.
Miguel Llopis (00:16:36):
Well, Rob, I think you're being too hard on yourself. We don't have
answers to everything. I assume you would have just like us, just
fail fast, learn fast, iterate, learn from customers, learn from
you, Sid, and just refine and get better over time. Here we are 10
years later, or eight years later, and we still have a lot of
things to improve on.
Rob Collie (00:16:56):
It's not even really just about me, it's also that Office doesn't
have the right culture, to do something like what you've done. We
would have gone and tried to solve a handful of simple cases,
that's what would've happened under scheduled pressure. And we
would have gotten committed to a system that wasn't elegant at its
core. And then we never would have been able to really scale it to
address... Because you know how it is, if you address 99% of
problems that people have, it doesn't matter, that 1% is still
going to plague enough of their workflows, that is the difference
between they can adopt your tool or not. You've really got to be
complete. We would have never been complete enough. And I can say
that with confidence, knowing myself at the time, and also knowing
the culture that was around me. We would have never gone and done
the right thing, we would have hacked it, and we would have paid
the price. And it's not just a question of me not being up to the
challenge, just organizationally, we weren't at the right
place.
Rob Collie (00:17:48):
Office has really leaned into Power Query, it's a core part of the
Excel ribbon now, basically taken over the prime real estate. So I
think they're absolutely in on Power Query, and they're absolutely
in on the value that it brings. It's just that they're not the
right place to have invented it. In the same way that Office wasn't
the right place to invent DAX, it's just not what Office does,
Office does other things.
Rob Collie (00:18:12):
If I wanted to turn this around and say, the historical struggles
of the data side of the house has been that there haven't been
traditionally as good at user experiences as Office was. But that
gap is really closing, that has become an engineering discipline on
your side of the house that it really wasn't when I was there. I
used to describe Microsoft as there were user teams and engine
teams, and there was no such thing as a team that was both. Office
was the user team and the data platform, they built engines. But
the engine team couldn't build user experience and the user
experience team couldn't build engines. And so I think that's
changed a lot. And this is a great example of it.
Sid Jayadevan (00:18:48):
I mean to the point about Office and Excel, one of the things that
has been a little different with Power Query is that we've embraced
the open source model, perhaps a little bit more than for other
products like that. We have the Office team contributing very
heavily in our code base, not all aspects. It's the Power Query
team that drives the majority of changes, but the Excel team is
very, very involved. In fact, if you look at a lot of the
developments around Excel on the Mac, the Office team has
contributed very heavily to that. And so that ability to have other
teams come in and make changes and they've really been a poster
child for this on the Excel side, that has helped build Power Query
into more of an ecosystem even within Microsoft.
Rob Collie (00:19:38):
I didn't know that actually, I really had no idea that there were
Office engineers contributing code. I just sort of naively I guess,
assumed it was a one way street. You guys were sending them a build
update every now and then and they were ingesting it.
Sid Jayadevan (00:19:51):
And that is fundamentally how it operates because we do want
everyone within the larger Microsoft ecosystem to be benefiting
from the same enhancements, so there is a build that goes out every
month for Excel desktop. But we also have a lot of teams across
Microsoft who are contributing in a fairly big way.
Rob Collie (00:20:11):
In terms of, we talked about the M language and all of that. I just
told you the story about never getting the chance to do Data Merge
on the Office team. I'm really deeply curious about how the M
language got greenlit, how did the need for it get recognized and
bubbled up into something that they got resources. Because like I
just said, it's such a crazy thing. If you haven't experienced the
pain of the world, in terms of automatically munging and
transforming data, if you haven't experienced it, and most people
have it, even at Microsoft, most people haven't experienced that,
trying to convey that pain to other people is very, very difficult.
I look at Power Query as, look, this is something that the world
needed, not just like a demographic, this is something had improved
the world. And yet, I know from experience, it's very, very
difficult to explain to people who are already on board, what the
value is. Is there anything that we can talk about there?
Sid Jayadevan (00:21:06):
Without getting into all of the details, we went through a number
of iterations to get to where we are and where it started was with
some precursors to M, which were more about modeling, what we set
out to do. And there's a large number of people who contributed to
this. And so some of this predates some of our contributions. I've
been involved with the project on and off, in fact, I left at some
point and came back to it. And a lot of the seeds of the project
were in modeling related efforts. So ways of modeling your data,
modeling your relational data model. And as I guess, in hindsight,
could have been expected, folks started to realize that a lot of
what you needed to do to have a successful modeling environment was
enable transformations as a first class thing. And so at some
point, you had a language that was a little bit of data modeling,
and a little bit of transformations layered on top of that.
Sid Jayadevan (00:22:12):
And frankly, over time, we talked about how the query thing became
more and more important and became the essence of the product. The
data modeling side of things faded to some extent, and the focus
shifted towards transformation. And it shifted towards
transformation of all data. There was a period, not just at
Microsoft, but in the industry where very focused on data as not
necessarily a silo but homogeneous data stores. And when that
heterogeneity of data became a reality that no one was going to
change, the focus of tools like ours, and languages like M shifted
more towards that ability to embrace all kinds of data, wherever it
might live, of course, change the language and give us what we have
today.
Rob Collie (00:23:04):
This will show how old I am, there used to be a series of
commercials for Reese's Peanut Butter Cups, where two people would
be walking along, one of them will be carrying a chocolate bar and
one would be carrying an open jar of peanut butter for some
inexplicable reason. And they'd bump into each other and the two
would accidentally mix. And then they'd accuse each other, "You got
your chocolate in my peanut butter." "No, no, you got your peanut
butter on my chocolate." And then they would take a bite of it, go,
"Oh, my God, this is the best thing." It kind of has that feel to
it, doesn't it? The origins of M and Power Query, it's not like
there was this anticipated union with some DAX and what we think of
as the VertiPaq, Power Query data model. That wasn't a mission
statement from the beginning, it's just these two things ended up
going together super, super, super well, sort of an accidental
union. That's been my sense of it forever. Is that true?
Sid Jayadevan (00:23:53):
I think that's a very fair assessment. Miguel, what do you
think?
Miguel Llopis (00:23:59):
Yeah, I tend to agree. I was actually thinking about the previous
comment you made about the heterogeneous nature of the data space
right now. So yeah, really, when you talk about big data, it's not
really only about the volume of data, there's also the variety of
data, both in terms of the sources you connect to, the schemas they
have, the different keys on either side, and the need to use things
like fuzzy matching and mapping tables and whatnot.
Miguel Llopis (00:24:22):
And then lastly, is also about the velocity of the data, there's
some data that changes once a day, there's some data that changes
once a quarter, there's some data that changes multiple times per
second. And so providing tools for non technical users, which is
the vast majority of people in the world to actually be able to do
this efficiently and with ease and that even for somebody who can
do the hard thing, of course, who wants to do the hard thing if you
can do it much simple ways. I think that that was key to us and
just democratizing this whole problem space and of course, there's
a lot more that we can do.
Miguel Llopis (00:24:55):
And thanks, Rob for your list of suggestions from the team. We love
those and within our team, we do have this whole bucket of what we
call customer law, which is about, "Hey, give us a problem that
you've ever tried to solve with Power Query and it didn't actually
make the cut for you. And I will try and generalize that and give
you a feature out of it." That's how many of our existing
transforms came about.
Rob Collie (00:25:17):
It's just such a rich canvas. When you start from a language, you
have a lot of future flexibility in what you can do, it's awesome.
The heterogeneity thing again, I also really reacted to that, that
speaks to me. Another thing that shows my age, I grew up during the
peak Cold War between the US and Russia, or NATO and the Warsaw
Pact, whatever. And so I read a lot of Tom Clancy and I was one of
those kids.
Rob Collie (00:25:44):
Something that really strikes me from that is that the two
different philosophies of the 1970s, 1980s, Russian military
strategy versus the United States is, you see it in every morning
or every day of operations at a United States Air Force Base.
Everybody at the Air Force Base, gets out in a big long line, and
walks the entire length of the runway, picking up pebbles, and all
kinds of foreign objects from the runway, because if any of that
gets sucked into the intakes of these really sensitive airplanes
there's going to be hell to pay, it's going to break it, it's going
to go down. Whereas, the Russians built everything that they had,
at least in theory to eat mud.
Rob Collie (00:26:23):
I think the old world of BI was that 1980s American strategy. You
had to have this absolute clean room. It's ideal, frictionless
circumstances in order for everything to work right, which is, of
course, it's completely unrealistic. The real world is dirty, it is
noisy. There's chickens running across the runway, it's not just
pebbles. Is there even a runway? And this wave of Microsoft tools,
the Power BI beating heart, which Power Query is part of it, I
mean, it is built for that real world messy, dirty reality. It's
not the kind of thing that you imagine when you're sitting around
in a whiteboard doing computer science. And when computer science
can meet that kind of reality and perform, it's really something to
behold. It's just a whole new era, isn't it?
Sid Jayadevan (00:27:16):
Yeah, I couldn't agree more. It's messy and dealing with that
messiness is still very much a work in progress. But that's the
thing we're trying to embrace, that messiness that isn't going away
anytime soon.
Rob Collie (00:27:29):
It only gets messier, even our company.
Miguel Llopis (00:27:31):
But at the same time tools get better and smarter. So how can we
actually make it so that it's even easier and easier for you to do
these things with Power Query in this case?
Rob Collie (00:27:41):
Yeah. Some of the things that you can start to do with machine
learning and AI to write the code for them, there's some scary
stuff that can be done there. A no column by example is sort of the
most straightforward poster child for that kind of thing. I do want
to at least make one joke with you, which is actually the truth but
it's funny, is that back when I would teach classes, we still teach
a lot of classes, but they don't let me teach them anymore, because
I'm not as good as the people on our team. But whenever I bring up
M, and I would show people the code, and so we'd using Power Query
a little bit, and then I'd show them the code that it was
generating. And then I would zoom in on that code. And I'd say,
"And this word here at the beginning, tells you everything you need
to know about where this thing came from." The first word of every
Power Query script being the word let, I just talked about like the
messy real world reality.
Rob Collie (00:28:31):
But the word let at the beginning of every Power Query script,
tells you this came from the ivory tower. I look at the class and
say, "It's almost like a philosopher smoking a pipe, who then says
to you, 'Suppose.'" What if we pause it, and then the script
starts? Like I said, I admire what M can do. But the M language
itself doesn't speak to me in its raw form. I look at it and I kind
of want nothing to do personally with editing it. A lot of people,
especially on our team, they do, I'm just one of those people
that's like, for whatever reason, I was willing and able to learn
DAX and I typically don't learn stuff, I don't learn new tool sets,
I don't learn new languages. The fact that I learned DAX is really
an outlier for me. I'll never learn M, not in its raw form. I'm a
button pusher, dyed in the wool.
Sid Jayadevan (00:29:25):
And then we want to cater to all the constituencies, the folks on
your team who wanted at the end, we want to make that possible. And
for the many folks who would rather press the buttons, for that we
have the visual interface.
Rob Collie (00:29:41):
Do you have those personas behind the scenes where you talk about
the person who only wants to push buttons, you have the
unsophisticated user of M persona. Can we just name it Rob and I'll
give you a picture of a me going...
Miguel Llopis (00:29:53):
Actually, I call dibs on that one because I'm that kind of person
as well. And that's what I would push for most of the time.
Rob Collie (00:30:00):
Damn it. And you being on the team, you got an inside track to be
the persona. All right. Well, listen, I'm waiting in the wings.
I'll be your understudy. So how much of the two of you got an
exposure to the next part of the chain, which is, do you sit around
building Power BI models? Do you write DAX? Do you build data
models?
Miguel Llopis (00:30:20):
Yeah, big time. I mean, we use our tools, the tools that we build,
we use them internally for for example, understanding how users are
using our products or understanding our backgrounds and our feature
tracking report, you name it. Not to talk about personal projects,
I do have my personal projects with Power Query and Power BI as
well for non work related stuff. And that's actually, in my
experience of this is, to me has helped me the most actually
understand internalize all of the end user pain points around this
area and actually push the tool to actually become better. And I
know Sid does quite a bit of this as well.
Sid Jayadevan (00:30:57):
Yeah. The entire team does a large amount of eating our own dog
food, dog food, and you've heard myself term for this. That's
always been a very large part of what we've done. It's not just
about using Power Query, it's about using in the context of all of
the things that Power Query is hosted within. And so Power BI, of
course, and Excel and Power Apps, and aspects of Azure, we try to
ensure that we're experiencing the end to end experience as much as
possible.
Rob Collie (00:31:29):
It's just a complete divergence from the path we've been on. But I
want to at least mention to you before I forget that, in the past
seven days, last two work weeks, off and on I've been teaching a
little bit of Power Query to a high school football coach. We're
just kind of messing around for the moment with a Power BI through
a pro bono project. It's just sort of a passion project of mine. I
got to tell you, it's fun. This guy's eating it up. He's loving it.
I'm showing him how to add error checking and things like that for
when there's the temp Excel file still in the folder that he's
trying to load from, that's going to mess things up. Well, you
could filter that out and everything. And yeah, he sponging it up.
It's just cool to see it. It's all these unexpected places, you see
these tools end up being used.
Rob Collie (00:32:14):
So both of you seem to have a lot of opportunity to sort of drive
the race car that you build. And that was not something that I
really felt like I had much chance to do when I was at Microsoft.
It's like, we built race cars, we have no idea what it feels like
to sit behind the wheel. And so it's always surprising to people
that with whatever tool I've been working on, the customers were
better at using it than I was. It's nice that there's a little bit
more of a culture now of using the tools even for personal use.
Personal use is fantastic, there's nothing better than personal
use.
Sid Jayadevan (00:32:46):
Absolutely. As Miguel mentioned earlier, it's for both hobbyist
projects, pet personal projects, as well as internal day to day
work. Love using it for all of those things. And Miguel in
particular has I think some soccer things that he probably use it
for, but I'll let him speak to that.
Miguel Llopis (00:33:10):
Yeah, definitely soccer as well as a bunch of other things I
wouldn't name. Yes, quite a few personal projects.
Rob Collie (00:33:17):
It's really nice of you to call it soccer for us. I'm sure you
don't call it soccer with your fellow soccer fans.
Miguel Llopis (00:33:24):
Yeah. You mean with our football fans?
Rob Collie (00:33:24):
Yeah. What are some of the craziest things you've seen? I'm sure
that you've got just some really crazy stories of things that
you've seen customers doing with Power Query that you never would
have expected? Anything like that come to mind?
Miguel Llopis (00:33:37):
Many things. So I guess could take crazy in a couple of dimensions.
One could be unrealistic expectations on the tool or the
technology. The other one could be tremendously complex projects.
So I'll actually head down the second path.
Rob Collie (00:33:53):
Sure. Let's do that.
Miguel Llopis (00:33:54):
I think the biggest Excel workbook with PQ queries I've ever seen,
had probably about 280, 290 queries on it. I'm glad we introduced
query groups as a feature because that person will be there in the
world without them. But even there, it's a pretty heavy to maintain
project.
Rob Collie (00:34:13):
And the dependency.
Miguel Llopis (00:34:14):
Yeah, I was going to say understand query dependencies. So you do
have some support for that in Excel today with query dependencies.
We're working on way more interactive, highly visual experiences
that eventually will make their way into Excel. But as of now
available in the Power Query online experiences with what we call
the Diagram View, which is currently in public preview.
Sid Jayadevan (00:34:35):
290 queries.
Miguel Llopis (00:34:36):
Yep. And they're all legit, that we literally sat together and say,
"Let's simplify this." And actually, yeah, it could have combined a
few things, but it actually made sense the way he had it
organized.
Rob Collie (00:34:47):
And is the endpoint of that data landing in Excel?
Miguel Llopis (00:34:52):
Yes, it was inside an Excel workbook.
Rob Collie (00:34:53):
Wow. Wow. You don't have any examples of people using Power Query
or data flows to automate their home? For example, I have a friend
of mine right now, who is setting up using Power Automate, he's
setting up where if he gets a text notification from a certain
Internet of Things system, it will go in and adjust the temperature
gauge, the thermostat, turn on heaters, turn on humidifiers, things
like that. It's a terrarium, he needs to maintain the balance in
this biosphere that he's built. And he's got monitors in there, but
all they'll send them or text messages. That's all he can get. But
he's like, "No problem, I'll eat those text messages and feed him
into the power platform. And next thing, we're adjusting
temperatures and humidity and all that kind of stuff." I bet
there's a lot of stuff out there like that, it's data
transformation but analysis isn't the endpoint. It's being used for
something else.
Sid Jayadevan (00:35:54):
We're blown away by a lot of the creativity, seen a lot of these
very self regenerative programs that people have created, where the
queries adapt and do all kinds of things. It's a ton of
creativity.
Rob Collie (00:36:11):
One scenario, and now we're doing the program manager feature
design thing. And one scenario that I've wondered about for a while
is failures in a Power Query, the error handling. Using the moment
of error, harnessing that, and activating a human workflow to
address it. The way you're nodding, this is not the first time this
idea has come up, right?
Miguel Llopis (00:36:38):
Yeah, I was wondering if I had mentioned some of that stuff to you.
Because today, within the Power Query Editor experiences, you do
get some help with data profiling features, you understand
duplicate values, you understand errors. To some degree at least
within the data in the preview that was for you to run that over
the entire data set. But nothing really helps you with, after you
save that, and you say, "Yeah, refresh this thing every day at 8:00
AM." With understanding if that still is correct, if you get a new
outlier value, if you get a new duplicate value, and you get some
errors around that. That's one of the areas that we're looking at.
And it goes back to the thing we were talking about earlier about,
how can we further simplify this tool and make it more productive
for the real users of it on a day to day basis. And this is clearly
one of those areas. I mean, if you're putting together a report or
a dashboard for your boss, you want to make sure that they don't
start looking at the wrong data without you even knowing.
Rob Collie (00:37:32):
Oftentimes, it manifests in some very sinister ways. Like if a data
source succeeds in refresh, but it feeds you back nothing but
zeros. [crosstalk 00:37:42] There's no runtime error. And then, of
course, if you saw a report with nothing but zeros on it, you'd
notice, you say, "Oh, clearly, this thing's dead." But if those
zeros are only one leg of a five leg platform that makes a single
metric, the answers you get on your report can still be
credible.
Miguel Llopis (00:38:00):
Yes, that is a problem.
Rob Collie (00:38:03):
And I'm speaking from experience, I've been burned by exactly this
sort of thing in the past. Even when there's a runtime error, it's
almost always a human being that has to go do something. If a
duplicate key comes in, that wasn't there before, what do I do
about that? I have to-
Miguel Llopis (00:38:19):
Would it be nice if we just fix it for you? Or if we maybe ask you,
"We saw an issue and this is what we think you might want to do."
And we give you a couple of options. And maybe you don't even have
to go to the tool, maybe there's a quick text message you get,
maybe somebody is giving you a phone call while you're driving,
maybe it's an email that comes in and just with a couple of clicks,
you can just get it fixed.
Rob Collie (00:38:41):
This are all good ideas. I like this. This sounds promising.
Sid Jayadevan (00:38:44):
One thing that we recently added in this space was integration with
power automate. So that's more on the data flow side. And it's
early days for that, but we've already seen some very interesting
solutions. One of the things you can now do is have your data flow
include a bunch of these reports for issues that you mentioned, you
could perhaps partition off the errors or have a bunch of litmus
test queries that check the data quality. And if those queries
start yielding results, you can fire a power automate that can
engage whatever workflow makes the most sense for you. Whether it's
sending an email, whether it's writing something out somewhere for
someone to take action, going all the way to sending someone a text
message. All of those things are possible. They're perhaps not as
frictionless and out of the box as they could be, but we're making
some of those things more possible.
Rob Collie (00:39:42):
I think that problem of merging the automation with human like
referees of the occasional error is probably as ambitious of a
problem to address as Power Query was originally. I've got a lot of
respect for that problem, placing myself in your shoes. Might not
be that quite that ambitious, but it's a large problem. It's a
product level problem to solve as opposed to a feature. Every now
and then like, I get some data where someone keyed in an
exclamation point instead of a one, because their shift key was
down, and all hell breaks loose over that exclamation point.
Rob Collie (00:40:24):
You got a hard job, the error tracking in your system, it's many
levels deep. We all know the experience of you get the error, and
the top 11 errors all say exactly the same thing. And you scroll
through the list to get to the one at the bottom that tells you
hopefully, what really happened before the downstream errors
happened. It's hard to bubble up the right error to the right
person at the right time when almost by definition, you don't know,
you can't anticipate what this error is going to be, you have no
idea what's going to come in. So I recognize this as sort of a
frontier for you, but I do not mean to trivialize it at all. It's
only an improvement. It's not like you need to do this otherwise,
everything you've done is... No, you can stop today completely and
Power Query is arguably complete, you just have so many places
where you could-
Miguel Llopis (00:41:16):
Go and tell that to Satya, we want to still keep our jobs. Got to
find new challenges.
Rob Collie (00:41:20):
Well, next time I talk to Satya, next time he calls me up for
advice. Yeah, I think it would be a shame if you did stop. It's a
compliment to what you've got, that if you stopped today, it's
already well past amazing. I'd say to students and clients that
there are two engines at Microsoft, two data engines in particular,
that all of Microsoft's competitors wish they had it instead. What
are you going to call the DAX and data model VertiPaq. Microsoft is
not very good at naming, I don't know if you all know that. And
then the other one is the M engine, the Power Query engine, which
also by the way, goes nameless in all of your products. It's just
get data or import or whatever now, getting transformed.
Miguel Llopis (00:42:03):
It's the M engine and Power Query is the experience.
Rob Collie (00:42:06):
These two engines, wherever you call them, they belong in the
software Hall of Fame. I believe that. And this is a very vicious
critic of software, who's talking to you right now. I hate
software. And these two things, they demand your respect, it's got
to feel good to have been involved in something like that from such
an early stage. It's got to be one of the most gratifying sorts of
experiences for a software engineer because most of the time, it's
not like that.
Miguel Llopis (00:42:33):
This is such a tough interview, Rob.
Rob Collie (00:42:36):
To make you guys feel all gushy about yourselves.
Miguel Llopis (00:42:43):
Yeah. Don't know what to say [crosstalk 00:42:44].
Sid Jayadevan (00:42:43):
That's very kind of you.
Rob Collie (00:42:43):
Oh, come on, you've lived it. Right? You've probably also lived as
software engineers, you probably lived the other kind of project
too. There's all kinds of dead ends in software that you can chase
them for years.
Sid Jayadevan (00:42:54):
I think one thing that's been a big differentiator with this one
is, so Miguel and I are here today, but there's a team that has
stuck together over an extended period of time. And it's the most
fun I've had in my time at Microsoft. I'm very, very fortunate to
work with those folks. For a problem like this, there is a kind of
continuity that becomes necessary to... You talk about the
iteration and needing to keep going. And we have a lot of work
ahead of us.
Sid Jayadevan (00:43:24):
But the thing that has made this easy and fun, at least from my
point of view is the team has been phenomenal. You tend to have a
lot of churn on teams, and you go through phases, and people come
and go. But this has been one where there's a set of fun. And I'm
not talking about a handful of folks, it's probably a few handfuls
of folks who really pushed on this over many, many years. I think
that's one thing that's been a little different vis-a-vis a lot of
other projects, that there's been a set of folks who have stuck
with it and have been incredibly passionate about it. And that's
been a big part of Power Query.
Miguel Llopis (00:44:02):
Completely agree.
Rob Collie (00:44:03):
Some products really require that kind of continuity in order to
continue being successful. Excel, by the way is one of them. I
think Excel, I don't really know what it's like today, but when I
was there, there was pretty healthy turnover every release on the
Excel team. And the developers, the engineers, the actual writing
the code, they had a bit more continuity, actually quite a bit more
than the program managers. It was every two years, the school bus
would drive up, all the program managers we get on, it would leave,
new school bus arrives with younger program managers and would drop
them off. I got off that bus one day and enjoying the Excel team.
And the engineers on the team were just like, "Ah, the new
youngsters, we got to train these people now too."
Rob Collie (00:44:55):
It was a year and a half of working on Excel before I stopped
coming up with feature ideas, like wouldn't it be cool if Excel
could do this. It was a year and a half before I stopped coming up
with ideas like that, where they'd look at me and say, "Yeah, we
already have that." Honestly, I think that culture, that continuity
was enforced more by a handful on the Excel team when I was there,
they were keepers of the flame, if you will. And there was like one
on the program management team, half a dozen on the dev team.
Sid Jayadevan (00:45:27):
And you have a lot of those projects where you'll have one or two
keepers of the flame. And I think what's been unusual with Power
Query, at least compared to other projects I've been on is that
there have been many, many keepers of the flame. And of course, you
want fresh ideas. So you want people to be coming in and bringing
those ideas, and we've had a lot of that as well. And so there's
been keepers of the flame, there have been challengers of the flame
in a very good way. So we've had that mix. But there has been a lot
of good cohesion.
Rob Collie (00:45:59):
It sounds a good title for a Kickstarter funded board game,
challengers of the flame. I'll tell you what, well, you all get
equal rights. We'll call it a common intellectual property, that
name. I'm here by seizing 1/3 ownership in Challengers of the
Flame, LLC.
Sid Jayadevan (00:46:19):
What was the board game at the end of Office Space, the jump to
conclusion board game?
Rob Collie (00:46:28):
I don't actually remember, I've seen that movie so many times. Now,
I've got an excuse to go watch it again. Tell my wife, "Listen,
this is important. This is for work."
Sid Jayadevan (00:46:38):
That was our quandary, what do you name the thing?
Rob Collie (00:46:43):
So how much commonality is there, I'm assuming a lot, between data
flows and the version of the M engine that lives in Power BI?
Miguel Llopis (00:46:55):
Basically is the same engine. So data flows, the way I like to talk
about this is layers of the onion. So if you think about the M
engine as the core of the onion, then the next wrapper around that
is the Power Query experience that allows you to create queries
that run in M. Outer layer on top of that is really the data flows,
which really automate and orchestrate many different sets Power
Query projects that were defined with a Power Query experience to
generate M that runs.
Miguel Llopis (00:47:23):
So whereas you could have a data flow that maybe brings say, your
customers data, your customers table. Or your customers entity, you
may have another data flow that connects to that customers entity
and then maybe does a bunch of additional Power Query and M query
transformations to do your customers who are most likely to churn.
And it's the orchestration of that whenever that customers table
gets refreshed, cascade refresh everything else that depends on it.
That is what data flows are.
Rob Collie (00:47:55):
That makes sense to me. One of the challenges that I know that
Power Query faces is that at tremendous scale, when the data is
just gigantic volumes, the elapsed time of a query can get up
there. And it's just an optimization thing. It's almost like the
ideal software problem to have as engineers. How much progress has
been made over the years? I haven't really been paying much
attention to it. I just remember from the very early days, people
saying, "Okay, it's great, but we can't use it for the 500 million
row data set, going through a Power Query, just takes too long."
Have there been any strides made? Again, I'm really sympathetic to
this, it's a really hard problem. Power Query has to process every
single row, it can't do the things like the VertiPaq Engine does
where it sort of groups rows into clusters and treats them as one
band of rows, you don't get those really nice columnar in memory
tricks when you're performing transformations. So you're kind of up
against physics in a way.
Miguel Llopis (00:48:56):
Yeah, great point. So there's actually two avenues we can take to
answer that question. I'm going to talk about both, I'll just call
them out. And then I'll answer the easy one and I'll let Sid answer
the hardest one. One is about increasing the scale of what you can
process with Power Query. And of course, you need to do that. But
on the other extreme, there's also the make it clear to the end
user clicking those buttons as Drobo usually does, that there is a
problem, and so that they can correct that problem before it
actually becomes the root cause for things that are many, many
steps further down the pipe.
Miguel Llopis (00:49:29):
And so on this area on making things more clear to users, we're
actually introducing quite a few new features. We just announced
something called the step folding indicators. So it's a feature we
recently launched inside Power Query online, inside data flows that
as you connect to a data source, let's say, SQL Server, and you
connect to the customers table, and then you apply a filter to
maybe say exclude customers in the US, then you get your filter
versus they will actually give you a tick next to it to say, "This
has actually been pushed down to SQL because SQL can run filters
like this one." Now you go to a different operation that does not
fold. As a new step, it will actually immediately tell you, "Hey,
this thing is no longer when I run in SQL, we're running it
locally, we're compensating here, this is what's going to happen.
In the extreme, this might actually cause you issues, click here to
learn more, learn some best practices for how you could do things
be different." And many other features that we're working on there.
This is the most basic way to tell the most basic end user about,
"Hey, there might be a problem," is like the engine in your car
dashboard.
Miguel Llopis (00:50:33):
We're also looking at things like query plans, more detail, deeper
information for slightly more advanced users who actually
understand the underlying SQL, the underlying code behind it, to go
reason about okay, where are actually things going south? How do I
understand this better? So I answered the easy part of the
question, which is how do we make it clear that there's a problem?
Now Sid can talk about the exciting stuff we're doing on the
scale.
Sid Jayadevan (00:50:56):
And I'll have, I guess, an unsatisfying, cryptic answer to that,
because it's probably our largest area of investment right now. But
we don't have anything that we can really announce yet, but it's
something we're working on. And that should come as no surprise,
because we hear a lot of feedback in the space. And there's a lot
going on at Microsoft and in the industry in this space around
making compute more available, even if your data lives somewhere
where there isn't compute. And so that's something that we will
definitely be investing in and that we're actively working on.
Rob Collie (00:51:32):
I actually find that to be a very satisfying answer. Because
honestly, all I want to know is that A, people are working on it,
and B, there's optimism, there's still improvements to be made.
That's all I really need to know. I mean, there's a nerd part of
me, it's like, "Okay, come on. How do we do it?" But even then
probably, if I got too close to it, I probably go, "Oh, yeah, now
we're on board." I'm really just interested in the fact that it's
going to happen. Have either of you seen all of these, a meme, but
it's a YouTube meme, a format that's Hitler losing his cool,
screaming at his generals in the bunker, and the subtitles have
been replaced with something completely different? You seen
these?
Sid Jayadevan (00:52:15):
Yep, seen those.
Miguel Llopis (00:52:16):
I've not, I'm too young for that.
Rob Collie (00:52:18):
Oh, really? YouTube didn't go and record Hitler in his bunker. I
don't know if you know that YouTube is relatively recent invention
that probably has happened in your lifetime.
Miguel Llopis (00:52:30):
But I just don't have time to watch it. There's just so many soccer
games to go watch. Sorry, football games.
Rob Collie (00:52:36):
Football games. I agree. So I made one of those a long time ago,
making fun of Tableau. And in terms of the first three months of
its existence it's probably the video that's been watched the most
of all the things I've ever done on YouTube. There's a part at the
end where he mutters under his breath, he turns to look at his
subordinates and say something like, "And if you think we're paying
for those Alteryx license, you better be sprucing up your
LinkedIn." So as the Power Query folks, I made that joke for
you.
Miguel Llopis (00:53:09):
[crosstalk 00:53:09] Geek.
Rob Collie (00:53:10):
There's a lot of inside baseball in that video. Even the Tableau
employees that have seen it, look at me say, "Okay, that actually
was pretty funny." What's next? What am I not asked about? Such an
exciting space with so many opportunities.
Sid Jayadevan (00:53:26):
We have a whole new interface coming in terms of a more
diagrammatic visual representation of the queries. Miguel may have
alluded to this before. That's a big one, changes the profile of
the product quite a bit. We're not taking anything away. And we
don't want to make things tricky for people who are familiar with
the existing interface so it's strictly additive. But that's one
we're really excited about. We've tried a few things there. It is a
new interface, but we're also using it as a way to address some of
the feedback that people had, just surround how you track
relationships and make it a little more fluid to chain things
together. So that's one that I think the team's very excited about.
So we're going to push that one out pretty soon. And that's already
in preview, so you can go play with it.
Rob Collie (00:54:19):
I think I should as one of the absolute sloppiest designers of
Power Query scripts in the world. If you ever want examples of
really, really, really ugly, I can't believe this Rube Goldberg
sequence that someone's written, all you need to do is just ask me
for anything that I've done. I've got stuff now that I'm just like,
"Okay." I've got four queries that are basically one to one
linearly feeding into each other, that their only purpose is to
feed the next one. And they're not even sorted in the proper order
in the query pane. Even I don't remember which one is the root, I
don't remember which one is the first one in the assembly line.
Every time I go back and look at I have to re sort of trace, trace,
trace, trace. Like, "Okay, that's right. That's how this thing
works." You want ugly, I got you covered.
Miguel Llopis (00:55:06):
Yeah, we would love to see those and see what we're reinvesting and
actually behaves against that. So yeah, I just sent you a link on
the chat window for the Diagram View, would love your feedback on
that. Let's you share feedback about every other area. And again,
we will take it and we'll generalize it, and we'll make it into
something that improves the product.
Rob Collie (00:55:26):
And this poor high school football coach, his first exposure to
Power Query is with exactly the example I just told you about. He
has no idea how much better it can be.
Sid Jayadevan (00:55:37):
That's cool. You know that ad hoc style of using the product where
you don't necessarily architect how your queries come together, in
some ways that we want to cater to that even more, we don't want to
go in the direction of some very formal modeling exercise. And we
want to keep enabling that style of using the product. And so this
tool is not meant to police any of that, it's more just to help you
understand it better. I have some mashups where I have so many
queries, and they could have been designed way, way better. And
over the passage of time, a few months later, I look at the thing,
and I have no clue what I did, back when I created it. So this is
meant to help with those sorts of things. It's more to help you
decipher what others did, and sometimes help you decipher what you
did.
Rob Collie (00:56:29):
A previous version of you is almost just as inscrutable as another
person's work. I can be away from it for two days and come back and
go, "What was I doing here?" Same is true, by the way, with
spreadsheets, traditional spreadsheets, non DAX spreadsheets. I can
generally go back to one of my DAX models, and pretty quickly get
back into the personality of what I was doing there. But the old
spreadsheets, using just the Excel formula language, and really
pushing it to its limit, oh my gosh, those things. I'm always
impressed at how smart I must have been in the past to have put one
of those together. The current version of me always feels dumber
than whatever the version was, that was able to do what I did in
Excel back in the day. So, connectors?
Miguel Llopis (00:57:14):
There's a roadmap on connectors, there's some... Overall, our
strategy with connectors is one where we have the custom connectors
as the key that empowers anyone to build connectors. This could be
you building your own connector for whatever you're trying to do.
Or this could be an actual ISB company who owns an underlying data
source backend, who actually wants to provide connectivity to that
from Power BI from Excel. And we do have certification programs
around that.
Miguel Llopis (00:57:42):
So really, there's some new connectors coming out of our team,
there isn't much in terms of net new connectors. There's a bunch of
connectors that our team owns from the early days when we didn't
have this way to actually extend our SDK. And there is where you
see most of our investments on making sure that X connector now can
use this certain new feature that the underlying back end added or
that customers are demanding now, more than others.
Miguel Llopis (00:58:09):
So I wouldn't say there's much in terms of excitement there on
connectors to cover at this level, is very point wise feature level
things on existing stuff, does have experiences, Power Query
experiences. So yeah, everything you want to talk about regarding
diagram views, or more by example, like experiences infuse AI into
the product. On the data flows prompt, we talked a little bit about
the refresh base data quality and monitoring stuff, which is
actually not formally in our public roadmap. But just because I
think the discussion we have, it just screams at, hey, this is
actually a useful area that I think is just okay. Those are kind of
the big pillars.
Rob Collie (00:58:47):
There was something interesting that as you were talking about the
connectors. Of course, Microsoft cannot write connectors for
everything, the list of everything is damn near infinite. And
reasonable percentage of the time, the systems that I wish there
was a connector for is a non Microsoft product that, at best is
sort of neutral towards integration with Microsoft technology and
other times it's openly hostile to it. And so, at our company, of
course, we're Microsoft at its core, we use a lot more Microsoft
than other stuff.
Miguel Llopis (00:59:21):
Come on, you don't need to apologize, what else are you using?
Rob Collie (00:59:24):
We use a lot of things. And so like Salesforce is our CRM, and in
terms of workflow, it's one of our most central systems. Certainly
not our only system. Right off the bat, we've got an alien right in
the middle of the story. And we park a lot of data for our own
internal BI. And by the way, our internal BI is very, very, very
sophisticated today, it's not a stretch to say that we simply could
not survive without it. It's not like our business operates and
then we use BI to optimize it, it is life support. It's the oxygen
supply, it is really, really, really, really critical to us and our
business model.
Rob Collie (01:00:07):
So we use another third party product called Stitch, which you've
probably heard of that they've written a bunch of connectors
essentially, will then dump data into various endpoints that they
know about. And so we get a lot of data out of our core systems
into the Azure Data Warehouse, so not just Azure SQL, via Stitch,
and then Power Query kicks in. It's not like, it just lands there.
Gosh, our Google AdWords data, we're grabbing that from Stitch into
Azure Data Warehouse.
Rob Collie (01:00:40):
And then because the data that's grabbed... It's so weird, guys.
AdWords data is like day to date running totals. So every time you
take a snapshot of it, it's like you had three clicks last hour,
now you have seven clicks. Does that mean you have 10 clicks today?
No, you have seven. So we've got Power Query that is doing a group
by and taking the max, or grouping by the most recent timestamp on
that day, because Stitch doesn't do anything magical for us, all it
does is just raw data dump from one place to another.
Rob Collie (01:01:14):
It's really neat like this, going back to that metaphor of, you
want your jet plane built for the reality of the world, with all
kinds of noise, and all kinds of variety, and all kinds of
unpredictable things. And even without dedicated Power BI
connectors, for a lot of our systems, it doesn't matter. We're
going to get that data. And the fact that the Microsoft tools
participate in this larger ecosystem.
Rob Collie (01:01:39):
I've always been really ambitious about defining sort of the new
template for what consulting firms should look like in this new
world. It sounds like a cliche, but 11 years ago, I'm sitting in my
office one day in Cleveland, and I was using Power Pivot and it
just hit me like a thunderbolt. I'd suddenly done something that
was not possible. And I'd done it in a space of like an hour that
had taken weeks and weeks in the previous world. And I could kind
of see that the world was going to change, that the size and
duration of a typical project was going to shrink dramatically,
still have the same amount of impact as the big long project, in
fact, actually better. It's going to have more impact because the
short projects means that you're actually holding people's
attention long enough to iterate and get the real results that the
longer projects never got to because people got too exhausted, and
just called it done even when it wasn't. So the size of the average
project was going to compress dramatically.
Rob Collie (01:02:37):
So the utilization model for a traditional consulting firm, which
has long been like park a handful of people on a six month minimum
project. That whole business model was going to die. Now I thought
it was going to happen a lot faster than it has, it still hasn't
happened, really. We've reached the point where the world is
intellectually, agreed that citizen developer model is primary, and
is important. But for a long time, that was still heresy. So we've
reached the point, we've intellectually accepted that.
Rob Collie (01:03:06):
But that doesn't mean that the real on the ground muscle memory has
changed. This has been the mission for 11 years, go and build this
firm. However, we never took any investment. It's not like really
people who would ever want to fund a consulting startup anyway.
Angel investors and venture capitalists, they're always looking for
tremendous intellectual property. They don't want people involved.
Consulting firm has too many people, it's too good of a deal for
too many people. They want something where you can essentially
charge rent when it's done. So we wouldn't have really been able to
attract that kind of funding anyway. Plus, they would have ruined
it if we had taken their money.
Rob Collie (01:03:40):
So we've organically grown, all of our hires, and all of our growth
has been funded out of revenue, which makes it slow or slower
anyway. But someone told me something a long time ago, which is
like, "Let me tell you about my 10 year overnight success. It's not
overnight, but it has been 10 years." And I'll be completely honest
with you, I think that there's really no limit to how large we can
be. It's been a long road, but the way we operate is to run with
these tools as fast and as impactfully as they allow. So we're
great for the customer. We're great for the customer in a way that
I don't think really any other Microsoft partner is. It's a very
hard business model. It's obviously the thing that the customer
needs. But it's a hard business model to sustain which by the way,
we've used the Microsoft platform to make it work internally.
Miguel Llopis (01:04:32):
So you're ceiling, your bottleneck is actually going to be at the
very least talent acquisition so that you can scale to more people
as you scale to more customers.
Rob Collie (01:04:41):
Yeah. I learned a lot of things at Microsoft about interviewing
too. And we're using a lot of systems, we have a lot of actual both
software and delegation to assistants and things like that, that
allow us to scale. The hard lessons that I learned about
interviewing at Microsoft, we apply that at national scale. So we
have like a 2% offer rate for our candidates, and we get to pick
the best of the best. So I actually don't think we have a supply
bottleneck either.
Sid Jayadevan (01:05:13):
That's a lot of interviewing.
Miguel Llopis (01:05:15):
We're hiring. So if you have any pointers, we appreciate them too,
both engineering as well as PM.
Rob Collie (01:05:21):
Well, I don't know, that's the kind of consulting fee that I'm
going to have to [crosstalk 01:05:27]. I'm going to have to have
McKinsey white label me, so that I can charge Microsoft the
millions of dollars that they would pay McKinsey, but they would
never pay Rob Collie. Definitely, yeah.
Sid Jayadevan (01:05:42):
Very interesting. Fascinating story. I mean, I've watched from
afar, but I didn't know many of these details. And so yeah, very
helpful.
Rob Collie (01:05:52):
The engineering mindset, I think both of you actually would be
really sincerely kind of interested and fascinated by all the
things that we've developed and found about how to incentivize the
right things with our consultants, for our clients. And there's
something almost, it's not patentable, it's not protectable. But
there is something in the same way that software has intellectual
property, our system are all up system, software, people workflow,
all of that. I'm pretty sure this is the only instance in the world
like it of a company that operates like this. We've had to discover
how to do this rather than there was no template to follow. You
guys both know how exciting that kind of problem is. The same sorts
of things that get you geeked up about going to work at Microsoft
to solve that performance problem or whatever we're talking about,
that same itch being scratched, but in a different plane. No, you
can't have any of my people, Miguel.
Miguel Llopis (01:06:48):
Good to know.
Sid Jayadevan (01:06:50):
And have you been geographically distributed throughout?
Rob Collie (01:06:53):
Yeah. It was really like 2015 was the first time that I realized I
was bringing in the demand for work was exceeding my personal
capacity to address it. And it was just me running the website and
doing the trainings and doing the consulting up until that point,
basically. And I knew that I didn't have time to train up another
consultant that could do the work that I was doing, I needed to
find someone who was basically ready today. And I knew that I
wasn't going to be able to do that, if I was just like, "Let's just
find someone in the vicinity of where I currently live."
Rob Collie (01:07:30):
So the very, very, very first candidates, the very, very first
interviewing that we did as a company was remote. And the first few
people to pass this interview, which again, was designed 100% from
my experience interviewing program managers at Microsoft, and
especially the fact that I've done it the wrong way for years and
then I did it sort of the right way for the last third of my
career. The first people to pass it were in all over the country.
They were in Oregon, they were in Iowa, they were in Iowa, they
were in Alabama, and I was in Ohio.
Rob Collie (01:08:05):
So it's actually something that's really interesting. And I'm
almost a little bit bummed about the fact that COVID has rewired
everybody this way. Because for a while, I think we'll still have
this advantage for a long time in a way, but especially given the
nature of the consulting industry that we're in, which is still
very in person. When you can hire from any geography, you can
afford to be a lot more selective. You just have a bigger
denominator. If you want to hold a really, really high quality bar
and clear it, you can do that, if you're not geocentric. So in a
way, we were kind of forced into behaving optimally from the
beginning. It wasn't some fiendish genius plan, like, "Oh, we will
be geo distributed, and we will therefore get the best talent, and
bahaha." It wasn't like that at all. It's just like, "I need a
person and there's no way that I'm going to find one in Cleveland."
And all followed from there. So it's really insane. Heck of a
journey.
Sid Jayadevan (01:09:09):
It's an amazing success story. And sounds like you guys feel like
you're just getting started.
Rob Collie (01:09:16):
And trust me, plenty of failures along the way. I found out
somewhere along the way that I actually am not good at running a
business. And it's like you find out you're not good at driving a
boat and the way you found out is I just crashed it onto a reef. I
did that. I almost killed my own baby at one point. And I had to
realize that I needed to share the steering wheel. And so the guy
whose podcast went live this week Kellan, he's the architect of
almost all of these good things I've been talking about. My vision
and the things that I wanted to have happen, never ever would have
met reality without Kellan to bring them to life.
Rob Collie (01:09:54):
And he was one of the first people to pass the interview. I hired
him as a consultant originally. I had no idea that I was hiring my
other half at the time. It took a long time for me to come to terms
with that. So hard road, lots of humbling, really humbling
experiences. Well, guys, I'm sincerely grateful to be able to grab
a couple hours of your time. Thanks for doing it.
Miguel Llopis (01:10:16):
Thanks.
Sid Jayadevan (01:10:16):
Thanks for having us.
Announcer (01:10:18):
Thanks for listening to the Raw Data by P3 Podcast. Find out what
the experts at P3 can do for your business. Go to
powerpivotpro.com. Interested in becoming a guest on the show,
email lukep@powerpivotpro.com. Have a data day.