Power Fx parity with Microsoft Excel with Greg Lindhorst

[mark]: 1:19
today we're going to be discussing power fx today's guest is from Washington in the us he works at Microsoft as a principal pm architect in the power apps team He loves empowering people to create their own solutions you can find links to his bio linked into other things like that in the show notes and anything we discuss that the if we talk about any resources et cetera they will be in the show notes as well with that welcome with the show Greg

[greg_lindhorst]: 1:47
thank you mark great to be here

[mark]: 1:49
good to have you on the show i always like to start with you know introducing to the audience in a way that's not just about your role and what you do at Microsoft so tell us a bit about what you do outside of micros maybe family food friends fun what whatever you do that when you're not working

[greg_lindhorst]: 2:07
so outside of work these days is dominated by i have two freshmen in high school and so running around to sucker meats and ultimate tournaments and outdoor back packing and hiking and so there's a lot of activity around their lives that takes a lot of time so that that that dominates my my life other other than power effects

[mark]: 2:30
yeah how have you found going back to the office is such what's what's your type of work model that you're finding for yourself that's working in this kind of post covid time that we're in now

[greg_lindhorst]: 2:42
well i'm taking advantage of the fact that microsolis very flexible in this i have not gone back to the office i gave up my office

[mark]: 2:48
well

[greg_lindhorst]: 2:48
and said i'm just going to work from home and for me that's worked out really well so far i do into the office to meet with people from time to time for offsites or design discussions but in general i worked from the house which is really convenient for all the stuff i talked about in there go all the socker mom and driving the kids around and get them to school and all that it it's difficult to see spending the time to commute into work when i could be just working or taking care of that instead

[mark]: 3:18
i've found myself in the last two weeks i've been you know sick had the flu don't have covid but just have the flue and it's funny that now like i'm working and i've worked today better i was if i had to go into an office i wouldn't have gone in today because um you know you cough out in public these days people looking at your sideways right

[greg_lindhorst]: 3:39
right

[mark]: 3:39
it's like

[greg_lindhorst]: 3:39
right

[mark]: 3:39
a different world now even though you know val dated time and time again i don't have covid it's kind of like there's this i don't know a bit of a new social stigma and like people like if you're sick do not come near like we don't want you at the office

[greg_lindhorst]: 3:52
correct

[mark]: 3:52
at all but what i'm finding i'm working anyhow and so what would normally be a day in bed i trying to recover i'm actually of a multiple customer meetings today multiple engagements because we are remote so it has allowed new paradoms of being able to work perhaps when and one should be taking a rest maybe

[greg_lindhorst]: 4:11
yeah indeed

[mark]: 4:14
tell me about your journey into Microsoft how did you end up in this organization what path did your career take to ultimately end up in what you're doing now

[greg_lindhorst]: 4:23
i've been microsophet a long time coming up on thirty years and i came into i came to the company working on four trandfortran power station was my first product

[mark]: 4:33
wow

[greg_lindhorst]: 4:33
and that comes from my time in hulot packard where i worked on compilers for h p x in one of their language labs and so since then i've always been drawn to languages and expressing logic for a long time in visual studio working on visual basic visual inter dat if anyone remembers that short lived product those were some of the stuff i worked on but i kind of got tired of professional developers being my primary customer

[mark]: 5:00
right

[greg_lindhorst]: 5:01
i wanted to i wanted to more of a challenge than that i guess i wanted to be able get normal people to be able to express logic and that that don't concern themselves developers that there's so many more regular people out there that have a need for some piece of automation some little piece of logic that can help their help them in their lives i'd love to empower them to do that that i moved from visual studio into office to work on the web part framework which became a part of share point

[mark]: 5:30
right

[greg_lindhorst]: 5:30
ultimately excell and access in foot path all those more low co tools that microsopt was was fostering during that period and then called my friends who had worked i worked it on access started up sienna which became poweraps and so i joined them and then went from there and really have enjoyed this ride we've been on with a power platform growing up and so much adoption and i really feel it it's really it's got a lot going for it a lot of the mistakes that we made with some f the other products they seem to be ave been resolved here an there's a lot of funding for this this effort a lot of people a lot of energy behind making the power platform really successful and that's great to see

