Max Performance in Power Query – Ep. 422

Mike, Tommy, and special guest Alex Powers (the engineer who literally writes the Power Query documentation at Microsoft) unpack how to get the most out of Power Query across all its hosts—Excel, Power BI Desktop, and Dataflows Gen 2 in Fabric. The conversation spans staging defaults, V-Order optimization trade-offs, query folding strategies, and why treating Gen 2 like Gen 1 is the fastest way to blow up your capacity bill.

Beat from the Street: Keeping Up with Fabric Updates

Before diving into the main topic, Mike and Tommy discuss their strategies for staying on top of the relentless pace of Fabric updates—April’s release alone was 65 printed pages. Both use personal projects as testing grounds for new features, prioritizing what removes friction from real client work over shiny nice-to-haves.

Key strategies:

• Build internal projects around each new feature to get hands-on runtime before recommending to clients
• Prioritize friction-removers — Features like materialized views and copy job improvements that fix existing pain points get priority over nice-to-haves like real-time analytics
• Think event-driven, not real-time — Most businesses run on-demand; data pipelines should be event-driven rather than constantly streaming
• The “Conductor’s Era” — Tommy shares an article arguing we’re shifting from building to delegating, with AI agents and multiple models orchestrated together rather than one tool doing everything

Main Discussion

Guest: Alex Powers

Alex Powers joins the show—he authored the Microsoft documentation on Dataflows Gen 1 to Gen 2 migration and the scenarios docs. He’s deeply embedded in the Power Query engine team and came to, in his words, “brainwash everyone” about rethinking their Power Query habits.

Power Query Lives Everywhere

Alex reminds everyone that Power Query isn’t just in Power BI Desktop. It’s hosted in Excel, Azure Analysis Services, Power Apps, Microsoft Teams, and even when you paste tabular data into the service. With 30 million monthly active Power BI users, patterns that worked locally in Excel or Desktop don’t automatically translate to cloud efficiency.

The Gen 1 vs. Gen 2 Paradigm Shift

The critical insight: Dataflows Gen 2 is not Gen 1 with a new coat of paint. The UI looks identical, but the execution engine is fundamentally different:

• Gen 1 = ETL tool that outputs CSV files, can attach to storage accounts, became a consumable item (and people wrongly treated it as a database)
• Gen 2 = ELT tool that writes V-Ordered Parquet files to Lakehouses/Warehouses with a completely different architecture

Alex’s plea: stop copy-pasting Gen 1 M code into Gen 2 and expecting identical performance. The generations are not the same under the hood.

Staging: On by Default, But Should It Be?

Staging is enabled by default in Dataflows Gen 2, which lands your data in a behind-the-scenes Lakehouse before transformations occur. Key nuances:

• Small files — Staging auto-disables with a yellow banner when file volume is too low
• Warehouse destinations — Staging is required (no choice) because the engine needs bulk operations, not single-row inserts
• The CU cost — Every additional step costs capacity units; users should evaluate whether staging is necessary for their scenario
• Alex’s aspiration — Microsoft should auto-optimize these settings after a few runs based on observed patterns, rather than burdening low-code users

V-Order Optimization: The Hidden Cost

Every Dataflows Gen 2 output applies Vertipaq V-Order compression to Parquet files—the same algorithm used in Power BI semantic models. The problem: you can’t disable it in Dataflows yet (unlike notebooks where you control this per table).

Mike argues that if a table has no lineage connection to a semantic model, V-Order should automatically turn off to save CUs. Alex agrees but raises the tension: at what point do you add so many configuration knobs that a low-code tool stops being low-code?

Query Folding: The Star Wars of Data Flows

Alex describes the ELT pattern in Gen 2: data lands in the staging Lakehouse, then transformations fold back to the SQL analytics endpoint. The catch—every point-and-click transformation either maintains or breaks the fold:

• If it folds → SQL pushdown, blazing fast execution
• If it breaks → Falls back to the Mashup engine, dramatically slower

Tommy’s summary: “The path to the most efficient way is very narrow.” Alex pushes back—it’s not narrow, you will get to the end. It just might cost you a lot more CUs if you’re not careful.

The Right Tool for the Right Job

Alex lays out a practical decision framework:

• Foldable sources (Azure SQL, Snowflake, Oracle, Teradata) → Fast Copy in Dataflows or Copy Job can move data at scale
• Non-foldable sources (SharePoint files, Excel) → Power Query is still the easiest tool by far for ingestion
• Separate by purpose — Don’t put all 10 tables in one Dataflow. Split by update method: drop-and-reload tables in one Dataflow, append/incremental tables in another
• 50 query limit in Gen 2 → Spread across multiple Dataflows based on source, update pattern, and fold compatibility
• Copy Job + Dataflows → Use Copy Job for bulk moves, then Dataflows for the final transformations (merges, appends)

The “Deception of Being Too Easy”

Tommy lands on a core tension: Power Query’s UI makes everything look equally easy, but in Gen 2, not all operations are equally efficient. Unlike a notebook where you’d naturally research the most efficient approach, the Power Query UI presents all options without restriction—and patterns that ran fine in Desktop or Gen 1 can become CU-expensive hindrances in Gen 2.

What About Excel Users?

Alex advocates hard for the Excel audience (700-800 million monthly active users vs. Power BI’s 30 million). Most Excel Power Query users just want a cloud scheduler—copy-paste their M code and have it run on a schedule. Dataflows Gen 2 with cloud refresh schedules serves this need, and the latest Excel builds now have “Get Data from Fabric” built in.

Mike pushes for tighter Excel-Fabric integration: tables in Excel should push directly to Lakehouses, and a full Excel experience should iframe into the Fabric workspace. The opportunity to bridge these audiences is massive but underutilized.

Gen 1 Sunset Signals

Mike discovered a hidden admin setting (appearing around April 5th with zero fanfare) that lets admins disable Dataflows Gen 1 creation tenant-wide. Alex confirms Gen 2 has migration tooling—“Save As Gen 2” in the ellipsis menu, plus Semantic Link Labs functions for bulk upgrades. Gen 2 advantages include CI/CD support, VNET data gateways, and proper security through Warehouse destinations.

Looking Forward

Try this on one real project first: profile one slow query with Query Diagnostics and confirm folding. Once it’s stable, refactor the worst step (joins, filters, sorts) and re-test refresh time.

Episode Transcript

0:33 Good morning everyone and welcome back to the Explicit Measures podcast with Tommy and Mike. Good morning Tommy. Oh, good morning Mike. How you doing? It feels like you’ve got a lot of energy this morning going on. You were hyping when I was running the intro. Tommy was dancing in the background. Tommy usually doesn’t dance in the background. This is This is So you must have gotten up early today or you’re just excited. I I think I’m a little excited, Mike. and my mother-in-law has been in town. So, I’ve been telling her, I’m like, “Listen, I know it seems like I don’t I don’t sleep a lot. I know it seems like I’m going back and forth in the office, but you

1:04 back and forth in the office, but you got to understand the mind here. The mind’s always going when I wake up, I’m ready to talk my wife’s ear off before coffee about anything. I now plan ahead where I need I know she needs a cup of coffee, sit down for two minutes, and then we can engage.” But from the more from the moment I wake up to the moment I go to bed, this mind is just running. And it it sounds like that would be a lot, but 37 years normal for me now. So it’s working out. It’s doing okay for you. We’ll see about 50 where we’re at, but right now it’s working.

1:35 we’re at, but right now it’s working. Yeah, I am excited though. I am excited. Awesome. today’s topic is going to be talking about how can we get the maximum performance out of Power Query. So that we’re going to go deeper dive into Power Query. What things can we do inside the service? What are data flows gen two? What are some things that we can tune the performance around those things we do in Power Query and potentially what are things we can do in desktop even Power Query lives in many different places. It’s not just the service. It’s actually in PowerBI Excel and

2:05 Excel and PowerBI. com. That being said, Tommy, do you have a beat from the street here before we get into our main topic? You before we get into our main topic? what? I think I do actually. And know what? I think I do actually. And I think this goes around a lot of the updates we’ve been talking about. just all of our updates lately have just been off the blog because they’ve been so the volume has been so significant about how many updates been coming out really it almost feels like the last six months that it’s just been fabric update fabric update fabric update and I think that I’ve been finding a process for myself

