Excel to Power BI Migrations – Ep. 385
In this episode, Mike and Seth walk through what it really takes to migrate Excel-based reporting into Power BI without losing trust in the numbers. They share practical guidance on scoping, modeling, and rollout so your migration improves the experience instead of recreating spreadsheet chaos at scale.
News & Announcements
- Important Update coming to R and Python visuals in Power BI | Microsoft Power BI Blog | Microsoft Power BI — Update for R/Py visuals in PBI
Main Discussion
Start with Inventory, Not a Rewrite
The quickest way to derail a migration is treating every workbook as a 1:1 artifact that must be “rebuilt” in Power BI. Mike and Seth talk through a more durable approach: inventory the workbook ecosystem, identify what’s actually used, and separate “analysis” spreadsheets from “production reporting” spreadsheets.
Model the Business Once (Then Reuse It)
A migration is the opportunity to move from duplicated logic in dozens of files to a shared semantic model. The discussion centers on designing for reuse: consistent dimensions, shared definitions, and measures that behave the same across reports so users stop reconciling multiple versions of the truth.
Refresh, Distribution, and Security Are Part of the Migration
Excel often hides operational complexity (manual refreshes, emailed files, and local assumptions). Mike and Seth cover the practical considerations that become first-class requirements in Power BI: refresh schedules, data gateway considerations, row-level security, and where users will consume content (apps, workspaces, and shared datasets).
Adoption: Don’t Recreate Spreadsheet Chaos in a New UI
Even when the model is solid, migrations fail when the rollout is unmanaged. The episode highlights the importance of training, building a small set of “golden path” reports, and setting expectations for how business users request changes—so you don’t end up with a new stack of disconnected “Power BI spreadsheets.”
Looking Forward
If you’re planning an Excel-to-Power BI migration, focus on repeatable patterns: inventory what exists, standardize definitions, and build a shared semantic model early. As always, send us your ideas and questions for future episodes so we can dig into the scenarios you’re running into.
Episode Transcript
0:33 welcome back to the explicit measures podcast with Tommy Seth and Mike good morning everyone welcome Beth do it good morning and a happy Tuesday gentlemen There It There It Is Well I have some a little bit of bittersweet news here I guess it’s bittersweet this might be your last happy Tuesday you’ll hear on the podcast well I hope so I hope so because that no nobody should take that from me nobody should take
1:04 from me nobody should take that to want to snip out a couple audio clips here there’s some announcements coming things things are changing on the exposive measures podcast Seth we’ll give it over to you you have some announcements you’d like to make yes there is an announcement this is my last episode on the explicit measures podcast wow so at the end of this year a catalyst of change came about and I’m no longer part of powerb tips and contributing and owning to that brand bringing value to it was a main
1:35 brand bringing value to it was a main driver for motivation for the podcast for me and without that I have a chance to re-evaluate My Time Investments so it falls into a little bit of introspection but time is our most precious asset and I’ve spent over 10 years devoting a ton of that time to technology and powerbi it’s it’s been great it’s been rewarding but there are sacrifices that have to be made in other areas of life obviously yep and I think it just given me some
2:06 yep and I think it just given me some time to reflect and figure that I want to spend some more time devoting some time to those and give myself a break figure out what other areas I want to contribute to in the future future so for the time being I’m going to spend some more time on my faith my family fitness and friends I realize all of those are those are FS and we’ll see happens in the F future future with your yeah with my f excuse but as an intro I I do want to
2:36 as an intro I I do want to thank you guys for great conversations and being part of this experience it’s obviously been great and I want to thank our listeners and those that submitted questions and joined us in chat we’re part of the conversation you made this a much better experience for me and I enjoyed the journey that we had that all being said the explicit measures podcast lives on you guys are continuing on on next Thursday albe it without with a bit of a change a bit
3:06 it without with a bit of a change a bit of a change wow so Seth I just want to say thank you it’s been blast you you pretty much pulled me into the community space of this whole stuff anyways I have to give you mad props of you you you pulled me in as an MVP you pulled me into all these things inside the power B ecosystem it’s been a fantastic ride so I can’t I can’t thank you enough and also I’ve loved the conversations and the perspective you bring to the exposive measures podcast it’s been super fun we really appreciate all the time that you’ve hung out spent with us those early
3:37 hung out spent with us those early mornings those weeks of time where we had to record like a thousand episodes as we prepare for holidays and breaks and people traveling so I do want to appreciate all the the the hard work you put into this one and it’s been a thoroughly fun and enjoyable ride best wishes on your new adventures and where things go maybe our paths will cross again who knows I don’t know where things will will land but you you never know do I get do I get special guest access anytime I want to come back I I think so I think that’s been doing this for four years
4:07 that’s been doing this for four years after doing this for four years I’m pretty sure special guest access has always welcome this is you’ve you’ve done it for so long yes I I would easily admit to that on the podcast you get immediate special guest access if you have things when you go over to Tableau now and you’re the only person allowed to interview Seth I want to say too as shocking as this is for everyone everyone here this podcast you’ve challenged me
4:39 here this podcast you’ve challenged me you have not been afraid to argue and it’s really what I honestly love to do in in all my Endeavors and you really made me attempt to really expand my opinion my thoughts and my point of view on so many things in powerbi and not coming from a place of contention but truly okay you have that idea can you back it up and you’ve challenged me live you’ve challenged me offline but mostly live but it’s it’s always G be good fun
5:11 live but it’s it’s always G be good fun and Seth the way you have discussed pondered and gone through over 385 hours basically of conversation is really the heart of why we started the podcast in the first place again I’ll always say this the reason why were actually even doing this was we had a conversation just as friendly and we saw how we engaged but more importantly we really knew that there was nothing in our space
5:43 knew that there was nothing in our space about the gray area about the reality is always like well how to build a report how to do a visual all those other things that we’ve dealt with you’ve LED that through so much so I want to thank you I am sorely going to miss you because it’s always nice having three too because you can always gang up on someone and and sadly Tommy’s been pretty much the one that we gang up on the most usually that’s usually it feels like it has been in the podcast so at least it be somewhat 5050 now I need
6:13 at least it be somewhat 5050 now I need I need to find another kindred soul to help me gang up on Tommy that’s what I you better be ready to debate oh boy here we go so Seth I yeah I just want to thank you it’s been an honor it’s been awesome and like I said even if no one listened to this the last four years I would do over again it’s been fun it’s been definitely mark it up as part of the story right the experience of Life yep and I think with that we’re going to we’re going to end our our last episode
6:44 we’re going to end our our last episode talking talking about Excel yeah so so with that let’s get into Seth strong suit here is an area we so our main topic for the rest of this episode will be just kind rest of this episode will be just talking about migrating Excel of talking about migrating Excel reports to powerbi reports or doing like a migration from Excel to powerbi visualizations reporting whatever you want to call it and just the challenges that we face when we when we go through these migrations or when we’ve been given an Excel sheet we want
7:14 we’ve been given an Excel sheet we want to mirror it or build the same thing inside powerbi so why why migrate Excel the powerbi what are the challenges there that’ll be our topic for today but before we get into our main topic we do actually have one announcement Microsoft has been very much slowing down on the output of blogs or content or things because they take off for the holidays as well and so we typically don’t get a release or any new big information until mainly through January December and January are usually time things go quiet and then we get into February and we get another release and
7:45 February and we get another release and releases start kicking in again but Tommy you found a topic today for news there is a R and python visuals in powerbi important update coming to R and python visuals in powerbi you’re noting here yeah so we haven’t really seen a lot of updates from the Microsoft team about the python and R visuals it looks like they’re finally updating Python and R to be obviously what’s going on I think it’s going to be python 311 compared to
8:16 it’s going to be python 311 compared to what it was before which was just an old version another topic for another day but I would be interested if you been ever dabbled around with the python visuals or the Python scripts and if there are any many of you have it in your production report I have a few in my my sandboxing but nothing that I’ve actually released to a client or to Consumers so it’s interesting there yeah I I’ll also Tommy did finish off your point there as well they’re i’ I’ve played with the
8:48 well they’re i’ I’ve played with the r and python visuals because their interactions are very limited they don’t really do as much as what you need them to do related to interactivity or clicking on things and having data being cross filtered from the visual back into the report side of things I have found very limited use cases for R and python visuals it’s nice it’s handy you can write code to build a visual which is nice but other than just playing around with the feature I don’t really use them in production very much there’s not a lot of extra effort there if I’m trying to use or build a more elaborate visual I’m using things like chartic and
9:19 visual I’m using things like chartic and denb honestly to be frankly honest those go into production more frequently than I have been using R in Python but the fact that it exists and is there there are certain visualizations you may want to pull through another opportunity here as well with RN python visuals is in notebooks you can add visuals and you can actually add notebooks into organizational apps so if you do need visuals as a part of a story of data you
9:42 visuals as a part of a story of data you can incorporate them into notebooks as well and not necessarily a powerb report so it’ll be interesting to see where this R and python update is going to go looking forward to seeing the developments here U and I think you’re right Tommy it’s worthy to point out that some of the updates are coming if you are using them you’ll get some newer tools you’ll get the newer versions of code code to update and build some different visuals so anyways just want to point that out no other really major announcements coming out at this point so other than that let’s get into our main topic today we could we could spend time just going through all
10:12 could spend time just going through all of the libraries all the libraries that could be a fantastic end end episode riveting riveting python a tokens version version 2. 4. 1 certify version 20283 I can keep going is this is is this the best use of our time with the use of our time with Seth and his final final hours we have with us I don’t think so numai now updated to 2. I didn’t see any gluten in here so I saw
10:42 didn’t see any gluten in here so I saw blue I it’s a python library but it’s R anyways good good information I just put the the link of that in the in the it’s actually in the description but it’s also in the chat window as well if you want to go check that out there’s more documentation there on the Microsoft website around that update as well so be interesting to see what the community does to be frankly honest I have not seen the community or when I have not seen the community or when Community I scan across Reddit I mean Community I scan across Reddit I scan across LinkedIn I hang out on Twitter those are the three places
11:12 Twitter those are the three places that I play and watch the community I don’t see many people building visuals in r in Python so if you’re if you’re just like oh interesting should I do this I I don’t think you should spend a ton of time on this unless you have a very specific use case that being said let’s move over to our main topic Tommy want to give us a little run up here Tommy what’s what are you thinking for this topic we’re talking Excel we’re talking migrating things from Excel into powerbi reporting or models Let’s let’s unpack this oh yes I think we need to
11:42 unpack this oh yes I think we need to give this a little more love and I don’t know if you guys have ever been in a situation where there was a Excel report and we’ll call a report in the most liberal sense but the data was structured cleaned and formatted nicely and styled in an Excel file and it looked nice there some tables maybe a few visuals but really it’s however it’s getting the data into that final version that’s what a team or Department uses or relies on to see what their numbers are
12:15 relies on to see what their numbers are I’ve seen this for sales teams to see the quote I’ve seen this for product inventory it’s always updated once a day and the configuration behind this is not going to be the the conversation today but usually it’s macros and a lot of formulas and a lot of additional Excel sheets a lot of advanced usage to get that final cleaned up Excel Vision a lot of times when organizations are really trying to make that data
12:45 are really trying to make that data culture change they want to move obviously off those Excel files it’s tedious takes a lot of time but the problem is Excel migrations to powerbi reports as seamless or as easy as that would sound in practice man what a headache there are a so many gotas that you may have to deal with besides automation but one of
13:16 deal with besides automation but one of the big things for me is the consumers or the audien’s expectations what powerbi can do compared to what they can do in Excel and then changing their mindsets so anytime you I kind want just start there lay the groundwork have you guys ever done this what where do you feel Excel is now well Excel is a official database for Microsoft so let’s start there right it is a full-blown database you can use it to do is totally sarcasm at
13:48 can use it to do is totally sarcasm at this point this is not true Excel should not be a database but to your point Tommy I have worked on many of projects where Excel is acting as if it is a pure datab base it is all the things all the data comes loaded in I’ve even had customers build a whole bunch of access databases that pull from a SQL Server aggregate data down in order to get it loaded into Excel documents so SQL Server to access access to excel bring in lots of
14:19 access access to excel bring in lots of tables and both build full reporting off of that because the tooling that they had in at their disposal did not allow them to have lots of Records so yes I have definitely dealt with these project and I’ll I’ll maybe highlight a couple areas where I think there’s some friction here and I think Tommy to your point towards the end there was there is a m shift that needs to happen between what I’m thinking about in Excel and then what I’m trying to do in powerbi Excel lets you build tables and cell level in information on top of your
14:49 cell level in information on top of your your Excel elements when you’re in powerbi we’re actually talking about like modeling the data so you need all so you need all the data to be loaded and then once the data is there then you can manipulate shape adjust and refine that data another thing that I think is interesting that changes in people’s minds here a little bit is the volume of data can be very different in Excel we have limitations around how much volumes of data you want to bring into the Excel sheets themselves because the calculations run slower I’ve done one
15:19 calculations run slower I’ve done one sheet where we had 250, 000 rows of of data of physical data but then we had like 13 Columns of calculations well if you do 13 time 250, 000 rows you’re getting millions of calculations that need to run that are interdependent on each other and if you don’t build your Excel sheet correctly it will take hours for it to recompute all the information that it needs that doesn’t necessarily get the same effect that you have in powerbi so you can pre-calculate a lot more things or in the memory of the computer which is highly faster it’s
15:50 the computer which is highly faster it’s much faster than doing it in Excel however it comes with its own set of unique challenges so I think I think those are my my two talking points here there’s probably a lot of other things here that Excel uses other data points or other things here as well so I’ll just leave it there Seth maybe any any interactions you’ve had around working with Excel or bringing Excel users into the powerbi world what challenges have you faced or OBS observations have you seen there yeah I I think there’s two aspects of it and Tommy you said you
16:21 aspects of it and Tommy you said you didn’t want to touch on it but I think it’s it’s a major part of migrating folks from Excel into powerbi when when when feasible when it’s a good fit the first front end is is sometime well they’re they’re both the hardest right because it’s the difference between saying that there are different data sets that you’re direct data access to individuals of seeing like the raw data dumps and then they do something
16:51 dumps and then they do something versus what you’re trying to do with aggregated visualizations is just get the Insight like I want to look at something and have the answer as opposed to get the data then have to go pivot or then have to go do my own things or then have to do my analysis on a br byro level and I think that’s where powerbi increases the value to organizations because by having those conversations folks don’t need to look at the raw data all the time but that’s such a a challenge sometimes in and of
17:21 such a a challenge sometimes in and of itself when folks have access to Raw data for for some reason what’s what’s interesting to me is I think if you’re not familiar with looking at reports or building visualizations out of your data it’s a very uncomfortable shift for some people to not have access to the raw data AG because because it it’s a thought process to get to a visualization right I want to look at my data and do these
17:51 I want to look at my data and do these things with it I want this information out of this large data set and it I don’t think people have had enough of that experience in like seeing the impacts of looking across the year worth of data and seeing a line change as opposed to trying to like sus It Out by themselves by looking at all the raw raw data points so I think like first and foremost that that is one of the the big shifts in
18:21 big shifts in conversation that before you go like all in on a migration out of cell because I I think the easiest part to do is just integrate with the data that’s coming out of the fully formed product that they’re producing and start to bring forward some of the insights right like take the take the data set that they built in Excel part one teach them that like hey here’s after talking with you like hey here’s after talking with these folks everybody’s pivoting
18:52 know these folks everybody’s pivoting things in this way here’s the insights that you’re trying to drive here’s here’s what we want to do from a report visual ation standpoint and that doesn’t mean that like we can’t do tables and matrices Etc right it’s what what are the enhancements you can bring and sell them on the enhancements before you go like first and fix all the data engineering stuff which is where I think most of the value comes when you’re going down these projects I like your points here and I I I’m taking some notes Here on my side on
19:22 I’m taking some notes Here on my side on my screen here just to point out a couple things I think you’re highlighting Seth if I had to articulate the design path of reporting or visuals or things in Excel I think it would go to I think what you’re were speaking to Seth the design path is see raw data dumps look at the data inside Excel sheets figure out what’s there shape the data in Excel deleting rows cleaning data fixing things right and then then start focusing on what information can be aggregated into a
19:52 information can be aggregated into a visual an aggregation of data I think to your point we skip a lot of that be the raw data portion and do that experience of like shaping the data I think power query serves a lot of that and I think another aspect of this that that I I think is underutilized in general many Excel users don’t understand powerquery exists it’s it’s there it’s an automation tool that helps you shape and clean data that seeing the raw data dump getting the data in and then being able to shape and clean that data for Preparation in whatever the report
20:23 Preparation in whatever the report portion is I think it’s it’s not used enough it’s it’s it’s an unknown feature and that’s one of the things I like to explain when I’m doing things in powerbi when we show them people loading data sets in look this is the same power query engine that we have in Excel and actually I’ll go do a demo of watch me build this load of data using Excel I’ll load data in using Excel and I’ll do the one button press import that same power query experience directly into powerbi so like look if you just start working in power query initially when you load
20:53 in power query initially when you load your data one it’ll go much faster two you can load a lot more data but then once you’re done with this experience if you like what you’ve done here and you want to migrate over to powerbi it’s literally one button and it takes all the powerquery language and migrates it for you which is people love that they’re like oh wow so it’s a lot easier than I was thinking anyways some thoughts thoughts there so from the data integration point it is not necessarily terrible to get the data in but have what I have always
21:23 the data in but have what I have always run into is these Excel projects that we’re we’re trying to migrate it’s the manual side of this that is more than just yeah that pivot tables and power query importing that to powerbi is great but a lot of these ex Legacy Excel reports are based on so many manual processes with individual knowledge on what has to happen at certain times that you run into where that report can only be done in the current process but if it
21:56 be done in the current process but if it was manual not necessarily you was manual not necessarily just getting the data in we’re know just getting the data in we’re going to turn a table into a visual the biggest problem I find is how many hands touch the Excel data before it gets to that final form and this happens on a like a weekly or monthly B Cadence and it’s amazing how much time spent but it’s all this manual configuration no we’re going to adjust this I’m going to edit the cell because this cell sets off a bunch of
22:26 this cell sets off a bunch of other numbers that dare I say in that form can’t be done in powerbi powerbi is databases semantic models structure tables Excel can take anything it doesn’t have to be a log of csvs a lot too many times the first phase of those Excel reports is someone going to the input Excel table where they’re just inputting the aggregate
22:56 they’re just inputting the aggregate numbers into not in a what we would think is columns and rows but B1 and C1 basically that is very hard for us to convert the hardest thing that I found was always where did that data come from Seth you have the floor yeah but I think I think what you’re what the points you’re making are why in the past like or why organizations go down this route of
23:27 organizations go down this route of moving away from it L it it’s it and be- because of of the first points you’re making you’re producing a data output that you don’t you have unknown inputs you have unknown manual interventions you don’t know how like this data set is getting put together and I think fundamentally that’s one of the the the drivers for bi teams to be be be or get
23:59 drivers for bi teams to be be be or get engaged with business units because if you’re talking about all the things we talk about all the time Tommy like trust in your data where does it come from what are the things like that’s why I think teams go in and say okay we we want to automate this process we want to make sure that the data inputs that we’re getting that we’re driving business decisions on are not predicated on somebody manually putting together a file that also by the way takes lot of time to do and I think that’s one of the
24:31 time to do and I think that’s one of the big ticks here as well is the the speed to delivery for report right many like more often than not and it it happens to be more of the complex implementations that I’ve dealt with in the past with Excel is you’re in there with bringing some some different levels of process Improvement because a team takes two to three weeks to put together the data for a report and if we can come in and say hey we we’re pretty sure we can turn
25:03 hey we we’re pretty sure we can turn this around in a couple days most or it’s refreshed automatically every you it’s refreshed automatically every whenever you want it or at the end know whenever you want it or at the end of the month or at the end of the quarter whatever you don’t have to wait because in a lot of those time times you’re talking about financial data you’re talking about how fast we can make decisions based on the results of sales or our financial you results of sales or our financial aspects of things and I think know aspects of things and I think that’s the driver for why you get into this but what is like when you dive into these projects it’s all of these things
25:33 these projects it’s all of these things that I think in general business folks aren’t thinking about their data in the same way that we are right and Tommy a lot of the things that you’re saying that are challenges to automation are because people don’t think about automating their workflows or their data flows in the way that they should be so very often like 90 plus perc of every everything that’s happening in an Excel file can be a power query step it can be
26:03 file can be a power query step it can be some trans transformation shaping Etc and in the cases where you need some manual input like in my experience that’s always been able to be something that’s a a one-off table or a configuration table or something else that drives impacts to other things automating and getting all of this to a place where we can one create a repeatable process something that can scale that we can
26:36 something that can scale that we can automate and others can use I think is why you drive into these migrations from an Excel Excel into powerbi for cases that make sense but it is an investment because it’s not like I think the misconception here is all of this data engineering and process is is the timec consuming part of it it’s it’s understanding all of the things that are happening that are both
27:06 things that are happening that are both manual and built into these manual built into the Excel documents is is sometimes extremely challenging to like dive all the way deep into and and then rep platform on power query right and and to your previous point and then I’ll shut up is it it it has always astounded me that that powerquery was in Excel for forever and it I think it just proves the point that most business folks don’t think in that way because if they had
27:38 think in that way because if they had they’d be looking at Excel going how can I automate by processes right it’s the cell byell problem that I think we’re trying to solve with rep platforming as well as giving better insights to business users I think another aspect of that so Seth I love all these points and I think I’m going to add to some of these points of things that I’ve observed as well I think another challenge that you’re you’re speaking to Seth or talking a little bit around the periphery of is like call me to your point a second ago the ReUse of the
28:09 point a second ago the ReUse of the Excel output is extremely challenging right so let’s say I get to a place where I have a pivot table I get to a place where I’ve shaped or processed the data what then happens to that output like how do you share that Excel document with other teams what is where does that output go to visualize or actually become an output of that element you share the Excel file it gets emailed around it gets stuck on a share drive it like it becomes like this physical thing that is now then handed around the company and when it does that
28:39 around the company and when it does that that there’s a high risk of is where you get the the the final final underscore final V1 V2 thing going like joke that comes from because that’s you you don’t want to break the existing Excel sheet because it’s so complicated already but I need a new one and at some point you run out of memory limits Excel has actual limitations in it that like things slow so I can’t tell you the number of projects I’ve been pulled into they’re like hey can you help us build a
29:03 they’re like hey can you help us build a better Excel thing we’re doing this really complex data transformation and and if you think about like the company the the company’s lens of data the data lens is large it’s it’s millions of Records across hundreds of customers or thousands of customers the data volume could be immensely large on these companies but they’re not putting themselves in a place where powerbi or now even at this point fabric can help them so what happens is instead of looking at all the
29:33 happens is instead of looking at all the customers data we build an Excel document that has like a flag or a switch or some input that lets you change from customer one to customer two to customer three so I can then load so now I have I take my ex my one Mega big Excel document instead of making a single model of the data what are my inputs what are my outputs stepping back and saying what is the process of like what is the the methodology I’m I’m using to build this data process it now turns into this well I can I can only use this Excel file for this particular customer and so now you
30:03 this particular customer and so now you start building these smaller Excel files that are manageable have reasonable amounts of speed and I’ll use this as another analogy here like the refreshes inside Excel typically take much longer than what you’d be building in powerbi because you spend the extra time automating how do you get the data in in a regular fashion Seth Point earlier I have a lot of thoughts on this is there’s so many thoughts here the inputs of data so when you want to have an input screen powerbi is not good
30:33 have an input screen powerbi is not good at that honestly frankly like in Excel you can have multiple Excel input fields that then change how the calculations work on subsequent Pages you can do that in powerbi but it’s just not as easy there’s no input text box there’s no input number box in Excel where you can easily type in a number and just have information appear and it just works throughout the rest of the rest of the powerbi report that’s just not a function of what they’re doing inside RBI reporting it’s not there so I think there’s there’s all
31:04 not there so I think there’s there’s all these little aspects in things and in my observation of excel the more complex your Excel document becomes the more we try and solve those loow things that are slowing down we do more things with macros so macros now start showing up in Excel to then pick up tables of data shape it bring it and put it back down like all these macro hey this Excel Sheet’s really slow okay well someone in our team built in a macro that will now pick up all the data reprocess it and then put it down somewhere else so that’s again to your point Seth it’s a
31:35 that’s again to your point Seth it’s a Excel buries a lot of business logic into the Excel sheets or or workbook that then gets very hard to extract and give to another team member or automate in a large fashion my opinion is a lot of what Excel is doing is dis discovering what to do with the data right I don’t want to ever poo poo the idea of like saying hey we’re getting rid of your that’s not what I’m trying to say here all I’m trying to say is Excel has its limits like I’d rather have you in Excel figure out how does the data come in what are
32:06 out how does the data come in what are your inputs how do you shape the data what should be the output of the data right that’s great to use in Excel but do that on a very small window of data and then once you understand it let’s talk about how we rebuild that process in an an equivalent powerbi feature and that will likely increase the number of Records to a higher level millions or tens of millions of Records or more you can handle a lot more in bck and and powerbi so I think there’s this idea of like scale that comes with this as well
32:36 like scale that comes with this as well back to You Tommy so obviously I think we’re all in agreement in terms of powerbi is better for automation great I want to slightly shift to where that other disillusionment is with powerbi reports especially for those heavy Exel teams what honestly truly is the hardest part of the Excel migration is the interactivity or ability to interact
33:06 interactivity or ability to interact with the Excel file for the consumer that they cannot do in powerb or to try to get the same view I believe our good friend Kurt berer had an article about this once where never taking Excel basically report and try to copy paste that into powerbi from a visual point of view it just can’t be and I think that’s one of the hardest things because powerbi is great for obviously the analytics side of things
33:36 obviously the analytics side of things but what people are probably used to in Excel and what teams are used to in Excel is maybe I can quickly update a number I click on these three buttons it run some macro or some automation so I can get something shown a different way and powerbi is pretty pretty set in his ways and a lot of things we do is the visualization let me ask before I I I give my opinion here but when you find teams who are heavy and dependent on
34:08 teams who are heavy and dependent on those Excel like tables and reports do you find them H find it hard to help communicate to them or really move them towards a powerbi visual point of view rather than just creating a powerbi report with just a bunch of tables trying to replicate what they had in in Excel let me let me before before I want to down that like that seems like a very specific question I want to generalize your question maybe a little bit here and maybe step back a little
34:38 bit here and maybe step back a little bit and look more high level at what we’re talking about here I if I had to think about the things that are occurring inside Excel there’s maybe four there’s probably more areas but I’m thinking just in this conversation just now I’m pulling these out of my head four areas we should think about right there’s one around like data engineering right how do I shape the data what’s what shape of the data does it transform from raw form to what I can start using it so there’s a data engineering aspect of this that comes in Excel and powerbi there’s this idea of Automation in both tools right Excel can automate things
35:09 tools right Excel can automate things RBI can automate things what does that look like for you and then there’s the I think what you’re talking here next Tommy is the uiux experience what does the user interface look like how am I presenting the data in front of those users either in table form or in visual form there’s a an experience there is even inputs right so what input Fields am I using to shape and mod that data and then the last part of this I’ll point out is there’s another aspect of this world that’s sharing how do you share that information right and I think you could grade both powerbi and Excel in all of these areas and I I would
35:40 in all of these areas and I I would argue that the data engineering side of things is much richer inside power query and inside Fabric and I’m going to pull fabric into this too because I think we’re not even though we’re not talking powerbi and other things but I think fabric adds a lot of data engineering capabilities that automation of things that really make this extremely powerful for us so that one is very relevant there the reason I’m bring this up is because there’s more and more systems are becoming like apid
36:12 and more systems are becoming like apid driven I have to go talk to this API and get data out of it I’m seeing more and more use cases where this is occurring cell’s not good talking to apis and getting Json files out and shaping them unless you’re going to go right into Power query so I think I think you could grade the data engineering part like that to me that’s a win for fabric and powerbi talk about the automation yes Excel has Automation in it but I think a lot of that business logic gets landed in macros cell Excel cells and I’ve been in Excel sheets
36:44 cells and I’ve been in Excel sheets where the formula for a single Excel cell is like 40 lines long 100 lines long in that one cell and I’m thinking to myself this is unsus like when I just look at it if you have hundreds of cells across different sheetss that are like many many lines deep and using lots of functions in there to do very compli awesome but who’s going to maintain that only the person who created it can understand and know what that’s doing or if you’re going to give it to somebody else they’re going to need a lot of time to understand what was built there so
37:14 to understand what was built there so they can utilize it it’s not as straightforward so I think the automation side yes it’s okay but I also would give another win to powerbi in that space uiux I think if you’re going to grade RBI and Xcel in that space I’m going to give the win to Excel for like input parameters or inputting data and having that incorporated into your analysis I think Excel does better there I think Excel also does better in table views of views of things that’s the golden standard for table building my opinion why powerbi
37:46 table building my opinion why powerbi hasn’t been upgraded to match the same level of capabilities and Excel at the table level that’s beyond me another challenge I have found around the uiux side of things is in your tables when you want very specific cell byell formatting like this cell number turns into this number therefore it should be this particular formatting or coloring the formatting and conditioning of the table information is way richer inside Excel than it is in powerbi so that’s another win I think in my in my base for for Excel where I give powerbi the length or
38:18 Excel where I give powerbi the length or the the leg here is when you’re talking about visuals bar charts other charts line charts and the interactivity between data in Excel it’s very difficult to get a chart to click on data in one chart and have another chart change and show the data dynamically there so the The ux uiux Experience like when I look at powerbi I think of I’m building data products as opposed to excel I’m building like a table or I’m
38:44 excel I’m building like a table or I’m building like a very finite analysis and then the last part I’ll just give is the sharing part I give that a huge win to powerbi so sharing is much easier in powerbi it’s designed to build around data products that you’re going to produce it has org apps it has regular apps like all these other things are going to make it easier for you to distribute that content out to other people so that’s maybe I don’t know maybe I took your question a bit of the wrong way Tommy here but I think I think grading the tools in those areas they
39:15 grading the tools in those areas they definitely have their strengths but I think more of the strengths lend itself to moving more towards powerbi yeah I I think I’d answer in with a a smaller Subs out of that where Tommy I think to to me your question delves into one of the hardest challenges around going from Excel to powerbi right which is you’re taking end users out of a space where they’ve learned overtime how to change data
39:45 learned overtime how to change data through manual work or some Excel automation that they can like trigger right so they get a raw data set they they need to analyze and manipulate it but if if end user users don’t know the data engineering steps these these aren’t meaningful to them they just know the data right they just know that like they’re going to do this thing they’ve developed some formulas to transfer it and then in advanced scenarios they start getting into macros or some some sorts of automation but they need to be
40:16 sorts of automation but they need to be shown and I think it as much as I say like hey like show the insights that can come out of those results in the data sets I think the biggest selling point that I’ve seen two teams of people is saying hey you guys this takes you three weeks to do it’s not a criticism that it takes you three weeks to do we’re not saying that you’re not you’re not doing good work there but let’s let’s break down what it is you’re doing and then show them the value of
40:49 doing and then show them the value of what would happen if you go down the paths of saying we know we can automate 90% of your process what that automation means is like we set this up and something else triggers a Refresh on a on a Cadence you don’t have to touch it right and that back and forth between that automation versus in some cases there still needs to be manual input right there NE they there may still be some analysis that has to be
41:20 still be some analysis that has to be done that Excel is great at because they’re cell by cell row by row you can change and manipulate data there could be a base level set of data needs to go into the engine right but if an end user goes from a 3-we process to hey I just got my raw data dump file I’m going to do these CH I’m going to do my analysis on it and then I’m going to play save my file to this location I know that tomorrow it’s going to be up and ready for the for the organization and I think across all of those cases the there there’s
41:53 of those cases the there there’s always been a a huge Drive once people know the direction they’re going to get on board with it right and and you need them on board because not only are you changing their process from like super manual to automation you’re you’re forcing power query and a new engine and a new way of doing things so they have to learn and adopt that and they’re not going to learn and adopt it unless you show them the business value to them the
42:24 show them the business value to them the tradeoff there is a like why you’re doing this and pushing way of fear that comes with that is when you’ve got folks that have like spend all their time in Excel you you’ve got to interject this this thought that your manual data manipulation that you do over and over and over is not value
42:54 do over and over and over is not value to the business the value is your analysis or the value is in you doing more types of analysis that you don’t have time for right now and can’t produce and that’s why we’re going down this path of trying to automate your workflow it’s not automating you you’re not at risk right typically they’re they’re not so it’s it’s what is the value to them in Automation and then show them the other things that they could do which are on their backlog and
43:26 could do which are on their backlog and a lot of times that’s in conversation like hey you’re doing all these things what are you not getting to in a day what are the things that the business keeps asking you to do that that you just don’t have enough time for those are the things that you need to bring along for the ride because that that pushes aside this like oh Tommy’s just trying to replace me because this is what I do for three weeks out of every month right like I take these files and I have this really long verbose process and then I have to do some analysis and things like this right so there there a people part of
43:58 right so there there a people part of this as well but it it’s I think the there’s the building blocks for what you’re able to start to teach business users are exactly where you would want to go with data culture initiatives or how you would engage with bi teams in business teams these are some of the best areas for businesses to invest time because it builds efficiencies it it it starts to train teams how to look at their data sets ask the right questions and then drive away
44:29 the right questions and then drive away from all the manual work right I’m not suggesting that Excel doesn’t have its place because it absolutely does it has it will like it it’s the go-to for quick analysis for updating data for modifi like getting your head around things which you would want to do before you’re going to go build a a powerbi report for insights and all this other stuff first anyway it’s just H like teaching users that that’s not the end Place anymore right it’s just a data input if need be or when they’re
45:00 input if need be or when they’re thinking about bringing this new insight that they did in an ad hoc analysis fashion through Excel the next PATH is great I’ve done all this stuff now I know how to get it to this next level where I can automate it I can have this report just available for everybody and I’m shedding this responsibility off on to systems that are designed to support it and my impact is exponential right and this is why I think in this microcosm of excel to
45:31 I think in this microcosm of excel to powerbi people have found immense success in powerbi being the thing in organizations because it’s a magnet it’s a it’s a multiplier right if I’m so good at analysis in Excel I can I can magnify my value to the business by learning these automation steps in my process in powerbi because I’m automatically delivering these insights without manual intervention so all everything I know about my business area
46:02 everything I know about my business area gets bigger and better and people see that and that’s why you like Technologies explode because they’re they’re they’re multipliers I like that point I like your point around the it’s in the the value is in the analysis it’s the thinking critically about the data and and getting out of it what you want to make decisions on later on in the business that’s the value ad it’s not the I’m removing this column scrubbing out this data I’m I’m replacing these
46:32 out this data I’m I’m replacing these values like that shouldn’t be a thing anymore like that that that’s such a low hanging fruit it’s like an automatable thing yeah I like that a lot that’s a great question to ask sorry back to your question Tommy I know you were pushing on people like maybe rephrase your question does this change your question at all in these after these two points honestly what what SE said and again I’m G to miss that 45 minute gold nugget every single time too so but honestly true what was said is
47:02 but honestly true what was said is really the hardest thing to do because it is so much about the culture too and the people you’re dealing with Seth to your point where a lot of times I’ve gone to those Excel projects and the first thing we do before we even look at the data before we even say what the end goal is is I work with the people have to input the data and are all part of that journey and really get a cost of time on what they’re actually doing like so how much time do you spend on this on a given or a given month and what we
47:34 on a given or a given month and what we really do from there is like even if you don’t like the report we found out that just this team of five people alone will save them x amount of hours it’s gonna allow the finance person to actually do finance things so to speak so it’s really that’s one of the first major steps not to your point my twoo where Excel does have its benefits but you don’t come in guns of Blazing power be is so much better for everything so no we’re looking at all the things we do and really my first mode of attack or
48:06 and really my first mode of attack or or first weapon is really those time involved and then actually for the probably for the first time outlining to leadership or who I’m working with that step-by-step process that has to happen and how many hands it changes and that’s what we want to eliminate but at the
48:26 what we want to eliminate but at the same time and you still need the culture and the right people right because people will be stuck in their ways and not even the people who are just building that Excel report every month or on a recurring basis but the people who like to look at it and know where it is and knows what they can manipulate so I would always go against when going into a project like this like we’re just going to replicate your tables and will have an powerbi No One’s
48:58 tables and will have an powerbi No One’s Gonna be happy that way agree and I think that’s such a Mis so misunderstood it’s like no what are you actually trying to do with this Excel report like I know it has 10 rows by 20 columns split up with all these numbers but are you looking at all those all the time like what are we really trying to do here just because yeah when you’re trying to compete with Excel from a cell and you compete with Excel from a cell and specific point of view not going to know specific point of view not going to work work but that first approach is
49:30 work work but that first approach is is really a culture change it’s more than the technology with Excel then you can deal with the headaches of where’ you get that number from who put that in where’d he get that number from yeah how did he come up with that we can deal with that later but we have to first just simply sell how much time and really what we’re going to do from the benefit not just hey it’s G to be pretty visuals and it’s automated I think that’s really what makes or break that those Excel projects in my in my past
50:00 those Excel projects in my in my past experiences what have what has typically happened here is when you get to that very complex Excel thing what happens is you have one or two or a handful of really skilled people on the team that are building and maintaining that Excel document and if those people don’t do their job around that Excel document you lose the ability for the business to leverage that information over and over again so it really yes Excel is extremely powerful it can do a lot of things but it really Narrows down the
50:30 things but it really Narrows down the knowledge base down to a very small group of people who know how to maintain and utilize that Excel document I did put in the chat window here the article from Kurt data goblins around report requirements one thing that I have found that works for me as a technique here is that works very successfully is stepping back and saying Help Me Define the inputs what input things do you require to make the output correct so once you do the experiment Excel get the Excel document thing figured out and completed fine but to your point Seth
51:01 completed fine but to your point Seth let’s step back and say what is the real value that we’re producing throughout this sheet or Excel document what Define what are the inputs these are the areas where I’m injecting information these are the sources of data and then you step back and say what of those can be automated is there anything in those places where we can automate that information so it becomes regularly loaded into the Excel document another Point around this one is a lot of times I see Excel documents looking at a very small window of data across an organization or a very summarized
51:31 organization or a very summarized version of data when you get to powerbi I think you can add more data to the scope of what you’re looking at so I also think there’s a there’s a volume scale here as well that shifts your mindset away from users just thinking about their customer their one little scenario and things you had to do cheats you had to take around the data previously you don’t necessarily have to do anymore there is a better way of absorbing more data into one analysis and you can actually report across more so I use the analogy a lot of
52:03 so I use the analogy a lot of times when we look at these things a lot of people aren’t thinking critically of the data and saying do I need all the data to show up or do I really just need I I think of excel Andor the data model as one really large table well the entirety of the table isn’t relevant all the time what you’re looking at is for where the performance of those couple customers impacted your bottom line you’re looking for the the edge cases of the data where does the data not fit your expectations that’s a lot of what we’re doing in our analysis is trying to figure out what
52:34 analysis is trying to figure out what are those groupings or what is The Logical grouping of data points that get us to the number like wow I wasn’t expecting that number to be so low in revenue for these six customers I was expecting to be much higher then you dig in and go figure out what happened did we lose a customer did we lose some sales did we give a price increase and the customer didn’t like it and bought somewhere else like all these other kind somewhere else like all these other questions get spurred from of questions get spurred from these these aggregations of data so really I can’t I can’t in express
53:04 I can’t I can’t in express enough using budget numbers and actual numbers is so important here and I think that’s a lot of what we need to get our culture around in our organizations is thinking more about that way anyways yeah about time final thoughts Seth I was it’s all about Roi right and I think there are two ways you can help with selling and Excel the power behind migration where where it makes sense as well as like prove the points the first being insights right the the fastest
53:35 being insights right the the fastest win is hey we’ve got all this data I’ve talked to the teams that use it here’s the insights we can just show you on a page right and you can instantly get from visualizations as opposed to all of the raw data and that that is the first thing you can go build off of the Excel file right is here’s the new world and why we want to go down this path once once you have that it’s it’s a much easier path towards saying the the secondary part of this that’s going to
54:05 secondary part of this that’s going to take longer is in the automation of it but it’s worth it because more often than not what you’re saying is you get this data refreshed faster than it was in the past meaning like these are probably reports and the data that is coming out of it that’s extremely important for the business to make decisions on which is why they’re going to invest a bunch of time in creating efficiencies but in that automation process with business users essentially the feedback to the business should be yes we’re building an efficiency but
54:36 yes we’re building an efficiency but we’re also building future efficiencies because what you should be doing is training the teams and saying to the business we’re teaching these teams how to not put us in this situation again and they’re going to aut be able to automate their Solutions they’re going to be able to create more value for the business and and that’s what you’re doing along side of just this oneoff process where it’s like I’m going to take six weeks to work with a team to save you three weeks per month so like that tradeoff or you
55:08 per month so like that tradeoff or you get equilibrium after two or three months yes but in the interim you should be seeing the results of this work in other areas in other more productive things that those teams are doing and that’s how the tide raises All Ships when you go through and can spend time with these teams and train them how to use these new tools because it’s it’s an accelerator for them and it’s a value add to the business I like it Tom any final thoughts as you wrap here
55:39 here honestly Seth my final thought is I’m GNA miss you and I’m gonna have a lot of thoughts about you in the coming weeks and weeks and months honest that’s all I got I made tummy speechless I’m a little I’m a little I’m I’m pretty bummed about this knowing this coming but Mike what we should do with all this AI stuff is we should get all the transcripts from all of our podcasts oh boy AI Seth just SE AI Seth AI we’re we’re gonna train it
56:10 SE AI Seth AI we’re we’re gonna train it we’ll have to we’ll have to get a a Seth AI prompted on all the the the thoughts and ways of Seth trained into model need to talk to an IP L [Laughter] here yeah it’s funny you always agree with Mike’s points nowadays what’s going on awesome well we want to say thank you very much Seth for your all your time on the podcast it’s been it’s been a pleasure and an enjoyment just always talking with you and hanging out and discussing things you always bring great insights to the table we really
56:40 insights to the table we really appreciate that as well best of luck on your new adventures on things and you’re always welcome back on the podcast as a Perpetual Always On guest if you’d ever lean to come back so we we really appreciate your time best of luck on your your new adventures that being said we do appreciate everyone of your listeners out here you guys are are awesome a great community of people engaging adding a lot of conversation in the chat so thank you chat very much today for also participating at your thoughts and hearing how this may impact you as well
57:10 hearing how this may impact you as well with that being said we really do appreciate you and the podcast we would love it if you would let somebody else know you are enjoying it go listen to it on your daily run or whatever other medium you like to use our podcast on we appreciate it and we thank you very much for your time Tommy where else can you find the podcast you can find us on Apple Spotify or wherever you get your podcast make sure to subscribe and leave a rating it helps out a ton do you have a friend or do you have a question idea or a topic that you want us to talk about I feel like we’re gonna see a lot more mailbags coming in
57:41 gonna see a lot more mailbags coming in soon and they’re gonna have the same tone well you can submit it on power. podcast leave your name and a great question and finally join us live every Tuesday and Thursday 7: 30 a. m. Central and join the conversation all of powerbi tips social media channels thank you all very much and we’ll see you on Thursday Thursday [Music]
Thank You
Want to catch us live? Join every Tuesday and Thursday at 7:30 AM Central on YouTube and LinkedIn.
Got a question? Head to powerbi.tips/empodcast and submit your topic ideas.
Listen on Spotify, Apple Podcasts, or wherever you get your podcasts.