[mark]: 6:16
i love it i love it so we're going to talk about power effects and i have so many questions to ask you about this and let's start with when you're asked what is power effects and you know some of the things that i've heard jumped is it another programming language that we need to learn um you know what was wrong with you know the java script or type script

[greg_lindhorst]: 6:40
yes

[mark]: 6:41
et cetera that was already available to use how do you explain it

[greg_lindhorst]: 6:47
well here's many many different ways to explain it in those terms compared to professional languages professonal languages give you a tremendous amount of control you can do anything you want like type script and job of script c sharp but for the kinds of apps that most people build for the for their business that are trying to solve business ranted problems it's kind of overkille and they can get themselves into trouble wi we you can see in power effects for example we everything is nasincrnis call even if you don't know it if you make a call to a web service we'll get off the u i thread let that happen keep the u i response automatically wait for the promise to be resolved and then give the result back it's really easy for people to not get that right trying to write sincrenis job criptit's not for a professional developer it's very straightforward and makes tons of sense but if you try to get a normal person to get that it's it's a lot so we came at this from an angle um it is a programming language but it is meant to be very concise very it is strongly typed so that we get a lot of support at compile time compile time being when you edit a formula because we're constantly compiling about is there a problem with what you've written is in tell a sense giving you a good feedback on what the elements the fields and columns that you're using out of a table or those correct or the right types or the right rations happening all that's there to help you support you as you're writing that code in real time so that's that's one answer to it i can also give you a more computer science answer which is it's a functional language it's very much like excell it tries not to have side effects unless it has to bells are the one piece that we added on top of what excel gave us because if you think about excell you can you can't really put a button on an excel spread sheet and have it do something you can do that with v b a but you can't do that directly the excell formula language it doesn't have imperative logic it doesn't have side effectvul state manipulation the only way of cell changes is by a user typeyougt a new value into a cell or were calculating and that's the basis for for power effects and for power apsis to try to use leverage as much as possible of what excel has taught hundreds of millions of people about how to logic take that knowledge and and leverage as much as possible earlier on i spoke to andrew hylesburg who is one of our technical fellows who did see sharp and did type script and eat made a really important point to me is like you never want to teach somebody something you really want to leverage with the aledyuknow even if you don't like it even if you would fix it don't fix they already know it it's not that average of knowledge is so much more important than getting every little detail of the language exactly the way you might want it to be even if it's bad go with it and so that's what we try to do

[mark]: 9:40
it's interesting in that you know you mentioned your experience with other load code products and really

[greg_lindhorst]: 9:46
my

[mark]: 9:47
the mother of all load code products is excel and

[greg_lindhorst]: 9:50
yes

[mark]: 9:50
and it has been it's been that tool that a beginner can start using it very quickly you know maybe a simple calculation of you know the grocery list or something like that and then you've got pro debs that build

[greg_lindhorst]: 10:04
yes

[mark]: 10:04
full applications on on it and so it's cells an amazing tool except for that it's highly portable to a degree that the data set can be lifted easily without the control of visibility of of you know compliance and things like that

[greg_lindhorst]: 10:23
correct yes

[mark]: 10:23
so so the lessons learn here the lessons learned if you like from access and i'm keen to explore a little bit on access shortly um and excel has that how have you leverage that nowledge and that you started with a clean slate right on what you built here

[greg_lindhorst]: 10:42
right there were lots of lessons we've learned along the way on the access side we were building access web applications which were recently retired ah they had data macro's u i macro's ways of expressing logic which were low code oriented we learned a lot about how um users don't understand how to deal with errors they just on't even think errors are a thing they just not conscious of it and so what we did with access is we made everything transactional everything wrapped in a transaction if anything failed at all failed so the end user had the ability to roll that back as a whole and so those kinds of it that helped us tremendously whereas access with v b a that was wrong with a client if there was a failure v v would just keep going unless you took some action and it would corrupt your data base it would modify these tables these other ones because there was a problem and you get it into an inconsistent state we we need to protect and help our makers understand not even understand help them avoid those kind of pitfalls where they won't necessarily think about these problems that might crop up we needed to do that for them and ensure that their as are successful even if there thinking like a professonal developer who would think about eros oh