2:35 I’ve been finding a process for myself but I wanted to open this up to you what’s the process what’s your take on learning and keeping on top of all these fabric updates not just reading it obviously you can read the blog But then doing that I’m going to learn this. I’m going to get really familiar with this. So what’s your process or what’s your methodology staying on top of this? That’s a great question. I would say Tommy, my pattern is when a new feature comes out or something has been released. I try and put a project at least for me internally around

3:06 at least for me internally around whatever that new thing is. So that way I get some runtime on that feature, build something with it or take an existing I have a number of processes that I run internally for my business already. So I have API calls. I’ve got things I need to go use and and store data from. So I’m constantly trying to figure out internally like okay here’s a new feature like let’s take one for example materialized views right there’s there’s a new thing there right I have existing processes that have notebooks that run one after another multiple tables being generated and thought hm what if I took that

3:37 and thought hm what if I took that existing process and just left the original process alone made a new workspace and just copied down what I knew was working so that’s something I do fairly frequently I’m I’m constantly pushing on new projects just to make sure I understand the scope of what it can do. and that way when it goes GA, if we have a customer who needs it, we can say we’ve already done a project around it. We have some some knowledge around this. We know what what it can and cannot do. another one that we’re working very heavily with customers right now is direct lake features, right? Having a

4:09 direct lake features, right? Having a semantic model that connects back to one lake or the the lakeouses and testing that out. What does that look like? Because it’s new. It’s could potentially provide a lot of great impact to businesses, but we need to understand how it all works, what are the caveats, what what is smooth, what is not smooth, and that way we can negotiate around what features we should add or not add. So, that’s how I do it. Yeah. No, and mine’s actually really similar because obviously we don’t live in the age right now where an update a single feature

4:40 now where an update a single feature comes out and there’s five blog articles about that feature. It’s just the volume’s too ferocious right now for that where eventually there’s some articles from the other MVPs, but usually it’s the pace is so fast. So I I I’m the same. I have three, we’ll call them personal projects that’s not going to affect client and honestly I’m trying not to affect my business so much either, but things that are important to me, things that are related, but and they all cover different ones. API. Some are coming from a database and I have a

5:11 are coming from a database and I have a lot of scenarios for those already. So the same thing I try to plug and play that like when the database came out for example I’m like okay what scenarios would I need the database for that efficient well probably a power app right or something in that capacity to extend outside then you’re testing that out and I always like to have the documentation up look at the limitations just to see what’s capable there because for me I need to get to that point I call it the conga point of learning and that I’m taking that from the kan reuse

5:42 that I’m taking that from the kan reuse in the matrix when it gets plugged in and he’s like like I know kung fu you and he’s like like I know kung fu and honestly you there’s that know and honestly you there’s that immersion that has to happen with these projects and it’s hard because Mike look at look at April’s fabric update we’ve talked about this before 6 if you printed it out you’re printing 65 pages of up so you’re also focusing too Mike on I’m assuming you don’t have a project for all 65 pages you’re focusing on that

6:13 for all 65 pages you’re focusing on that you honestly have to take some critical thinking that are going to be the most impactful for clients in the future. Yeah, I would say a lot of the stuff around direct lakes, semantic models, data engineering exercises,, copy job. It’s it’s all the stuff that’s very heavily data engineering, I’m less inclined to do more work around the real-time analytics just because they’re just from a client standpoint, there’s less businesses that need real-time analytics., there’s definitely ones out there that have it. One of the things I heard someone say at a Microsoft conference that I thought was

6:43 Microsoft conference that I thought was very relevant, they said,, your your business runs like on demand. It’s all eventdriven, right? So, if you run a coffee shop, someone shows up at the front door, you’re not sitting there and saying, “Okay, every 15 minutes I’ll take an order for coffee.” Right? Right. As the as the order comes in immediately, once the order’s there, then you say, “Okay, order’s here. Now, I do something. Now, I’m going to make the order on demand when that happens.” So, while I understand that that capability, I I don’t think our data warehouse or our lakeouses run exactly

7:13 warehouse or our lakeouses run exactly in the same manner all the time. I do think there’s event- driven things, right? I’m doing some data loading in data bricks. When that is complete, I don’t want to wait to start loading the last little bit of data into fabric from data bricks, whatever that may be, right? So, I want I do want things to be event driven, but I don’t necessarily want them to be real-time analytics where I’m constantly adding things all the time. the time. I’m I I want I want event driven things, but I don’t necessarily want changes all the time. So that’s that’s what I’m trying to

7:43 that’s what I’m trying to balance a lot of times for clients. So that’s usually where I’m spending a lot of effort between those two balances. And now that I do a lot of software, right? So a lot of my day is trying to figure out what’s the surface area between like the apps that I develop. I’m doing a lot of embedding right now. So everything’s embedded, embedded, embedded. So trying to figure out what do clients need there? What are the feature requests? how how are we supporting our existing clients as well as getting new ones in the in the door for our embedded experiences? And then also power designer, right? What do we

8:13 also power designer, right? What do we build there? What’s the what is the right experience for building software? So I do a lot of that as well. but I’m always trying to figure out what new features is Microsoft coming up with that I could use to build products or tools around the periphery of of what they’re not going to support or not going to build long term. That’s a great point, too. you almost have like a mental Eisenhower matrix in your head where there’s a difference between useful and critical, right? And I think that’s a big thing that I have to go through with these updates. The real time stuff for most clients, yeah, it’s very useful, but if I’m if I’m an IT

8:44 very useful, but if I’m if I’m an IT security, that’s critical, right? Because I would like to know now if there’s a breach, but for a lot of my clients, it’d be nice, but that’s the cost we talked about. So yeah, it’s a lot of vetting and probably choosing like obviously the updates come out. I don’t say there’s a number on how many I pick to actually learn or kind pick to actually learn or go into. It really depends on what of go into. It really depends on what comes out. But yeah, taking that time and I think that’s so important where we’re at right now in the stage of fabric, the era of fabric. I’ll give you

9:14 fabric, the era of fabric. I’ll give you one more on that little note there on like what do you pick to learn, right? There’s a lot of things in fabric that are a little of like friction points. I would say things that are like difficult to do and one of those difficult to do is sequencing multiple notebooks together side by side right so I like to look at what features Microsoft comes out with that are going to be where I where I see them removing my pain points anything there any any features that are coming out that are going to make things easier for me or more automated or more linked

9:45 me or more automated or more linked together like if I have areas in the tool that are frictionfilled and Microsoft is developing a new feature that see potentially addresses that friction. Those are the ones I prioritize over other things like you prioritize over other things like again like going back to like real know again like going back to like real time like real time looks nice to have great cool but like materialized views I think that’s going to change how people build things dayto-day I see it as something I would want to use to simplify how I build notebooks and SQL statements to to load data through bronze through gold. I also see like

10:16 bronze through gold. I also see like things in copy job. I see things improvements in data in the the data pipelines. There’s a lot of things there that I think are fixing problems that I have had with the prior systems. And so those are the ones that I’m like that’s that’s I should learn that. That’s something I need to understand. Is this something that’s going to be useful enough? And for some things they’re so useful that even if they’re in preview, I like I got to use this. This is just so much better. This is so much better of an experience than before. The risk. Yeah, it’s worth the risk. You’re gonna like this. This is gonna be really quick, but with all the AI stuff too

10:47 quick, but with all the AI stuff too to learn, I just read an excellent article about where we’re at with AI. And the title of the article was we’re now in the conductors era. A lot of the tooling now, you’re not necessarily the trumpet player or the instrument, especially with all these agents. You’re not building, you’re delegating. And I think we’re seeing a little of that with fabric, but with all this AI stuff, we have to consider ourselves almost as a conductor now rather than the bit player in it where we’re building., Mike, I already seen this with with VS

11:18 Mike, I already seen this with with VS Code and the agents, you could do a lot of building of a fabric semantic model and not write anything. All you have to write is a really good prompt and it can take care of it for you. So, I think so.

11:29 take care of it for you. So, I think so. I I love this idea of what we’re calling the conductor. So, yeah. Yeah, that’s a trend that I’m seeing. Also, there’s also this experience that I’m seeing here where nan. io is this is this program. It’s it’s basically it allows you to sequence a a a prompt and then the prompt can then do different actions. It’s really interesting to me what’s going on here in this whole AI space. So, you may not just need one model. You may need three or four or five different models. And each model is a little bit

11:59 each model is a little bit better at certain things than others. And so I’m seeing people now ask a prompt. Hey, send an email to so- and so and do these things. And what happens is you submit the prompt through Slack. Slack then interprets the prompt. And then it goes through and says, “Oh, well that’s you you’re writing emails best done through Sonnet.” So it it runs the prompt through Sonic and then it then uses the automation. It hooks into Gmail and does all the things and and writes through the email and then logs the information for you. There’s there’s

12:29 information for you. There’s there’s something coming here where you need to orchestrate multiple things together. And one of the things that are that’s interesting to me is Microsoft is really doubling down on this co-pilot experience, but there’s multiple models that I may need to do different things or write code or so there’s got to be something there in the future where you something there in the future where we can start picking. Yeah, know we can start picking. Yeah, co-pilot is maybe the main horsepower that’s run here. But like if I look at cursor and other programs you can pick which model you want to use when it’s running the AI. I think that’s

13:01 when it’s running the AI. I think that’s something that I’d like to see somewhere else in fabric as well. So I can cater or I can use other tooling here that could really automate things but then use multiple models together to to do things. This is really interesting where this is going. We’re we’re at a phenomenal stage. This is fun. Okay. Anyways, that being said, we’re on to our main topic for today. Oh, hear that, Tommy? We’ve got a We have another caller today. We have a call in today. I’ll I’ll bring in our caller. I’d like to welcome everyone

13:32 caller. I’d like to welcome everyone to the podcast. There he is. welcome to the show. This is Alex Powers, the one, the only Alex Powers joining us today on our call. My gosh. Thank you so much. Excited to be here for the Explicit Measures podcast. Great to see you, Tommy. Great to see you, Mike. And I am excited to talk a little bit about Power Query and all of its various flavors and stages and hosts if you don’t mind. I love we want to get into this one. This is this has been long on our our like well Alex and I have been talking this forever but like this is

14:02 talking this forever but like this is like a dream come true of like Alex you’d be perfect for the podcast. The things how you interact. I’m just I’m super excited about this one. So this is going to be fun today. By the way we don’t have a lot of guests usually but it’s nice to join on the explicit measure podcast. Mike has a nice ring to it. Nice little ring kind nice ring to it. Nice little ring thing. Yeah. So something. So yes, of thing. Yeah. So something. So yes, exactly right. Let’s jump in. So some items today. So let’s talk about max performance inside Power Query. What you performance inside Power Query. What, let’s let’s unpack a little bit know, let’s let’s unpack a little bit more of this like how do we ring the

14:33 more of this like how do we ring the most out of our investment in our time and our in our effort here. And let me just give a little context and Tommy maybe you could add some more here in in a bit. When we think about Power Query,, we have what three 30 million monthly active users from the PowerBI side of things. I think that was they recently announced in one of the Microsoft Q1 or one of the Q calls this year this year, early this year. There’s a huge number of people that are probably using some form of Power Query at some level. So, there’s a lot of best practices that come out here.,

15:04 practices that come out here., Alex dreams an M at this point., I’m pretty sure that’s how it works., the Yeah, you see the matrix. that’s now in front of you. So, maybe let’s just start there. Like what are maybe some good patterns that we see happening here across either desktop or the service? And maybe there are different patterns I should be thinking about for desktop versus service. So, let me kick it over to you, Alex. Maybe just give us a couple maybe key nuggets here and we’ll react to those and unpack from there. Yes. Well, let’s first start with going very far back in time to Power

15:35 going very far back in time to Power Query hosted in Excel. Oh, yes. This is where Right. Yes., so I always think back of like my early days of power query where it’s like a lot of spaghetti M and I’ve got multiple tables going on lefth hand rail and at the very end is like a business analyst sitting in my cube I just hit refresh and if it takes 20 30 minutes on my local machine that’s way better than doing it manually right so like willing to accept of like oh this is bad but it’s okay because it’s my bad and it’s

16:05 it’s okay because it’s my bad and it’s helping me be better this is amazing but then I’m going to be offload to PowerBI it’s like well now it’s the cloud that you’re dealing with is it part of your subscription $14 USD now or is it part of like your premium capacity like that spend along the way though like between Excel and PowerBI of course we have power query hosted in Azure analysis services we have power query hosted in power apps we have power query hosted in Microsoft teams there’s power query hosted in quickre up in the service like if you have a little block

16:36 service like if you have a little block of tagore data and you copy and paste it in it in you’re running Power Query unbeknownst to you and then of course with like this latest generation of Power Query with the data flows gen 2 like oh my gosh there’s amazing horsepower behind the scenes but the part that I really wanted to come on the explicit measures for is like the global or universal reach that you guys have actually came here to brainwash everyone. Oh yeah first really overestimating there that reach though really.

17:06 really. Let me let me turn on my spinning wheel here. You’re getting sleepy. You’re getting sleepy. When I snap my fingers like, “All right, we all forgot everything about Power Query.” Oh, interest. Okay, I’m willing to jump on this this little story here cuz that’s the beauty of it, right? Is the UI looks and feels the exact same., some of the 1980s UI versus like the modern Power Recording online experience, but for the most part, exact same tabs, exact same points and clicks, right click, left click, menus, whatever it may be. Yep. That’s the beauty of it.

17:38 may be. Yep. That’s the beauty of it. Now, the downfall of that is like the behind the scenes engine like, well, hold on. The thing that I used to do in spaghetti code down in Excel is now taking me six hours up in the service. Yeah. Where’s the difference here? the difference is obviously the VMs and the cloud hosting that has to do all this execution and then especially with data flows gen 2 now like you have a lakehouse and you have a warehouse and then ideally you have a data destination output. Mhm. So I want to I want to forget everything that we know about

18:08 forget everything that we know about data flows as they exist today. Done. Thank I’m willing to I’m willing to follow this trend or this story here. Thank you Tommy. because in my mind like data flows are a confusing topic. gen one it was your ETL logic and at the very end became a consumable item that you go well just connect your PowerBI to your data flow. You’re like well wait I thought my data flow was ETL expressions and logic. Well it’s both things and well you can also do direct query against it. You’re like oh

18:39 direct query against it. You’re like oh so it’s a database now. And you’re like oh this is even stranger. people who didn’t have databases, they treated data flows as if they were a database. Yes, saw that a lot. they also treated data flows as the security separation where they would like well create one table and one data flow and that’s what I’ll share with users in a single workspace and I’ll do link tables across things. so then you would always see the ideas boards of like well how do I get object level access so I can only give them tables A B and D and then skip and it’s like all

19:10 B and D and then skip and it’s like all right so now you want security support too. It doesn’t do that thing. it doesn’t do this other thing very well. So I think with dataf flows gen 2 it’s now the ability to like reset expectations across the board. It treating it just like ELT. I didn’t say ETL., extract load transform. Yes. So, this Oh, okay. Only ELT. See, I don’t know if I agree with that. Actually,

19:41 Actually, you will at the end of this podcast. This is why we Oh. Oh, well, there’s a me and Mike love a good bet. So, yeah. So, let’s go. The difficulty here now is that you’ve built up all of this estate of Power Query and ideally you want to try fabric. So this is your first run experience and you want to do the minimal amount of work to try directly to try these other things. Yes. Copy and paste your power query code and then you get a big CU bill and you go what the

20:11 get a big CU bill and you go what the heck happened? Yes. This is exactly what happens. Yep. And I and I would also argue I think most business users that are coming from the PowerBI realm the first time they go into fabric like okay great dataf flow gen 2. I already know dataf flow stuff. I the UI is exactly the same. Oh cool. I have a right location. I can stick the data where it needs to go. And they immediately just like with without changing any of the settings, the bells. Like there’s other things here I want to just unpack as well. Like this there’s this whole like staging data area that comes

20:41 whole like staging data area that comes with the data flow. Like, so again, this is where it gets a little gray for me and like my understanding like I know that that’s going to every time you add something more to a process, it’s going to cost you more CUS like, but I believe staging data is on by default still. And so because it’s on by default, it’s always using a little bit of CU to run that staging piece, whatever that may be. And so I’m looking at this going, well, do we really need that? Is that something we can do we have to have that there or is that something where you there or is that something where again to your point Alex as you get

21:12 know again to your point Alex as you get better with these things you you’ll say oh actually I probably don’t need stag I’m not dealing with a bunch of small tiny files I don’t really need that so if I turn that off that should save me some cus but I don’t know again I haven’t done the testing myself what percentage of cus am I going after but that’s now we’re not dealing with small files and it was faster with small files and now it’s not and so then there’s like what the copy job stuff that’s also happening too. So massive top files that are really big, power core is smart to not offload it to other processes that