[mark]: 11:59
so so and what's been built so far in power effects what's the parity level with excel is and from you know i can do a lot of things on any cell within cell apply different logic et cetera how does how does that compare to what we have with power fix

[greg_lindhorst]: 12:18
uh they have four hundred plus functions and we have maybe a hundred and fifty so we're way behind on the function count and that's that beyond the core language of how the operators in this antics of data types work that's the biggest piece that's needs to be short up the excell team is also running ahead they added the lambda function recently which allowed them to write user defined functions in the formula language itself they're using they're adding using that to reduce and map and other functional language concepts to excel awesome stuff and we have we've not had an opportunity to add that stuff ourselves yet but we plan to so

[mark]: 12:56
right

[greg_lindhorst]: 12:56
it's and ever re always trying to keep up we are different than excelled because we do have this imperative logic element in an application you have buttons that need to do things that need to submit of record to a data base so we have to we have to think about that side of it as well how do we manipulate mutate state that excel doesn't have to think about but the core my goal is that those s that you can now write and excell with a lamb to function i would love to be a place where that same function can be used verbatum in power as as well you can have a function library that's consistent patible between the two and there are some differences in our language which will prevent that from hard percent true a hundred percent compatible but i hope for the most part we can be compatible and you can write formulas and functions can be inter interchanged between the two

[mark]: 13:48
very cool what's what's your telimitary telling you around adoption how how much has the ecosystem bought in started using it is it what you expected i assume there'll always be a ramp up time on something like this has it been a year two years now it's been a market parafix

[greg_lindhorst]: 14:07
uh power effect has been well so it's been about a year and a half since we

[mark]: 14:11
go

[greg_lindhorst]: 14:11
announced it

[mark]: 14:13
okay

[greg_lindhorst]: 14:13
not not even ga so we are

[mark]: 14:14
okay

[greg_lindhorst]: 14:15
we we announced it it one of the conferences early last year

[mark]: 14:21
okay

[greg_lindhorst]: 14:22
we have been working to put it into data verse in the form of formula columns it went into power apses for commanding so that you can write commands in a model driven application that's written in power effects person power virtual agents is got it in private review right now and we'll be going to

[mark]: 14:40
right

[greg_lindhorst]: 14:40
public review in g a towards the end of this year and so that's and then we are also working with other teams like the power auto mate des team um so it's early day is really honestly we are still working with the other teams were learning about their requirements and what we need to bring to the language of course it's been in canvas since day one and so we we've got many many users there who already take advantage of an enjoy power effects

[mark]: 15:08
and so its use and model driven would that be come up via data verse and how that would be presence because you mentioned it was already um and you've put it into data verse

[greg_lindhorst]: 15:19
it's in dante or some formula columns is the main way we're doing that which we are we were already in data verse for teams we've been there for a while we are in the process of bringing that to normal normal quotqoedata verse where full data verse where you're using the power apps tall to interact with it and you write your formulas that's in process and i'll be coming and he is being rolled out slowly as i speak that's one exciting area and it can ultimately replace calculated columns that are in dat er day that run through work flows

[mark]: 15:54
well

[greg_lindhorst]: 15:54
and roll ups eventually doesn't do real upshot but that's the vision of where we want to go with that and that's really the first step on data verse there's so much opportunity there to look at

[mark]: 16:03
wow

[greg_lindhorst]: 16:03
business rules to look at power effects based plug ends having server logic written in power effects that does what plug ins do today so there's there's a long there lots of stuff we're exploring

[mark]: 16:15
is the intention that it will work across all five of the tools

[greg_lindhorst]: 16:20
my

[mark]: 16:20
and power platform

[greg_lindhorst]: 16:22
eventually yes it will take it'll take time to get there but power

[mark]: 16:25
even

[greg_lindhorst]: 16:25
automat

[mark]: 16:26
even power pages will you ultimately get there

[greg_lindhorst]: 16:28
we're talking to them too i guess so that