21:42 not offload it to other processes that are faster that do those large copy job things for you, but it’s still acting like Power Query. So there’s a lot of like what I would say mysteriousness that’s happening with it right now. And I don’t really feel like I have always a clear vision or picture around it when I compare it. And this is where I think a lot of my rub is when I talk about power corey against other things is there are notebooks and then there’s power query. And power curry is competing against like talent or other visual editing programs that are out there. But I see

22:12 programs that are out there. But I see it’s they has a new competitor. It has it Microsoft is competing with internal to itself with other parts of the product. And this is where Tommy and I have been very like aggressive like ah is it really the right thing to do with power?, I love the experience of it, but there’s a lot of friction points that I see right now that just need to be maybe more visible to the end user or ability to tune things better. And again, I’m sorry, I didn’t mean to derail you, Alex, some of your your pieces here, but one thing in my mind here is there’s a lot of work coming out of the notebooks

22:42 lot of work coming out of the notebooks team around is this a read heavy operation or is this a right heavy operation? And it changes how your delta tables are made. Are they veered or are they not veered? And so may I I my understanding is every time a data flow we have an assumption that every data flow may be touching a PowerBI semantic

23:01 flow may be touching a PowerBI semantic model. So everything that comes out of data flows is already vordered. Well in my understanding if I’m going after a read heavy workload and I’m just reading the data and I don’t need I don’t need to go into semantic models. Maybe maybe dataf flow should drop the whole vordering when I need to or let me decide when I’m writing the data down as a vorder or not and that save me some CU. So I’m always looking for I love the experience of it, but I want to get it more tuned down to like, hey, this is something that everyone should be able to be using. Yes, it’s slightly more

23:31 to be using. Yes, it’s slightly more than a notebook cuz you’re not writing straight code all the time. Fine. Like that’s okay, right? But don’t make it egregiously high or at least give me the controls to turn off the things I don’t think I need to get it down to a more economical state. God. Well, I love all these points, but Tommy, you were going to interject, so please go ahead, sir. So, two quick points, but you’re firing you’re firing us up, man. And, it’s not hard to do, but no, but I I think there’s two big things. So I I’m saying I forgot everything with data flows and yet you

24:01 everything with data flows and yet you mention all the different iterations of data flows which are in Power Apps, Power Automate, all these different things and now we’re saying with the Gen 2 it’s not ETL, it’s ELT. My first thoughts like then call this something else because if every single iteration I have followed the same process but that doesn’t work as well with Gen two. First thought. Second thought, and this is going to be super quick, and this is really, I think, part of talking about the enhancing this. Mike has heard this

24:31 the enhancing this. Mike has heard this a ton, but I have the philosophy of Steve Jobs. If you’re going to introduce a product, feature or service, and it has other things related. It has to do something better than something than the other products that are there or has no reason to exist. Steve Jobs said this when the iPad came out. If we’re gonna have a bigger phone, it has to be better at reading browsing the web or watching a movie or else we shouldn’t make it. And I think for us where we’ve talked about this is I love the UI. I have been such a data flow guy and un

25:02 such a data flow guy and un there has been that frustration with Gen 2. So I think with what can we do to show that value where hey power the data flows gen two it does this X Y and Z so much better than the other com in a sense internal competing like services or features. My gosh I’m just like deep in thought over here because I feel like I gave you all the answers to the test. So first one I want to go back to like Mike’s point here. VOR optimization. for those who don’t

25:32 optimization. for those who don’t aren’t aware, the Vertipac algorithm in compression it’s applied to the parket files. So whenever you’re writing things out, we’re going to apply the same amazing vauorder scanning that occurs today in PowerBI to those binaries. The thing here is in other applications you can control on or off. With data flows you cannot not yet. Not until not until this episode airs and the power team gets a hold of this Yeah, the universe is going to go out

26:02 Yeah, the universe is going to go out and vote on ideas. Fabric and they’re going to,, say allow me to disable. I’m also of that opinion, Mike. But the part I need to challenge both you and myself on is is it a low code tool? Like at what point have we added so many customizations, configurations that you as a Power Querym point-and-click user need to go out and understand like, well, what does parallelization mean? What does disabling view optimization mean? like at a certain point I feel like your level of depth is very far past maybe the original persona. Now it’s great as

26:33 the original persona. Now it’s great as power user that I could have that capability or should it just be intelligently discovered at runtime by the execution engine? Well, let’s also unpack this a bit of time. I let’s also unpack this a bit of time., usually your first run of data mean, usually your first run of data flows doesn’t really tell you anything much about how much CU it uses to be honest like let’s and and then honestly there’s Microsoft already knows the lineage of all my tables wherever they may be, right? So if that data flow is running and that that there’s no linkages between a semantic model and

27:03 linkages between a semantic model and that data flow or that table that comes out of the data flow, Microsoft already has a lineage of this that knows where these tables are coming from. So again, to your point, Alex, right, maybe Microsoft after the first two runs, it should automatically know, hey, this is not being used in a semantic model,, I’m going to immediately turn this down and not actually have it do a a v order anymore. Cuz again, you can you could I think you can technically change whether or not it’s vordering or not reordering because it’s just another series of tables that are being rebuilt. That doesn’t change it doesn’t change how it

27:33 doesn’t change it doesn’t change how it reads the files. All that does is changes how the impact of how fast that it runs. Right. Y these are things to your point if we’re talking about the front end UI of things again it’s a fine line right there’s the idea of build it so it works and then there’s the second step that is tune it so it’s more efficient right so there’s an optimization so a lot of times I feel like a lot of the power query is just I need to get people’s brains around what do they need to do to shape the data and then once we understand the shape of the data we’ll use that to build other

28:03 data we’ll use that to build other things that are potentially more efficient or better used or more reliable or but there’s there also a couple things in Power Query that are just extremely in my opinion extremely difficult to get around paging through data using an API in Power Query difficult. Right. Right. And and you difficult. Right. Right. And and there’s there’s just general know there’s there’s just general getting a token and using said token in an an API not it’s getting better but it’s still not as easy it is in other experiences. So there are a couple areas

28:33 experiences. So there are a couple areas that I think are just very frictionfilled that maybe the power power query team will get themselves around. But for me, I’m looking at it going like it’s it’s taken away some of the glimmer of it in comparison to everything that we get now. I’m willing to data flows though with that mic piece again it shouldn’t do everything better than everything else. Like and the API thing flesh I’m sure but I think the glowing reviews that I’ve always loved data flows again to this day. I remember when data flows came out gen one because I was so upset because we had my I had

29:03 I was so upset because we had my I had my first daughter and I couldn’t use it because I was on paternal leave. I was like oh no thing and I was I missed it 2017 I can tell you it was January. So or but I think the thing is yeah I can’t do some of those advanced features but the UI what other tool do you have out there where I can just start going and I don’t have to know another coding language. The big thing with Power Query has always been 90% of what you can do you can do with the user interface,, and that’s what a pitch,, and and

29:35 what a pitch,, and and they’re really great features and I think yes, I’m willing to forgive the API thing being a advanced I need 18 parameters and I need to make sure it plugs in here, but I can’t make it dynamic because it won’t work with data flows. Fine. Fine. The UI by itself is such a good feature and it’s always worked if really efficiently even with Gen Gen one that it may not be able to do the advanced things but that pitch of the user interface and being very efficient with

30:05 interface and being very efficient with that has always been its calling card. So the API example that’s a moment in time example with artificial intelligence, right? Like you just have GPT generate you some code. Well, I’m I’m I’m using more often than not when I write things in M or getting things trying to build stuff out in Power Query, I’m actually opening up Copilot in my well, Copilot in my browser and I’m I’m just or sorry, the the regular copilot in my Edge browser and I’m asking, hey, here’s the

30:35 browser and I’m asking, hey, here’s the error I I’ve received. Here’s the M code that I had. Can you please help me rewrite it? Believe it or not, the co-pilot is actually getting pretty dang good at debugging my M code straight up just using it. So like yes talking to again these are one of these things where I’m like the pattern exists. People are going to try to do it and I’ll still give I I’ll concede this one. Getting to files inside SharePoint. I still think dataf flows gen 2 or dataf flows in general is just by far the best tool to go find files in SharePoint.

31:06 tool to go find files in SharePoint. It’s just the easiest thing to get access to. I’ve tried to do it without it and it’s like a pain in the butt to get anywhere close to SharePoint with files. lists maybe not as much of a big deal because it’s a little bit easier now, but like that is egregious anywhere else. So, it’s it is by far the best tool there for accessing my Excel files and paging paging through them. Cool. All right. So, we’ve talked about the beauty of the UI with Tommy. Yeah. So, start talking about the behind the scenes now. Gen two, right? Gen two. Yes. You had talked about staging.

31:37 Yes. You had talked about staging. You’re like, “Oh, that’s on by default, but it is also turns off by default.” So if you’re going to small files, it will say I am going to turn this off for you with a yellow banner just due to the volume the scans that it may do against those files or sources. Yes. If you’re writing to a data warehouse, it is required to have staging. Really yellow explicit banner that says, I’m sorry, because you’re writing to a warehouse, staging is required. in the past when it was first released, it was trying to do

32:07 first released, it was trying to do single row inserts as opposed to like bulk. So you sit there and stare at it forever like all right we got to make this thing required you can’t do crossworkspace operations with the warehouse so it has to be in the same workspaces the data flow to the destination and then of course like all these other things that are now released like writing to SQL database and fabric SQL databases in fabric I think that’s the official marketing term or writing to like Azure SQL database like blazing fast like yes always if you’re just streaming it to a very simple

32:37 just streaming it to a very simple destination like that now When it comes to staging, we will take the data, we will write it into the lakehouse. So just like any other ELT pattern, ELTL if we’re really doing like the full acronym here, but you want to load your data into the destination first and then you want to start doing transforms on top of it. Yes. So we will write that view order optimization to that behind the scenes lake as Mike talk on. It’s like well why do I need this? Like I’m only using this in the context of my ELT pattern. Yeah, I would agree we

33:08 ELT pattern. Yeah, I would agree we should have that off by default for viewer optimization. So everyone here listening on explicit measures podcast, go out to ideas. fabric, let’s vote this thing up. let’s have this as a nice little requirement. From there though, now you get the SQL analytics endpoint endpoint that you can then start doing transformations on top of and you also have the query insights from the dataf flow staging warehouse. So if you want to go and see the M execution that’s occurring query insights what what

33:38 occurring query insights what what occurs with SQL in power query query folding. Mhm. Ah yes and now it becomes more of like the Star Wars of like you more of like the Star Wars of like close your eyes and hope that you know close your eyes and hope that you don’t break the query folding. like that’s really what’s occurring. So, as you move into the ELT pattern, everything you’re doing on the points and clicks, Tommy, is did I break the fold or did I not break the fold? Because if you did break the fold, we’re going to offload to the mash engine now. So, there’s multiple things going on here in the tail., but ideally with

34:10 here in the tail., but ideally with like a clean warehouse, everything folds, get me to my final destination. This thing can be blazing fast., I think in one of my examples, I’ve got like 373 million rows being written out in two minutes and 30 seconds. So it can do things at scale. Yeah. But the path is very narrow if you want to

34:31 the path is very narrow if you want to treat it like an ETL tool. This is interesting to me cuz I was I was just mcking around the other day inside the service and there’s this whole Okay, I don’t know. I can’t remember exactly how I got into it, but you have is it is it SQL data warehouse or SQL database? I can’t remember. I think it’s SQL data warehouse and you can now build visual queries in inside the Okay. Is that okay for the longest time? Yeah, that’s the power. Yeah. So, it’s weird. It’s weird, though. I can’t quite figure it out. So, I’m in a SQL data warehouse, I think,

35:01 I’m in a SQL data warehouse, I think, and I’m going to do a visual query and it it appears as though I get a limited like amount of operations that are all foldable. They’re all folding back to SQL. And then I can click a button that says push this over to Power Query. And what I was expecting was like a SQL statement at the beginning that said, oh, here’s your SQL statement of like the the visual query building. and then oh by the way we’ll pick it up from there and then do whatever you want to do with the with the the M code. But I was very shocked when I moved from the

35:31 was very shocked when I moved from the visual query into power query. It just wrote the M code for me. I was like okay that that does make sense because these are the same operations but I was very confused on how to explain it to my customers is like in the visual query space am I writing M or am I writing SQL or am I writing M that is only foldable SQL in that experience you can also create a view that write SQL the other option there designer the last button on there is a big rectangle did you click that I think

36:03 big rectangle did you click that I think maybe to take you over to Power Query experience. It won’t take you over, but it will take you from the limited navigation bar to the next full-blown Power Query. Correct. Yes. Then as long as you maintain folding, you can then save that visual query back. But that is such a weird experience. I don’t know why anyone would use it outside of I I have a low code user in a data warehouse and they don’t know SQL. And I’m like, well, why why are they editing if they don’t know SQL? And that isn’t like a gatekeeping thing, but that’s my own general curiosity. Well, let me great

36:37 general curiosity. Well, let me great curiosity in this one. I think there’s a a strategy around. So again, let’s think about the story or the use case of that 30 million users that we’ve been that have been conditioned to say, look, it’s power query, it’s tables, it’s views of data, I can see the things physically there. I I got to be honest if we look at the scale or the scope of like okay we have people who build reports we have people that are like maybe medium level experts they’re starting to get towards data engineering that they’re not there yet they’re doing a bit more and then there’s like the advanced users right so I would put this

37:09 advanced users right so I would put this in in like this is part of the growth pattern that I see for people coming from power query and powerbi moving more towards a fabric experience so it still needs to be graphic in nature I feel like it’s this is going to sound like a square word Tommy and feel free to tell me I’m wrong here but it feels like access for fabric that that access like it’s that visual query building and I I really do firmly believe like when I learned SQL that was my first interaction was using access grabbing a

37:39 interaction was using access grabbing a couple tables building a relationship or two like that made sense to mechanically in my head I could visualize it and then I could just flip over and see like the SQL behind the scenes like oh oh that’s what it’s doing So there’s something to say for like learning code by having some graphical something on front of it and then behind it you’re actually generating real code that you can kind generating real code that you can jump into and out of and see it and of jump into and out of and see it and you can’t. this is the same experience I really like with like learning Python using data wrangler. Like it’s the same vibe to me

38:09 Like it’s the same vibe to me when I’m able to jump back and forth between a UI and then the actual code experience. I think it serves a purpose and as we try to bring more of those 30 million users over to us in the fabric realm, I think this is going to be a useful useful teaching training education tool that’s going to help them become more capable in that space. So that’s where where I see it fitting right now. Yeah, let me touch that and I I’ll hand it over to Alex because I I want to touch on that’s such a good point, Mike. And I think this is so pivotal to where we’re

38:39 think this is so pivotal to where we’re at with data flows and I think just fabric in general that 30 million users. Well, not all of them are people who are like what’s PowerBI? Oh, look at user interface, a lot of MVPs, a lot of enterprise use data flows. Mike and I have had this conversation since Fabric’s come out about the overlap of skill between Power Query and Python users. And initially I’m like no people in if you’re a advanced power PowerBI person you shouldn’t learn Python or it shouldn’t be a requirement.

39:10 Python or it shouldn’t be a requirement. I’ve shifted with that. I’ve shifted the Mike’s can be very persuasive and so can the products. So but but I think the biggest thing here is what I’m hearing and this is what I want to pose to you Alex and tell me if I’m wrong but tell me the path forward here. What I’m hearing is dataf flow is gen two immensely powerful. We have all the features available with a nice user interface. However, the path to the most efficient way is very narrow. There’s some things you don’t want to touch and you need to be aware of. Yet, that’s

39:40 you need to be aware of. Yet, that’s not visible or manifested to the user initially with this nice pretty easy user interface. Am I am I far off from where we’re at? what can we do even if I’m an advanced user or just a normal user to use data flows but make sure I’m using it in a way that’s the most efficient. Yeah. So there’s multiple things you said in there and I want to hit home on one was like the narrow path to success which I think is wrong to look at it that way because you can still get to the end of a very successful refresh. It may just be a

40:11 successful refresh. It may just be a longunning tail that cost you a lot of cus. so when we talk about efficiency, we’re really talking about like a high level of skill set of that individual who’s building it. so now you’re like am I staffing my team with someone who is highly skilled in data flows to maximize the CUS or is the skill set better spent learning Python, learning SQL, learning these other things. the other half here was like the MVP thing and like a very interesting hypothesis that I have right now is that like a lot of people had to use power query but they didn’t lose