[mark]: 16:30
nice

[greg_lindhorst]: 16:30
we're actually working talking with them power automate is probably the biggest one there besides power apse and that's going to take we're having discussion with them too but that's goin to poably take longer and we're using power to mate death top as a precursor to that learning from

[mark]: 16:43
wow

[greg_lindhorst]: 16:43
that experience

[mark]: 16:43
great

[greg_lindhorst]: 16:44
before we go after power automate

[mark]: 16:47
if we camped excel and and power effects is there anything that you empower effects that you can't do an excell

[greg_lindhorst]: 16:54
oh lots of things lots lots of things i mean there's there's all the imperative logic like riting back to a data base which excel cannot do but what the imperative logic aside we have things like a jason function that allows you to create a jason string out of concatinating other stuff together building a data structure and putting puttin it out there having records and tables nested together that's something that excels is working through structure data but they're not quite there yet we're definitely ahead there because we needed to be build it um model which you get out of a data based like sequel or data verse so there's there's lots of stuff that because we're in application development platform we've been thinking about in doing which they because they're not quite like that don't we're trying to stay as consistent as we can any we add something i do have a conversation with the excell team and so if you thought about this and were thinking about this design like we just added string interpellation something that they went oh that's interesting and we talk their parker people and like do you think you could add this to the partner and excel like yeah we think we could not that they're gonna do it any time soon but we want to make sure we're not getting ourselves into a corner where they couldn't add something if they wanted to the future ah

[mark]: 18:13
when we look at the maker community from you citizen do to let's say it developer then through the pro dive

[greg_lindhorst]: 18:23
yes

[mark]: 18:26
this power effect is it for everybody in that spectrum should everybody have a working knowledge of it or your thoughts

[greg_lindhorst]: 18:33
yes yes i think it's the widest spectrum i can think of for a programming language we do try to reach all the way up to that professional developer all the way down to somebody who doesn't even know right to write the formulas and so talk about no code solutions which are our g p t theory work builders that help you write formulas and help you bug and understand problems with formulas so that you start learning how to even write formula if you don't know how to write excel like formulas today but the sub strate which you persist that stuff into should be a power fect formula so that you get the leverage up that scale all the way so you can go to an advance maker and get help with it professional can come down and look at it and go away stil understand what that logic looks like so we are we are trying to bridge that that spectrum as much as we can and just like an accent what we heard from access developers is they could build stuff faster cheaper better by using access than they could potentially a professional tool out of visual studio not thing visual studio but they but the kind of ape he were building they didn't need the level of customization possible with visual studio and we hear the same kind of thing here that if you can embrace the power effects and live with limitations and talk to us about those limitations and well we'll get those resolved but the simplicity of it taking care of a syncruslogic for example take care of air handling for you all those things we do for a professional developer they appreciate the speed and ease with which they can do it over what they might have to learn and work through if they're using a more advanced professional language and then ultimately we do believe in these fusion teams where you have this person who understands the business need and they can communicate and work together on building these these solutions all the way up with a professional developer and they can move seanlessly up on that scale that is one of our visions and one of the tings we're trying to enable

[mark]: 20:26
one of the things you know across my career i've often noticed in the various companies i've worked for and this is probably pre my time been working with as a partner with microsoft

[greg_lindhorst]: 20:35
yeah

[mark]: 20:36
is in any org nization you go into you will often find the x l guru right they

[greg_lindhorst]: 20:42
right

[mark]: 20:42
know how to do things in gore and sorry in the l and they might have got to that point that they even started learning v b but they weren't they're not developer is per pro type type experience but they got there they worked it out they fumbled through and you know i worked in a bank for a period of time and

[greg_lindhorst]: 21:02
yes

[mark]: 21:03
excel was the main loan originating calculating everything to right and

[greg_lindhorst]: 21:08
absolutely

[mark]: 21:09
every day you'd have to get the latest version because it did did overnight et cetera by that team that did it do you see that power effects that people will really future proof their careers in a way by learning this and being that to persons and there in their companies