40:42 use power query but they didn’t lose their SQL skill set. they didn’t lose their Python skill set. It’s just they didn’t have a place to meaningfully use it. Now with fabric it’s like well here you go. Here’s a tool of your choice or a language of your choice. You’re like oh amazing. I’m just going to go use that because that’s what I’m really most comfortable in. I just had to use the thing that was the the the path to get into PowerBI semantic model or using the data flows gen one. I think that that’s what we’re seeing is that a lot of people had these skill sets and they now are finally unlocked.

41:12 sets and they now are finally unlocked. The other path though is like every kid in high school and college is learning Python. and even that like we’re like I don’t know is there an investment that’s worth finding with the AI and GPT or is it going to be vibe coding like your way to the solution and that’s and the the marketing terminology here that I’ll like challenge everyone with is like I don’t hear anyone say low code no code anymore. No interesting yeah you’re a term has almost 100%

41:44 yeah you’re a term has almost 100% dropped off the radar. I would agree with you on that one and I will so me personally right I’ve been really experimenting heavily with AIS to help me vibe code things this this is something that’s really been very interesting to me and it there’s there’s a couple like when you when you apply AI to various things like you started asking chat GPT some questions around things and like those first initial questions your first interactions with chat GPT like how good it was to answer the questions so an example I’ll throw out here I was just having an electrical

42:15 out here I was just having an electrical issue In my house, I was replacing some outlets. I didn’t know how the wiring was supposed to be. I literally was taking pictures of the problem, describing it, and writing it out in text form, draw brawling a diagram, and giving the image to chat GPT, which consumed all the information, rewrote the answer, and then responded back with like the correct answer on how to rewire things so it was correct. And then I had to like verify on the internet like, is this right? So, I didn’t even really know what terms to look for, but it was just simple things. It was just, you just simple things. It was just,, adding some switches in and fixing

42:45 know, adding some switches in and fixing some things there that didn’t feel like it was working right. But it this, the reason I bring this up is because I have spent probably in the past two weeks, I’ve probably have built about three or four websites, just just fullon full websites., and that is something that’s been very new to me. And I was able to vibe code these website things. So,, websites aren’t the easiest thing, but they’re also not the most difficult thing either. And I’m thinking to myself again, I’m I’m trying to unpack this and like what does this mean

43:16 unpack this and like what does this mean for me as my skill set? What does this mean for my kids who are going to start learning computers that are starting to get into this step? They’re using Chat GPT now and I’ve taught them a little bit about it and and now they’re, you bit about it and and now they’re,, my son was working at a a know, my son was working at a a restaurant and he’s like, I’m I’m done. I I need to move on. And he goes, I went over to Chat TV. I told it what I wanted to do. It gave me a form. I added some things in there. It was it’s it’s going it’s going to physically change how we do everything and it’s not going to be

43:46 do everything and it’s not going to be low code no code. It’s going to be AI code or vibe code or full code. I agree with that statement but I think the UI is still helpful here to get solutions. So I’m with Tanya of like the pointing and clicking is amazing but that also allows the transfer of that logic to different languages to different systems very easily. So like start out in Power Query, quickly transform it into Python. Have Python, turn it into some Power Core. Yeah, like the transfer between services to skill sets could be very interesting as we move forward.

44:17 interesting as we move forward., I think that’s that’s something there that I think is un underutilized. I would I would really like to be inside the Power Query experience, build what I want to build there, go back to C-Pilot in Power Query and say turn this Power Query steps into a Python function. Like that’s that’s what I want to do. and then go to my notebook and then run it like that. That would be amazing. Mike, I I Alex, I know you’re you’re Microsoft, so I I have to use the C word here, but to your point, Mike, I’ve used cursor to do that. and I know that’s not the greatest tool for

44:48 that’s not the greatest tool for Microsoft, but dude, it’s I had the same thing. I had this data flow. It would run. I’m like, well, I guess I should go on a bike ride to,, St. Louis and back. And I basically told it I I had an agent run and we turned into a Python notebook. So yeah, there’s a point there with the user interface and you might have been touching on this Alex, but the user interface is so simple where it’s almost deceiving, right? Where in the same fashion they don’t make airplane co-pilots co or like the the dashboard easy to look at because it’s meant to be complicated., you

45:19 meant to be complicated., you don’t want it to be too easy to use. So like, oh, I could fly this plane. There’s a lot of things that you can do in data flows. But again, I think and I we were touching on this before the AI thing, but again, let’s talk about that path, right? You’re not you disagree that it’s a narrow path to efficiency. Yes. So, let’s expand on that. So, let’s start with our actual source. Okay. Oh, so if I have a foldable source or a non-foldable source, I have a foldable source and I just need to move it from point A to point B, is

45:51 to move it from point A to point B, is it a data flow that I need? Could I also use a copy job? Could I throw it as a copy activity into a data pipeline?

45:59 copy activity into a data pipeline? Multiple different places, right? So this is like the fabric issue of like 100 different ways to do something. it’s up to you to decide like what is the the best path for you. so data flows can copy data at volume and scale using the fast copy which is the data pipeline copy activity underneath the hood of power query made simple. So you don’t have to go learn a python. Yep. from there like if it’s a non-foldable source like a file in a SharePoint probably way easier to ingest using Power Query. Yeah, I’d agree. Excel

46:30 Power Query. Yeah, I’d agree. Excel files, is it worth building a full-blown notebook or like a full-blown data pipeline or is it way easier to just connect to that data and then write it using data flows? Probably say way easier with data flows. I’d agree there. Right. I think there there’s a pairing between like the Excel world and the Power Query world because that’s where it was like born and grew up together. Like the friction there is so little. It’s it’s much much better there., I’ve done a couple, you there., I’ve done a couple,, read a Excel file inside Python

47:00 know, read a Excel file inside Python and you need some libraries and you got to know how to do it right. And there’s there’s not like an easy button there that gives you like the pieces of code that you would need. Power Query by far is easier there to get after that data. I think I had a light bulb. Alex, continue because I think I know where you’re going with this, but I’m going to let you let you drive. What do you start evaluating like at the very beginning of like is this the most efficient method? Mhm. Could be the easiest method, but like efficiency is like a very different thing that we’re like evaluating here now, right? So, Power Query can absolutely do fast

47:31 So, Power Query can absolutely do fast copy if it’s a foldable source like look at the list, Azure SQL, Snowflake, I think Oracle, Terodata and a few others. simple UI point A to point B everything is folding because there’s a SQL engine for the non-foldable I think people call them like slower sources quote unquote it may be simpler to just use power query for the initial data transfer from point A to point B and then doing a query reference to data that now resides in the lake and

48:02 to data that now resides in the lake and then throwing the warehouse on top of it for the actual transforms. So you’re now having your query with query references. So in the old days, I think it was enhanced compute engine, right? That we had use this with all in one data flow or you can break it out into multiple if you’re doing like the medallion architecture. You’re saying this data flow’s purpose is only to move data from point A to point B. I want it done as quickly as possible. So that way I can release the mass up containers and then that way I have more flexibility downstream for the next item in line., other things that I

48:34 in line., other things that I look at here, Tommy, is also like the update update methods. So, as I’m writing data to the source, do these tables, are they just quick drops and recreates? Oh, yeah. Or is it going to be a longunning operation like an append where I have to add new data? I need to do some type of incremental refresh to look at some logic, whatever else it may be. I then start looking at my data flows of saying this set of data flows. I’m going to drop and reload. This other data flow has maybe one or two tables in it that are purely append

49:05 two tables in it that are purely append operations. I’m no longer having all my logic all in the same data flow. Okay, I like that because in PowerBI semantic models, what do we have to do? We had to put every table in there, right? Yep. Correct. Break free that that mindset. I like that one a lot. Yeah. So there could be scenarios where you’re like, well, I’ve got 10 tables. Eight of them are copying. The last two were transformations, right? At that point, you say, well, maybe because the source

49:35 you say, well, maybe because the source is supported with copy job, it’s still a UI. It’s still point-and-click. I’m going to offload all of these simple to a copy job, which unfortunately you can’t run in a data pipeline just yet., so stay tuned there. It’s been announced, but it just has it hasn’t shown up yet. Yes. So from there, you’re saying, well, these activities, I’m still going to use a low code, no code interface of a copy job. But these other ones, I’m going to land them in the lake. I’m going to connect to them with data flows. Then I’ll do the final transforms. Maybe it’s a merge, maybe