[greg_lindhorst]: 21:27
absolutely absolutely especially because it's gonna be spread so far and wide across the power platform that you can take that knowledge i talked about leverage from cell to power as we really want knowledge from power ap to powerautomate to power virtual agents if you learn this this language and learn how to do or trying to keep the patterns consistent between how it's adopted in these different hosts as well you learn how to do a thing in poweraps automatically have a sense for how to do it in power automate and can be successful that much faster so yes

[mark]: 21:57
i like it

[greg_lindhorst]: 21:57
absolutely absolutely i see that happening

[mark]: 22:00
it's what jumped to mind is the consistency of m three six five across all the apse if you use

[greg_lindhorst]: 22:06
yes

[mark]: 22:06
one ap know how the ribbon works you know where things are it's consistent g p t three and how much is that playing into what you're doing because you know using a this way to really enable people to do not really understand the formula they want to write and yet you know type in what they want to achieve and then it goes

[greg_lindhorst]: 22:32
yes

[mark]: 22:32
away and does it

[greg_lindhorst]: 22:34
yes

[mark]: 22:34
how much is that playing into what you're doing how you're thinking and how far do you reckon you can stretch out paradime

[greg_lindhorst]: 22:42
pretty far it is early days for that as well and we've we've only adopted it in empowerapps in a couple different places

[mark]: 22:48
yeah

[greg_lindhorst]: 22:48
there are plans to

[mark]: 22:49
yeah

[greg_lindhorst]: 22:49
do more there were also working with the prose team which is the same team works with excell to learn by example the same technology and excel where you have a table you start typing a couple of characters in a next column and it goes oh this is the translation transformation they want we're also working with them to see how we can apply that to power as as well the formula bar that's in power apse we are making that available to p v a and these other other hosts as well we'd like to build those that kind of functionality directly into the formula bar so that because the formula bar is really our r u i for power effects and it's much it's much richards text box it has got so much going on with intellicence and communications with the back end compiler because we're incrementally compiling your formula all the time every character we're taking a look at that thing is it proper and what what do we need to do with it if nothing else just for syntax coloring and for finding syntax errors as quickly as possible so that piece we're talking about enhancing it you know being able to put in natural language queries in any of the power effects host where they're using that formula bar and having that translate with g p t three into a formula that can be used and applied in that host so there's a there's a lot of runway there i don't think we know yet how far it will go and where i'll be useful but as i say it's early days we're still were still learning and putting stuff out there and getting feed back on it and how telemetry on useful it has been

[mark]: 24:19
so exciting it's a great time to be in this space tell me resources if people want to go find learn more i can put them in the show notes what what do you recommend you know for makers that are already out there but really haven't sunk their teeth into power

[greg_lindhorst]: 24:35
there's a number of articles that of course part of our documentation set but that's just the tip of the iceberg it's more the reference or information that you might want there's some articles about specially what power fix is and how you might apply it because we did open source this language anybody it's not just for a power platform anybody can pick this thing up and incorporate into their product and we have a couple of companies we're working within that reek so there's a lot of information there about what it is and how to think about it from a very abstract level then that we have a tremendous number of m v ps bloggers people that make videosorsol much so much community here that's here to help people and so i would definitely definitely look at for some of those those folks and the articles they've written about how to make the most of power effects and use it effective the empower apps primarily today but these other properties are coming and those can be really interesting to see what people do there too

[mark]: 25:30
i love it you mentioned then you've opened sourced it and that was one of things i wanted to touch on what what does that actually mean when it's open source is it that can somebody fork that

[greg_lindhorst]: 25:48
it's on get hub get com slash microslofslash power dash f x it's under the m i t license which is very liberal yes anyone can fork it and do whatever they want with it and that that's intentional on our part just like i talked about the leverage of excel and the power platform the more people using power effects learning power effects the more widespread that language becomes the more they can leverage knowledge from one power effects host and implementation to another and that's what we're going for as much leverage as possible

[mark]: 26:19
i like it is there anything i haven't asked you and i should have

[greg_lindhorst]: 26:22
oh i don't know i don't think so those are good questions

[mark]: 26:24
good good greg thanks so much for coming on the show

[greg_lindhorst]: 26:28
happy to be your guest thanks for having me