50:05 transforms. Maybe it’s a merge, maybe it’s in a pen, whatever it could be. And then you’re going to get them to their final destination. You’re starting to sprawl things out a little bit. I don’t,, like to use the sprawl word, but you’re having the copy job to do the most efficient operation of moving data from A to B or you’re having the data flow just doing the most efficient operation for data transformations. Tommy, go ahead. Yeah, I’m going to try I’m going to try to repeat what you said and my interpretation of it or what exit ramp you’re taking us to take here.

50:35 ramp you’re taking us to take here. really the the initial again the principle with the methodology really with data flows and power query in the past is you can do it all here you can do it all in one yes we had the ETL with gen one but it was all data flows y but what I’m hearing what I’m hearing is because of fabric because of the architecture we should not be thinking that way anymore where it’s only hey this project is only data flows here it’s it’s intended to be broken up where what we’re going to copy here we’re going to do some quick transformation information that I can do

51:06 transformation information that I can do in a data flow here going to bring that to a notebook here and that’s really the most efficient way. Am I hearing from you that really data flows in general or maybe all the products in general are not intended to be siloed in a sense where data flows do some things much better but it’s intended to be part of a longer pipeline so to speak the the concept conceptual pipeline so I think you’re going to see more things are going to be added to the data flows so

51:36 going to be added to the data flows so like right now they have the fast copy which is I don’t know how to build a data pipeline but I do high speed retrieval at scale. It’s like going after these 30 million plus developers, right, of like do they need to know how to build a pipeline? No, we have it here. they just need to know what the feature does, which you guys have kind feature does, which you guys have talked about at the very beginning of of talked about at the very beginning of the hour. They also have parallelization now where they can spread your instead of doing like a single node execution, then I’ll spread it across multiple nodes. So they can do some very high scale of like all right, you’ve got

52:06 high scale of like all right, you’ve got a massive transformation, let’s just spread this across multiple machines and then we’ll complete that for you. So all things that have been done in the cloud a long time ago as other things are being introduced to data flows that’s where like for me it’s like the professional level is being treated very well like reading very good right now. but as a low code first-time user it may be a little bit more it’s a bigger task and that for me is like I would always advocate that I want the

52:37 would always advocate that I want the back the background to auto optimize for me. So I would prefer that Microsoft just figure that stuff out. If I don’t have enough if I have 100 rows in the tech CSV file that’s on you to turn off staging. I don’t care. Exactly. Yeah. No. Exactly right. And I think that’s that’s where I’d like maybe a bit more aggress and it’s it’s also like yes I want Microsoft to do this thing but I’d also like to see like the hit list right so there’s a lot of these little optimization things that are probably happening or settings there’s there the properties panel of

53:08 there the properties panel of that data flow it feels like there needs to be like a summary a little bit more hey here’s all the here’s the settings here’s all the settings when you started it was like this and then here after a couple runs we decided that this is a better way or we’ve automatically turned these things off I I think that’s helpful. Microsoft should just do that. But there should always be a place where experts can come in and poke at those features and turn them on or off as needed. But again, Microsoft has doing what terabytes pabytes pabytes of data movement with

53:39 pabytes pabytes of data movement with data flows. Like they should know what’s best. Like they there should there’s no of the data flows that have been built. There’s probably nothing new that they haven’t already seen or have some haven’t had someone build already. So because of that, Microsoft should be the one pushing like this next round of like optimizations and that should not be a burden to the user. And I really do think that’s that makes a lot of sense here. What one thing I just want to point out here as well, we’re talking a lot about options in data loading when

54:10 lot about options in data loading when it comes to fabric. I there’s a nagging point in my mind here around like you point in my mind here around like there is a lot of users still just know there is a lot of users still just using PowerBI. So all the all the things we’re talking about there these are like dataf flow gen 2 features these are dataf flow like paralization that’s dataf flows gen 2 copy job happening behind the scenes large amount like that’s dataf flows gen 2 so a lot of these things that we’re talking about here also are fabric only related dataf flow gen 2 features and I was looking

54:42 flow gen 2 features and I was looking around in my tenant the other day and Tom I’m going to poke a bit here for a little bit Tommy and I found there’s a setting inside the admin settings that says don’t allow allow data flows gen one anymore. You can turn it off. And I was flabbergasted. I was like, “What the heck?” I don’t remember seeing this feature. I don’t ever remember seeing it being like listed as a new feature in the new items on the list. So, I don’t know when that appeared. I went back to my historical records and looked at every day. It looked like April 5th was

55:12 every day. It looked like April 5th was like when it that feature appeared, but there was no fanfare. There was no announcement. it didn’t feel like it was getting stuck into the hey look these are new admin features you can use and I feel like this was a little bit sneaky to some degree because all of a sudden now there’s a setting that says turn off dataf flow gen 1 that’s a tenant setting which any users who are potentially building things in PowerBI now can’t use that dataf flow gen one experience so that was interesting to me what was going on

55:43 interesting to me what was going on there and it feels like Microsoft is trying to push everyone more towards fabric and dataf flow 2, which is probably right. But I’m just thinking to myself like when I when I look at the dataf flow gen one experience, it’s CSV files. It’s, it’s it’s if you if you set it up correctly, you can actually attach like a a storage account and you can have dataf flow gen one running and then just saving files down to disk which is the storage account which it comes out with CSV files. So like all of that experience still exists and that’s just totally different

56:14 exists and that’s just totally different than what we’re seeing now on data flows like the whole to your point Alex the whole paradigm has now shifted and not only are we getting a lot of new options to do to do opportunizations but the data flows gen 2 is literally a different animal than it was the first time through. Oh yeah for sure and the setting likely was that famcon famcon April 5th. Yeah. Yeah. So that’s part of like the save as experience. So if you go into a data flow gen one dot dot dot ellipsus menu there will be a save as gen two a quick upgrade. I’ll

56:45 gen two a quick upgrade. I’ll have a spandic link labs function out there where you can bulk going through all of your data flows to upgrade to a gen two with CI/CD support. So very explicit there. a lot of the things are to address like the gen one challenges. Yeah there is different execution behind the scenes as well. All the gen one and gen two architectures are vastly different as we’ve talked about like hey this is writing out to CSV this is writing out to binary park a file with v order and lakeouses warehouse etc. The part for me of course is like if you

57:17 The part for me of course is like if you have a very large dataf flow gen one estate like now is the time to start deciding like oh we’ve been looking for security support for the data flow or at an object level or row level security of

57:29 an object level or row level security of the data in a data flow. That’s what warehouse is for like like that’s what you want to solve your problem. here is the solution now. so start looking at as the ELTL tool. the other aspects here of course are just like I want CI/CD support. Hey gen 2 has kind want CI/CD support. Hey gen 2 has got you covered there. VNET data of got you covered there. VNET data gateways only exist for gen two. They don’t support for gen one. if you go out in any of the docs like migrations of gen one to gen two. I authored both that and the scenarios doc. So I was

57:59 that and the scenarios doc. So I was very much like if I was a functional consultant, what is my go or no go matrix to say now is the time that we should start investing in Gen 2. or if I’m looking at the scenarios of like do I go lakehouse warehouse? Well, here’s the requirements. What do you want? Well, I want to share tables across workspaces like I did in Gen One with linked tables. Yep. So you want a shortcut. It’s like, oh, all right. I still get the thing. I just didn’t know what it was called now in fabric. They’re called shortcuts

58:30 They’re called shortcuts table across lake houses. so like there are so many amazing new things, but I do agree that it’s the level of investment and rework that you should now be evaluating. Yeah, please don’t just copy and paste the code and expect light for light comparisons, which I see everyone on LinkedIn, Reddit, Blue Sky, wherever else they’re like, “Gen one is faster than Gen two.” And like is it though? yeah, and and I would agree with you on that one, too. I think I think you’re right about that one very much so. It’s it’s very

59:01 much so. It’s it’s very people could potentially cherrypick or think they’re cherrypicking a good scenario, but in reality, it’s actually not playing to its strengths as much anymore. Yeah. But now it’s the time investment, right? Right. Yes. Yeah. True. And it’s the deception of being too easy. real quickly, Mike, when we were talking about the hidden setting, Alex, you’ll appreciate this. It felt like when the the Cubs or the Yankees designate someone for assignment on a Sunday night on a tweet like by the way Devin Williams is no longer he’s gone he’s gone Sunday night at

59:32 he’s gone he’s gone Sunday night at midnight by the way. So that’s what it feels like or Mike in baseball you can get rid of someone in a very not illegal way but they do it very under the hood and that’s what that’s what we’re doing. They’ve had some guys who were just like about to step on the mound and you’re just like, “Oh yeah, yeah, they got traded an hour. They got traded for cash, which is the worst one or like but it’s almost the deception with data flows that it’s like the the two sides of the coin.” I think we’re getting to

60:03 of the coin.” I think we’re getting to the point or we’re we’re finding the deception of it being too easy. And I think because you have unlike Python or and unlike the notebook where there is a user interface but a lot of things you got to do a little research on or you ask chat GPT or AI which is going to find you the most efficient way for what you’ve asked it to do like if I want to group by do a subindex or whatever it may be well chatp is not going to find you just any random path it’s going to do the one that obviously

60:33 it’s going to do the one that obviously works but with data flows and power query in general. It has all of these options available to me and there’s no restrictions for the user. Whether or not you’re advanced or just starting out and a lot of things that worked in PowerBI desktop that run blazingly fast or just worked, maybe they weren’t super fast or efficient, but they worked great. And guess what? It worked great in the service and it worked great on my desktop. Yeah, it may may take 20 minutes, but what? It it was

61:04 minutes, but what? It it was never a hindrance. those same patterns in data flows gen 2 can become an hindrance because like to your point it’s really doing something different even if the U looks the same it smells the same but it’s not the same thing. Yeah. And I think that’s the biggest difference that I’m discovering today is yeah, it does the same transformations, but unlike any other iteration of Power Query, it’s trying to it’s really attempting to be something else. But again, the and I don’t want to

61:35 else. But again, the and I don’t want to say my hiccup with this is but yet we have the same giant user interface of options available to me. So to to me even it’s like, oh, I guess I can do all of these and none of these will hurt. And I think that’s that’s the hard thing to really navigate through aspirationally. I believe that the engine should just do it for you. If you want to create crazy spaghetti code, then that’s the way that you your mind mentally maps. Like,, I could sit here and be like, well, you need to break it out and you need to do a query reference. You need to have this other query that does this. Here’s my M. You

62:07 query that does this. Here’s my M. You go figure it out. Yeah. That that is the ideal world for me. Aspiration. Oh, I like that. and there’s no reason why I couldn’t right like hey you have enough run times you can figure this out based on like data sources and like based on transformations etc like steps one through six fold steps seven through eight don’t like you figure out how to break it apart to make it run efficiently. I like that. Yeah. And the things for me is like I just look back to like the Excel users in like my own days. in data flow gen

62:38 in like my own days. in data flow gen 2 there’s up to a 50 query limit. So again, that’s like why I like things of like, well, if I want 51 or 52 queries, it’s like, well,, I’m going to go yell at Microsoft. And it’s like, well, why? Like, think about your problem differently. Spread things out. Use two data flows, use three data flows, based on sores, based on update method, based on does it fold or not fold. there’s so many different ways to think about the problem., but I do think of every Power Query user that’s in Excel right now. I would just love for them to copy and paste the code

63:08 love for them to copy and paste the code into the cloud and have it execute because nine times out of 10, everyone that’s using Excel is like, I wish there was a scheduleuler for me to optimize and run this., if you’re talking about if there was Power Automate, hey, that’d be amazing., if not, like use dataf flow gen 2 cloud schedule refreshes. Get your data ready, throw it into a database in Power Query. Now, if you’ve been in Excel, like the latest build, it has get data from fabric. Now, start selecting fabric

63:38 fabric. Now, start selecting fabric items., so keep people in Excel if that’s where they want to be. If you want to build pivot tables, like there’s so many amazing things we could do. Thinking about our Excel users,, they just want to copy and paste their code. That’s it. They don’t want to learn dataf flow gen 2. And that’s just me being honest. PowerBI users, they’ve been around, Gen One for a while. they understand like this enhanced comput engine stuff if they have access to ppu or a premium. I didn’t get asked about fabric pre- users so thank you guys

64:08 users so thank you guys till next I’ll remember that that’ll be a good topic too but there’s just so many great opportunities I think for people to take a step back and be like what is it we want to accomplish with like a light forlike comparison or first run experience just knowing that the generations are not the same underneath the hood. It’s it’s interesting that you’re bringing up the Excel space of things because I think that’s one area of PowerBI that has been continually the weakest since day one. It’s this like Excel table like experience that you can use. It

64:38 experience that you can use. It seamlessly integrates with like the table experience and going to like Lakehouse or M code or like it’s very it’s not quite the vision of that has never really come to where I think it should be polished on at this point. And I and I really do feel like there’s a missing visual inside PowerBI desktop that is like the table filter, table slicer, right? It should be something I’m going to go there again, Tommy. Tommy hates it cuz I keep going to Here we go. Here we

65:08 cuz I keep going to Here we go. Here we go. But I’m going to step back from that. I’m not going to I’m not going to push that too hard. But what I will push on though is if we look at the size of audiences, I know that the Excel I I don’t remember where I heard it, but I’m I’m guessing Excel scale is huge. Like the amount of users Yeah. And compared to PowerBI, we’re talking PowerBI is 30 million monthly active users. I think Excel is in the 800 to 700 million monthly active users, maybe even higher than that. Yeah. But that’s the the scale of that size is like if we can

65:38 scale of that size is like if we can bring more of that audience over and start leveraging more of what we’re the ease that we’re getting everywhere else like why isn’t there a lift your Excel power query right into fabric and just have full Excel super tightly integrated with like even just table view like a single object inside the fabric workspace that is like the same thing as tables inside Excel like the exact same thing but just really really polish polished and very featurerich compared to the the original one. So that would

66:08 to the the original one. So that would be one place where I’d like to see some investment there because I think that bringing that user audience over would be very useful for the fabric and the PowerBI space as well. Instead of close or close and load too, we could do close and load to a data flow. Yes. From Yeah, exactly. That would be that’d be amazing. or start pick even just in Excel just start picking tables like just make the tables that exist these tables in this Excel file just immediately go to lakes or whatever I don’t know what you do with them but some something there that just like immediately makes it easy to get it

66:39 like immediately makes it easy to get it landed somewhere else that would be immensely useful for people and then I could open up you again do the do the iframe thing right slap in an Excel document make it just iframe out here we go full Excel right inside PowerBI and fabric and then boom when you’re done I’m going to mark these tables as full copies, just push them right into the lakehouse. Done. And then I can pick them up from everywhere else. So there’s there’s I think there’s some opportunity for some tighter integration between those two experiences to make it a bit more seamless moving forward. All right, we are fully at time. This has

67:10 right, we are fully at time. This has been an hour of conversation around Power Query. Just a lot of like efficiency, optimization patterns that we’re seeing here, the old stuff, Gen 1 versus Gen 2. Really appreciate the conversation. This is super rich. Alex, thank you so much for your time today. really appreciate you being here on the podcast. It’s been super fun., for you users who are listening, thank you for hanging out with us. I hope you enjoy this episode. Our only ask for you is would you mind just letting somebody else know we’re talking about Power Query in depth in detail with the expert himself, the guy who writes the

67:41 himself, the guy who writes the documentation on the Microsoft site. So, I don’t think you get anyone who’s more knowledgeable about Power Query than Alex. So, we’re super thrilled to have you here., please share this with somebody else and let them know this was awesomely valuable. 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 us out a ton. And share with a friend since we do this for free. You like what Alex talked about? You want him to talk with us again. Or do you have a question, idea, or topic that you want us to talk about in a future episode? Head over to

68:14 in a future episode? Head over to powerbi. tipsodcast. Leave your name and a great question. And finally, join us live every Tuesday and Thursday, a. m. Central, and join the conversation on all of PowerBI tips social media channels. Thank you all so much and appreciate your time. We’ll see you next time.

68:57 Get out.

Podcast Info

• Find us on Apple Podcasts, Spotify, or wherever you get your podcasts
• Submit questions and topics at powerbi.tips/podcast
• Join the live show every Tuesday and Thursday at 7:30 AM Central
• Follow Mike, Tommy, and Alex on LinkedIn