New M Functions for Fabric – Ep. 273

Ep. 273 is a “tools get sharper” episode.

First up: DAX Query View in Power BI Desktop. The crew digs into why having a proper DAX scratchpad (with EVALUATE results, better authoring UX, and quick dependency visibility) changes the day-to-day workflow of writing and validating measures.

Then they switch to Fabric and a new Chris Webb post on DeltaLake.Table—a new M function that reads Delta Lake tables directly. The bigger story: Power Query is starting to look less like “the last step before a dataset” and more like a legitimate lakehouse integration layer alongside notebooks and pipelines.

News & Announcements

• Deep dive into DAX Query View and writing DAX queries — Overview of the DAX Query View preview, including how to write queries and inspect results inside Desktop.
• Read data from Delta Lake tables with the DeltaLake.Table M function — Chris Webb’s walkthrough of the new Delta Lake source function in M.
• Submit a topic idea (Explicit Measures Podcast) — Send a question or scenario for a future episode.
• PowerBI.tips Podcast — Subscribe and browse the full back catalog.
• Power BI Theme Generator (Tips+) — Generate consistent report themes without spending your day tuning hex codes.
• Mike Carlo (LinkedIn) — Fabric / Power BI notes and consulting learnings.
• Seth Bauer (LinkedIn) — Engineering, governance, and platform perspective.
• Tommy Puglia (LinkedIn) — Analytics and Power BI content.

Main Discussion

The throughline here is reducing the friction between what you build and what the platform is actually doing.

On the Power BI side, DAX Query View gives authors a first-class, in-Desktop way to test DAX, inspect output, and iterate faster. Pairing it with Performance Analyzer is especially useful: you can take the generated DAX behind a visual and run it directly in Query View, which makes debugging performance and correctness far more concrete.

On the Fabric side, DeltaLake.Table is a small API surface area with a big implication. If Delta is the common storage contract across Fabric, then Power Query (especially in Fabric-first experiences like Dataflows) needs a native, lake-aware way to read those tables—including Delta-specific concepts like versions/time travel and complex/nested structures.

Key takeaways:

• DAX Query View brings a DAX “workbench” into Desktop: write queries, run them, and see result grids without leaving the file.
• Generated visual queries become inspectable: Performance Analyzer can hand you the query, and Query View gives you the place to iterate.
• It lowers the barrier for constrained environments: built-in capabilities help teams that can’t rely on external tooling.
• DeltaLake.Table makes Delta a native Power Query source: M can now reach into lakehouse-style storage more directly.
• Delta features matter earlier in the pipeline: versions/time travel, metadata, and complex types aren’t just “back-end” concerns.
• Power Query is still a serious tool: it can deliver fast, repeatable shaping where notebooks would be overkill.
• Strategic signal: Fabric is standardizing around Delta patterns, and you should expect that surface area to keep expanding across experiences.

Looking Forward

If Delta is the contract layer for Fabric, expect more UI-first features that expose lake semantics (versions, metadata, complex types) so more of the workflow can stay in Power Query and Desktop—without immediately dropping into code.

Episode Transcript

0:30 good morning everyone and welcome back to the explicit measures podcast with Tommy Seth and Mike hello happy Tuesday gentlemen good morning good morning there it is today’s topic we’re going to jump into talking about an article that came out on the fabric blog if I had a little sound effect I would go yay and a little clapping sound because this this I think is the first blog article written by Chris web on the Microsoft fabric blog so we’re going to talk a little bit around the reading data from the new

1:02 around the reading data from the new function that now exists inside power query inside M you can now read Delta Lake tables or the Delta lake with a new M function which I think this is also very telling to the hand of where Microsoft is going with a lot of this Delta technology things and how they’re incorporating into every part of their product everywhere they see it should be an interesting conversation also Chris Webb awesome guy love his articles very detailed I’ve learned so so many things from him it’s been I love his stuff

1:33 from him it’s been I love his stuff anyways he’s so good so happy that he’s on the Microsoft team and pushing out extremely good articles like that speaking of other cool people on the Microsoft team yeah let’s jump into Zoe Douglas talking about she had a release on the blog for powerbi again it’s hard jumping between the two different blog pieces here so here’s the blog for from the powerbi side of things there’s an announcement being made around as of November relas release of powerbi desktop there’s now a public

2:03 of powerbi desktop there’s now a public preview for the powerbi Dax query view really interesting to see what’s going on there so reading through the article and actually playing with desktop a bit it allows you to write Dax directly inside parb desktop and you’ve been able to do that before but this is actually using like an evaluate you can see a table that renders beneath of what the Dax is doing there’s a whole bunch of other things here you have a better command bar you have its own ribbon on

2:33 command bar you have its own ribbon on the page so this is this is yet another icon that lives on the left hand side of your screen that you can click on and dive a little bit deeper in on the Dax what are your thoughts do you like it awe it’s awesome this is one of those product yeah go ahead no I was just going to say like the thing that pops off the the the first for me is when we started talking about or or have talked about how do how do you create your Dax queries right like first thing create a table like

3:04 like first thing create a table like not this sets up how you would want to like put together your measures validate that they’re working the way you would expect them and having this as a separate experience with the hooks that it does and I I won’t explain the whole article so I can let Tommy talk but like it the the hooks in it it’s very seamless from a Blog perspective so look forward to like your interaction with it I haven’t played with it yet which I’m tempted to throw away my meeting schedule today and just go play but easier said than done true

3:36 play but easier said than done true statement true statement it’s definitely one of those features the first five minutes you you start playing with you go how is this not a thing before because you’re right it’s more that’s a great Point Tommy that’s a great point it’s much more than just dax’s queries if that was the only thing here still incredible but the fact that from a I don’t even want to say a developer point of view but just from an advanced author point of view I can look at my dependencies for a measure very easily and add that all in from a defined point of view the the feature set

4:07 point of view the the feature set that they released stage one or alpha or whatever we’re calling this is already great just the ability alone if the if the first and only feature set here was just to be able to run and evaluate is already something that I’m using consistently it’s not taking away from my my favorite tools in the world my external tools but for a lot of organizations external tools are are not a thing or there’s a lot more limitations there a lot more barriers there the fact that this is built right

4:37 there the fact that this is built right in for either a new user or someone with maybe not not privy to all the external tools is something that’s going to help their own Dax it’s going to help also their own accuracy and their own workflow I like how you’re thinking about that Tommy because that’s one of the challenges I’ve had in general working with visuals and writing measures and dropping them to them to your point earlier Seth if you can’t get the visual to run so at some point in time you get to a point where I’m comfortable with what’s going on I

5:07 I’m comfortable with what’s going on I know how to build these Dex measures and you don’t really build like a table and then transition into building the visual you just build the visual with the measures and things that you have but there are times when things go slow and one of the features that I really like about this new tool is when you’re inside the performance analyzer so if you if you’re on a page inside desktop and you’re looking at the performance of the page which that is a specific window that you can have open you can refresh all your visuals and you can then pick

5:37 all your visuals and you can then pick out from that there’s now a new icon in the performance analyzer performance analyzer that says run in Dax query view so you can grab a visual see what it does go run the automatically executed Dax directly inside the view and then it shows you it literally writes it out for you correctly with the proper syntax and if you do just something simple it it runs a lot of code I’m I

6:07 it it runs a lot of code I’m I just did a simple bar chart with a couple like a legend and some quick measures in there yeah and there’s like I’m looking at here the code to write that single table is 184 lines and and it’s a bar chart with like four fields in it that’s it so there’s a lot of code being written there and I think it really helps a user to go back and say what is actually occurring here let me understand what the Dex code is doing

6:38 understand what the Dex code is doing because this is the part that you couldn’t do earlier which was sometimes you need to dissect the code yeah yeah and and that that point right there even I see a lot of potential here in how they didn’t just make it what I love about it is they they didn’t just make it the separate thing right there the flow here we through something they call code lens or whatever like quick queries evaluate to go from your model where you may already have queries where you may already have something that’s just not lining up and then have a seamless

7:09 lining up and then have a seamless experience to pop into this experience this this pain right and then also make your chain push your changes back into the model like that it’s very well thought out and and I I would imagine this becomes like the way I I manage all my measures or interact with them in the in the tool and and that’s a really big Point here cuz like I said the Dax query itself is incredible but the fact that it’s dare I say more seamless than an external tool oh yeah dare say it you dare say it no

7:41 yeah dare say it you dare say it no that’s you would expect that though you want that to be what you want but you’d also expect they they have this Dax career that’s completely separate even though it’s in the model no I think the the biggest win here is it is part of your normal workflow it is already integrated in the model you’re in yes so and that to me is one I think part of for firsttime users or intermediate users get used to this get comfortable with this but even for ourselves does this make more sense than using DAC studio in a lot of

8:13 studio in a lot of circumstances now I don’t know if you guys have noticed this but there is full what whatever you’re seeing here there’s a lot of things that mirror what you’re seeing in Visual Studio code so there is command prompts for control shift p or Control Alt control what is it Control Alt control control alt P hotkey guy here he comes here we go hotkey guy is in here now but when you’re doing this there’s a command pallet there are so many commands move cursor below move cursor to end

8:45 cursor below move cursor to end select and find match find next match control D there’s a whole bunch of really awesome visual studio code commands that you use to navigate around code all of that now exists in this editor and I think some of the things that are going be underrated PE that are here that I want to point out are there’s a format query button so you can make changes and it will reformat the query using a formatting pattern now I don’t know where it’s getting its formatting stuff from I wonder if it’s using the SBI API I probably doubt it

9:17 using the SBI API I probably doubt it because it’s it’s going to be at scale it needs to do something really big to do that but however it’s very nice to see that there’s there’s like hundreds of of quick commands that are in here that you can use to man iate this window so people can actually see the dacks you can Fold level one by using control K control1 control K control two so when you have nested elements it will fold them together and and Shrink them down for you it’s amazing what they can do here so very

9:47 amazing what they can do here so very interesting to see what’s going on yeah in this space real quick with the the options I remember when Visual Studio code when I started using it I hated the the command pallet at first I was

9:58 the command pallet at first I was incredibly intimidated by it with all the options I liked being able to select it I didn’t know what was available for those who’ve never used Visual Studio code and now this is your feature set your option set it’s something it’s not a large learning curve don’t feel too intimidated from it because I know that happened to me with vs code it’s like this is too much but now that’s just I I expect every application I have to have a command pallet so and other things I’ll just randomly point out here use the ALT key to get

10:30 point out here use the ALT key to get multiple cursors is a dream come true I multiple cursors is a dream come true that’s just oh it’s so good this mean that’s just oh it’s so good this this is this is not a brand new business user tool you can this is this is for the professionals that are really in and doing data modeling I was recently on a phone call with a client and we were talking about some things and what came to mind was they’re like well we want to build reports we kept talking about reporting report reports harbi desktop isn’t as much as a report

11:01 desktop isn’t as much as a report building tool is it it does do that but it’s actually a data modeling tool and actually reminds me of an article that comes from sqlbi that this is really pushing into what Microsoft I feel like should have been pushing into all along is it’s more of a data modeling tool that brings together tables and queries and helps you tune you tune Dax this this is what this tool needed to be from day one it just they were just now getting to it I knew you were going to be a big fan right out of the

11:32 going to be a big fan right out of the gate with this with with this this feature enablement when when their lead in Blog has short key shortcuts and short keys for you you are you all the right buttons all in I don’t want to I don’t want to use my mouth less click there’s not a lot of clicks to run it there’s not a lot of clicks to make it go do its thing like that’s what you want that’s a sign of a good feature inside the tools there’s less clicks now to go do something you’ve never been able to do before but it’s simple you

12:03 able to do before but it’s simple you able to do before but it’s simple the best part F5 doesn’t refresh know the best part F5 doesn’t refresh the browser because it’s not a browser it’s on your desktop still so want to say that actually there’s actually a really good question that came in from Natalie was she’s just starting to learn daak studio should she just use this instead oh this is a great question there’s too much overlap to say you can’t one or the other well I was this is a good this is a good question because I think this really helps you point point at where is there is there any

12:33 at where is there is there any shortcomings that you see between what Dax Studio does and what this one does does so my initial reaction is I don’t get to see some more of the technical pieces of how the query is executing I see it run I get a little success or not success and it tells me an output how many milliseconds did it take to run so cool that is very similar to what Dak studio is doing however Dex Studio does a little bit more cury planning pieces so

13:03 little bit more cury planning pieces so it shows you like okay when you have an efficient Dax thing you’re going to the storage engine or the compute engine is that right the SE and the CE right okay so when you’re jumping into the Dax and you’re looking a bit more in detail as to why was it slow I feel like daak Studio still has a bit of an edge against what this is doing this is more like hey this code ran you can now pick apart the code can run Snippets of it

13:33 apart the code can run Snippets of it which is fine but you’re not really getting like the tuning performance tool set that you would need to optimize more of the DAC statement or the visual or whatever you’re doing in the measures does that make sense no I’m I’m 100% with you there there’s a bit of this where it’s like Dax Studio diet version because when I’m trying to run multiple Parts the compute engine the verac analyzer which is also in DAC studio if you’re on the intermediate to Advanced side I you would completely still want to become an expert in Dax Studio I

14:04 to become an expert in Dax Studio I don’t think this takes away from that at all nope no if anything there’s also there’s also a lot of other things that you can do with d Studio relay connecting to the model seeing what’s going on with size spacing like even beyond that but yeah I like the I agree excuse me the optimization aspects definitely are are very very rose and well done in daak studio but having said that with this new preview feature

14:34 said that with this new preview feature I would I would yeah start getting comfortable with it in the desktop because it’s going to be like the de facto place that you’re going to be able to see whether or not your measures tie out the way you would want them to and it is it is a much better experience than trying to do that with your right click and just creating it in the the code panel the code snippet you the the code panel the code snippet so I I think it’s going to be a know so I I think it’s going to be a natural progression in the tool as you progress in Dax as you progress in into the the more nuanced aspects of

15:04 the the more nuanced aspects of it then Dax studio is still a fantastic tool and I don’t I I think we’ll there there’s not replacing it and I don’t think that that’s what they’re doing here they’re just making it more I think straightforward for us to understand and make sure that what we’re what we’re building from a modeling perspective and queries is tying out the way we would expect it to which is great another thing I’ll point out here too that I think deck Studio has another Edge clearly it’s for the developer of things but I think you pointed this out to Seth I want to

15:34 pointed this out to Seth I want to reemphasize this when you’re tuning a model you’re looking at which tables are the largest where is the column that has the most amount of unique records in them there’s not there’s not like a simple amount of there’s not a way of looking at the verac analyzer so verac analyzer is like a thing that comes with that studio it shows you all the tables the sizes now I’d have to imagine since this is a cube and and you are writing dacks against it you could probably write the DMVs or write views inside this Editor to output tables of data of

16:05 this Editor to output tables of data of that but then you would need to know exactly what you’re writing it’s not like a dragon drop experience so the the experience that you get I think with daak Studio still is you get that really nice rich editing piece as well another another fashion to this is if if you’re learning daak Studio learn it all the way and then like everything you learn there is that’s a good point transferable right view you’ll just notice that there’s these these differences I think that’s actually a really good point there Seth if I had to

16:36 really good point there Seth if I had to give you recommendations start with Z Studio because this is all a water down version of what that is already so if you already know you need to get to that higher end tool yeah do it there that makes a great a lot of sense I agree with that a lot any other final thoughts on this blog or article here here excellent there’s a lot of really interesting things coming out from desktop so there’s probably pretty some some more interesting things that we’ll be seeing here in the future but we’ll keep tabs on the news coming out for all things powerbi both

17:07 coming out for all things powerbi both on on both blogs on powerbi blog and on the the the fabric blog there’s a lot of I feel like right now when fabric came out initially I was overwhelmed for whatever reason the end of the year they must be just ramming in a bunch of features just before the end of the year because again I’m feeling overwhelmed and like oh my gosh they’re changing they’re adding so many things it’s hard to keep up on all these new little additions on all the features well that’s what this podcast is for it’s a bit of a support

17:38 is for it’s a bit of a support group it is a support group we had a little break I’m sure they’re not going to do the release in January then it’s going to be the big conference with 18, 000 more features come March so got a little break now I think I feel like I’m seeing a ton of articles I don’t know if about you guys but I’m feel like I’m seeing a ton of Articles just around all the things you can do with notebooks it feels like notebooks is this new untapped Arena that no one has been playing with it is cool kid yeah and it does so many things this thing called

18:08 does so many things this thing called semantic link is now out Phil C Mark is now blogging about hey you can go now create a visualize your powerbi refresh using semantic Link and he’s building a a table in a notebook and then he’s turning it into a denb visual and he’s like loading denb visuals inside a notebook and rendering them inside the context of a notebook with a A Time line and all this it’s incredible what people are doing in notebooks now I don’t want to straight too far but this is a really good point that happened to me last week as cool as notebooks are and I am all in

18:39 as cool as notebooks are and I am all in in trying to do it every day just loving all the features there that being said this should not take away what the benefits and what’s data flows and power queery can still do incredibly well I was noticing I was trying to do everything in a notebook I’m like wait a second I could do this in two minutes in power query if I wanted to maybe that’s from the skill point of view but it’s also the UI and really again data flows are not just a beginner level tool and power query is not just a beginner level

19:09 power query is not just a beginner level tool however you want to put it however you want to put it it may look like it but the processing and what the capabilities there everything has a place and it doesn’t mean that notebooks should take a place of everything there are some things data flows there are some things that power query does better than notebooks there’s a lot of things notebooks do better but it doesn’t mean there’s one that encompasses all and that’s been my realization the last probably like 10 days yeah this will be

19:39 days yeah this will be very so for those of you who are listening to the podcast right now Tommy was talking and I kept smiling really big here and and as you get as you’re so involved with these tools and what’s happening I’m smiling because Tommy’s you’re comment telling around power power query is not an intro level tool

19:57 power query is not an intro level tool which I would agree with like like it’s it takes a little bit of time to figure out the patterns and how do you make it work but it is more simpler it is simpler than what you be doing inside notebooks all UI yeah it’s it’s less like a extreme the barrier the barrier to entry is lower what what is that curve and I know you see it probably in Gil revie’s book right where yeah like you can do 70% you need to in the UI right and then all of a sudden then it ramps as opposed to you

20:27 a sudden then it ramps as opposed to you have notebook go for yeah exactly just here’s a cell go at it so so Adrian asks in the comments here which I think is another good comment here and I I’d like to comment on this one as well I’ve seen a lot of SQL code being placed inside notebooks why not just create a SQL database and the objects and call them instead the great point but I think in my opinion here is the reason why you do you find people using a lot of SQL people know SQL it’s a very universal language very easy to understand it’s extremely powerful it can do so many

20:58 extremely powerful it can do so many manipulations on things so I I honestly my opinion if I’m going to write SQL I would prefer to write it in a notebook rather than an actual like SQL Server because the way the queries run it’s easier to like have multiple commands with different lines of code of SQL in them so for me logically I just really enjoy the development experience of a notebook in general that’s just the experience that I like that aside there’s a whole bunch of other technical challenges around like well if you’re doing SQL what kind are you doing are

21:28 doing SQL what kind are you doing are you using SQL serverless where’s it come from do I want to pay for another machine or am I already going to use the compute from powerbi to do the notebooks there’s a whole bunch of other I think considerations that go along with why you wouldn’t want to just run SQL in a SQL Server but I think ultimately at the end of the day whatever solution you’re coming up with you want a system that has less things in it whatever that may be so when you’re going from raw data to a powerbi data set with something rendering a report the less

21:59 something rendering a report the less hops I can make with the data the more efficient the faster the process will be the easier it will be to run so yeah I think start with Simple Start simple yeah I I think I would answer in a different way from the standpoint that I I like what Microsoft has done from mirroring right like aure SQL SQL database like you don’t you can leave the data there and pull it into a fabric environment and get use out of it join creating it adrianne’s point is

22:33 join creating it adrianne’s point is like so you’ve seen a lot of SQL code why not create SQL database objects and call them instead and then follows up with book creating SQL database objects is about reability maintenance I I think where you’re a fan I’m a fan of Delta tables have been for a very long time is because like you’re you’re you’re talking about large data sets for one yeah yeah for two there’s it’s a completely different framework by which we’re interacting with data underneath the covers the Fantastic thing about fabric is we now have you user interfaces that plug into

23:05 have you user interfaces that plug into those same backend structures and ultimately like the things that you get with Delta tables that are I think a much better experience than than SQL is the the cloud scaleability because it’s all it’s all predicting based on spark right so you you can infinitely scale these are just files it’s also there’s some really unique things related to like reliability inversions it is extremely quick for you

23:37 inversions it is extremely quick for you to interact with a table and I cannot tell you how many times like you go through a massive load and all of a sudden it’s like what did somebody just like took something my table where’s my table oh sorry yep let me just revert and it instantly reverts back to the the previous file sets right so the the underpinings of how how all that it’s operating is completely different from a data storage indexing like Delta tables are individualized Park files right that

24:07 are individualized Park files right that then have versions applied on top of them right and then you have a pointer to the right files and it just works seamlessly you still get the same asset transactions Etc so being put into environments like data bricks or now fabric where you have notebooks and you’re interacting with them via will code completely different underpinnings right and and that’s where I find a lot of value you get a lot you get very used to how notebooks operate and and

24:40 used to how notebooks operate and and and frankly like working between data engineering teams and front-end teams is very seamless because you can have somebody pop in write a python function that executes at the beginning of the notebook and then your next things are in SQL and then or or pop Hal because the function like inserting or creating something of you cleaning optimizing whatever the case may be it’s very flexible and I think that to me is the biggest difference I’m a SQL Fanboy too right like I came but

25:11 a SQL Fanboy too right like I came but in terms of just those those I think features of it have have sold me especially as it relates to just the performance and and things at volume and I think that’s the the important part is when you start to ramp up things operate much quicker much better you have a lot more flexibility in clustering cluster sizes types of clusters you can use with different workloads etc etc and one other note here I’ll tack on to the end of this conversation here around that same point is yes there’s

25:42 around that same point is yes there’s two kinds of main workloads that I think especially if you talk about what datab brex is doing and I think Microsoft’s going to mirror very much the same experiences you already see it in their code today or how they laid out the UI there’s this idea of data engineering with notebooks getting data in moving it around creating tables right so you’re you’re essentially in my mind even though I’m using a notebook it still is SQL it’s still a SQL Server Like The Lakehouse is the server the tables are inside the server so you can you can roughly use the analogy here of the same

26:13 roughly use the analogy here of the same stuff mentally like the mental model right and again I’m thinking from like the SQL Developer who’s used to like I go I got to go connect to my SQL Server before I can see any tables in the database right all that stuff so that being said looking at the Next Step Beyond after you’ve collected the data now what and this is the next portion that I think a lot of organizations are going to start getting to is they’re going to build piles of data they’re going to go collect everything load them into tables the

26:43 everything load them into tables the next logical step is let’s go do data science or let’s throw some smarter math at our tables and you need a tool that can do that SQL by itself can do it in certain instances but I’ve worked on projects where we’ve loaded M and R sorry R into a SQL server and ran R inside the server it just created all kinds of headaches it felt like it was just a bolt-on thing and and and felt like okay that’s weird

27:15 and and and felt like okay that’s weird it it didn’t never it didn’t run as smoothly it was store procedures it was it was very clunky but then when you move over to the notebook experience with Delta tables just you being familiar with it I just could say I could say man back in my sequel days there just a seamless experience I store procedures calling store procedures and job jobs and hey it’s all it’s all all of the same types of things you’re taking actions you have orchestration you just do it in different ways well this also brings up a great

27:46 ways well this also brings up a great transition to our main topic today so our main topic for today is really around going into the deep dive around what what just came out from Chris Webb which is let’s talk about reading data from data Lakes so this is clearly a direction Microsoft’s going everything is touching Delta tables now and this is a great transition which we didn’t even mean to have it go this way but let’s go further let’s talk about the main article for today I’ll put that here in the chat window as well so here’s the main topic for today talking about the new Power query functions that are now

28:18 new Power query functions that are now going to let you read Delta Lake tables with a new function deltal lake. in the M functions so not very often that we get a brand new M function maybe they occur and I don’t it’s not a blog post I maybe right yeah so this was one that I think was very appropriately done by Chris web again awesome and again congratulations Chris first blog post on the new website the new blog site so we really appreciate you writing this out and I think the article talks it’s high level on pieces of this as well

28:50 high level on pieces of this as well I’m not so sure I understand the utility of this function when looking at your local machine M because it does require you to have a Delta table created on your local machine in order to test things if you want but I really feel like this function makes a lot of sense when you’re talking about oneel somewhere else where you’re trying to connect to that one L element and use that to go get those tables of data and load them into Power query so initial thoughts any reactions to the this

29:20 thoughts any reactions to the this article article initially so there’s a lot more than just your local folder there’s actually connecting to the ad ADLs Gen 2 API as a as a URL and putting that to a Delta L to table I know that was my maybe that was my point was like I think you’re I think you’re gonna use that more than just the local version now if you have the one leg on your computer sure I think for me the bigger I think if you look at this from a bigger point of view rather than just the function itself

29:50 rather than just the function itself this goes to me back to usually our past

29:54 this goes to me back to usually our past seven years power query has been near the end point it’s the end of your of your journey in data analytics if I’m using power query there’s not a lot of other places we’re going to go besides powerbi the fact that they’re now having this source which I imagine this is going to be more with data flows not so much powerbi desktop that are going to be using the Delta lakes that we’re getting to a point now where power query is becoming more to our earlier Point like a notebook where it’s part of that ETL part of that true integration

30:26 that ETL part of that true integration side I can connect to the Delta Lake I can or a a Delta table and the feature set here is not just necessarily only connecting to a table I can handle time history of a Delta table I can look at nested structures I can look at statistics if I need to of that Delta table all these things to me are pointing at a much bigger picture here where power query is becoming upgraded desktop is also becoming not so much like the end route of what we’re doing

30:58 like the end route of what we’re doing because this this is significant this is really significant on our normal workflow our normal con what our conception of what power query is again a bit of a background here this function just simply allows us to connect to Delta tables but allows us to use the feature set of Delta tables not necessarily just convert it to a table that works in power query I agree with that one there’s a couple interesting extra quote unquote options that go with this function that I think are very relevant here one of

31:30 I think are very relevant here one of them is this thing called nested structures and this seems purely geared towards a Delta table can absorb Json structures and have complex structures inside a column power query does not like that frankly it it loves like it’s got to be like a text thing it’s got to be like a single value it does not like complex nested internal structures of like a Json object right you can you have the the the keys that would define like the row The Columns of

32:00 would define like the row The Columns of the data and then the values of those keys Define the rows well now they’re giving you a nested feature and in there you can Define the max depth of those nested features so as you think about like a Json structure that comes back there’s nesting that occurs inside this object there’s another object inside that object there’s another object and you can now specify that by using this and I think it’s also good to note here that the by default there’s 32 levels of depth that you get so if you

32:31 levels of depth that you get so if you have a really gnarly Json structure that goes very deep if you have more than 32 nesting nesting structures you won’t be able to go any deeper than that but that also means you’ll you’ll prune data or you’ll cut out data if you have something that’s deeper than that or if you change your max depth to a number it will automatically lose data inside that initial read so I think that’s important to note but maybe that’s why this function exists specifically because like this is this is a new data structure that power crey doesn’t handle very well in the past so maybe this is

33:02 very well in the past so maybe this is going to help out a lot there that is significant because when fabric first came out in data flows gen two part of the workflow that I tested out was using data flows gen two with one Lake in a lake house and it was incredibly slow to try to integrate those files and in a sense push them to the lake house which it didn’t work well and it’s like okay this is not what Gen 2 is supposed to do this is solving that problem that’s solving that hurdle that we’re having where power query just does

33:32 we’re having where power query just does not process files in a folder very well and it since to get that nested table to your point Mike they’ve realized that and here’s their solution or at least their gen one solution of that I agree with that one any other features that stood out to you you talked about time travel to it’s to it’s there there I it’s you can get different versions of the Delta L table which is

34:03 versions of the Delta L table which is fine and this is the other thing they had fabric had a Blog a few probably like a month ago about time travel you can show the time history in the warehouse so and and these are all solution they’ve thought about this in a more holistic view not just hey we need the ability just to connect to a Delta lake table it’s like well what are all the use cases someone’s connecting to the Delta lake table and any in any feature not necessarily power query well let’s make sure we incorporate that so this can be the workflow this can be the main workflow

34:36 workflow this can be the main workflow of connecting and integrating with Delta Lake tables and I think that’s what again that’s what they’re trying to do here is how can we make Power query on par with something like notebooks or the pipeline to be that integration point this is this is one of the reasons why I’m really excited about this blog post I’m excited about it is because there’s so many things that the Delta table gives you out of the box by default all these cool features and now we’re going to start getting access to these things inside

35:06 these things inside desktop outside from the fact that I don’t think I’ll ever connect to a Delta table on my local machine I’ll probably always go find it in one lake or some ADLs Gen 2 storage location but I think this makes a lot of sense one challenge I’ve been trying to face here as I’m thinking through how to build these things one one area that has been in my mind that I’m trying to figure out here is the guidance for Microsoft you think about like a deployment pipeline of of things there is this pattern of Microsoft expects you to build

35:36 Microsoft expects you to build three separate workspaces that all have different environment names one of the challenges I’ve been looking at here was like okay I have a Lakehouse in each of these environments Lake housee for Dev it’s probably named the same so again I would assume you would name them all the same so it’s Lakehouse data Lakehouse inside your Dev environment and your workspace inside your test envir enironment and in your pride so you can use powerbi deployment pipelines to move artifacts between all the things but one thing that I’m I’m again trying to write my head around here is how do you

36:06 my head around here is how do you parameterize the lake elements and so that the notebooks or the or the you that the notebooks or the or the if you’re if you’re doing some know if you’re if you’re doing some interesting naming conventions on how your environments are built what is the thing that you can do to parameterize your data Mark your your semantic models so that every time you’re moving you’re getting a different Lakehouse name or you’re moving that through I’m assuming you can repoint everything like normal like you would a data set and a report repoint as you move them through the pipeline I just need some more testing around this because I think yes I’m very Pro like

36:38 because I think yes I’m very Pro like let’s just build everything in one environment but I can’t be so naive to think it’s all going to fit in you’re not going to build everything PR it’s not going to be just one environment you’re going to want to build multiples of these things and work your changes through a pattern of a flow here so I I’m very excited to see that as well because it’s I think this this new function will allow us to parameterize more of the artifact and things we’re making in the lake housee we’ll be able to parameterize a bit better the data sets ah shoot I keep saying data sets I still haven’t transitioned fully over to semantic

37:08 transitioned fully over to semantic models you’ll be able to transition that the semantic models between each of the environments as well which it’ll be interesting to see who’s going to do that I’m going to lean on John kky for that one and like John you need to write us a Blog call if the Internet calls John kiry to the stage and can the Internet calls the internet calls is that how this works you can just like say it into the internet and then the internet knows how to get someone will ping him and and he gets notified and there will be a blog post in the next week yeah ky krki ky

37:39 next week yeah ky krki ky no you s i i summon I summon the powers of Ki to do data Ops on top of whatever this is any other thoughts or things that roll up here as you as you look at this if this becomes maybe the question for both of you for for Seth who I don’t want to say his power query pessimistic or leans that way but there’s obviously in Seth’s mind if I were to assume that in the order of operations of features and

38:09 order of operations of features and tools and languages of Enterprise ETL power query is probably not going to be in the top five of what it can normally do I’m gonna make that assumption here I’ll make that General assumption in for most of the things we have data bricks you have synapse obviously we pipelines power quer has never been that main feature if they continue to adapt and continue to grow this connection feature and this feature set can you see this becoming one of the

38:41 set can you see this becoming one of the main areas or the main feature sets of a workflow for ETL especially at least in in the in the realm of fabric especially who are you asking well there’s two of you so ask I was gonna I was gonna let Seth go first I have an opinion maybe but I’m I’m kind ofous what you’re gonna say Seth I I would say this the vast major so have I tested

39:11 the vast major so have I tested everything all the time no however the the reasons I would argue that power query isn’t isn’t the goto or de facto Enterprise tool for me is because it doesn’t perform as well or isn’t in the locations that I would need to plug it in to find a ton of value out of it now is it being added to those different tools yes is

39:41 added to those different tools yes is the performance there the same way that it is in other ways I ingest large volumes of data no if if it starts to perform the same and it’s being

39:51 perform the same and it’s being integrated in all these areas and I can do things like this I think that’s a very compelling story for when we talk about how something graduates from you about how something graduates from a ad hoc level or even potentially know a ad hoc level or even potentially business where we’re ingesting processes that they built and just plugging them into our Enterprise ecosystem it it’d be much easier to do that and I’d love to be able to do that as opposed to like look at somebody’s power crew and be

40:21 look at somebody’s power crew and be like okay what we need to do is extract this move move this stuff that you’re doing here back one layer if if power query is a viable ETL tool back here and performs just as well as all of the other ways in which we can do things it like it’s not like I’m against Power query I got we got Alex in the chat I’m not gonna like I love powerquery oh yeah you better watch if only if only because I can go to conferences and listen to Alex talk about powerquery because we

40:51 Alex talk about powerquery because we didn’t even have that internet summon justed there’s only one thing that’s better than writing mcode and that’s listening to Alex talk about writing listening to Alex that writes M code that’s the so like it’s it’s not like I said it’s not like I I don’t think that it is isn’t powerful I do see value and I use it all the time it’s just not in not in the largest workload levels that we have it doesn’t perform as well and

41:21 we have it doesn’t perform as well and for the record I agree but yeah and that could be because of the tools that it’s in that there are limitations but like I said the there we’re we’re in a new ecosystem where Microsoft is presenting us with the comfortable tool sets that we are familiar with from the front end but they do not work the same right right like we’re writing SQL you have a SQL Warehouse that’s writing to Delta paret

41:53 Warehouse that’s writing to Delta paret that’s not a backend SQL Server right like like there are things that that are being introduced I think that could could definitely change how these tools as we know them in the places that we see them interact differently oh I’m G to go back to your comment Tommy around like when to use something and when not to use something I’m I’m going to make an assumption here that if we’re pulling data from a Delta table in general we’re

42:26 data from a Delta table in general we’re pulling a star schema like someone’s so the idea here is like this is this is roach’s maximum to the nth degree like if I’m already technically enough that I’m making Delta tables I’m assuming the data engineering that we’re doing is at a level of we understand what Transformations need to be made we know how to get data shaped we’re going to basically build everything that we need here’s the fact tables that we need here are the dimensions we’re going to go get but all that abides Upstream it’s in it’s in another place and so I see this

42:58 it’s in another place and so I see this as being a very valuable feature for okay I’ve already got my star schema I now need to get that into the model let’s just do that portion right to me that makes a lot of sense so if you’re going to use this step and what Seth point was saying was I’m going to then rip apart a table I’m going to go open up some Json inside that table structure if I’m going to do all these other Transformations on top of it there’s in my mind there’s two things that need to occur here right one of them has to be you have to let power query interpret

43:29 you have to let power query interpret those Transformations like selecting columns removing data picking out interesting things power query has to be smart enough to fold those Transformations and push them back Upstream so I could Envision a very similar pattern like you see when you do SQL queries like here’s the native SQL query that was run to go access that data right things I think like joining two t like if you did this and you joined two tables together it’s going to be expensive because you have to load both tables into memory and then do the

44:00 both tables into memory and then do the join to them so like that stuff would be things I would want to look for those in power cery and say let’s just not do them in powerquery let’s instead do them Upstream somewhere else so in this whole fabric ecosystem I have really felt like nothing in powerbi has changed everything from a semantic model forward is the same what fabric has done is it’s opened up another whole area of the back end that we’ve never had access to before to before Delta tables pipelines like all this other really rich stuff which that is

44:33 other really rich stuff which that is where you should really be doing the heavier data engineering I think if if you’re talking about a growup strategy of powerbi so obviously this function offers the ability to plug into Delta tables a bit deeper and potentially better what is what in your eyes what’s the difference between this like and the way I from the powerbi perspective like connect to spark via SP or Microsoft Azure Delta table connector oh that’s a great question I think that my perception here is this

45:03 perception here is this removes that middle compute layer right so you could never go read a Delta table directly so if you think about like the different compute engines you need to go read stuff if I’m reading Delta tables the whole reason why we built synapse in front of powerbi was because synaps had this thing called SQL serverless SQL serverless was able to go read the technology of the Delta tables and so I could say oh I’m going to create a bunch of Delta tables in data bricks or whatever there’s 100 tables there now how do I read them powerbi

45:34 there now how do I read them powerbi desktop had no way of actually reading what was the back end like what was in those files like in the Delta tables so now this function handles a lot of those lightweight those scenarios of like connecting to them so now I don’t need SQL server in the middle I don’t need synaps I don’t need a datab brick SQL endpoint to turn on just to write SQL against to get the data in the so thisbook this this function then is only for Delta tables that live

46:06 for Delta tables that live in the Microsoft ecosystem or on one l o this is a good question I don’t know I don’t know it could be too right well what what cluster are you using you can’t you can’t just plug into something without compute no but that I’m saying is this that’s my point though my point was you had to have compute before this function moves that compute thing away from whatever you needed previously so if you so let me just does it though because like if if I’m looking at let

46:36 because like if if I’m looking at let Azure storage data Lake fabric workspace okay so if I’m on a fabric workspace I already know I’m part of a capacity that I’m using something some but the but the capacity compute is coming directly from the the vertac engine like it’s it’s vertac that’s now because the function lives in vertac the compute is coming from ver pack and that’s why it says here in the article data Lake Gen 2 you can still use there’s no you could literally build a Delta table in and also this works on desktop right on desktop I don’t have

47:06 desktop right on desktop I don’t have any other spark running you don’t need another engine on desktop you isolate all that away and all you’re focusing on is on desktop it’s just the files and then verac is directly reading those files and un knowing how to read them so my opinion here is I think this simplifies your pipelines you no longer need this is a huge implication like this is Major big deal here you don’t need another compute to read the Delta tables which is again I think a very

47:36 tables which is again I think a very smart directional move for Microsoft it would be incredible they put that in function I’m looking at the documentation and I think they still need to fill out a bit but according to the documentation not just the blog if you have a Delta lake table you can return it it doesn’t say only in a in this certain capacity or only in a data lake or only in Azure it simply says Delta lake. table Returns the content of a Delta lake table I think there’s still some documentation on the limitations

48:07 some documentation on the limitations there that need to be filled out since this this is actually this article came out or the the documentation on Power query docs so to your point it potentially could I I’m with Seth there’s I I find it incredible if one function allows that conversion of any Delta lake table it’s just a file but that’s a thing though it’s just files you’re only reading flat files the thing that’s interesting here is you need to have something that understands

48:37 need to have something that understands the Delta format sure can read the log the log then shows you what partitions to go read so Gard beri I to go read so Gard beri the very beginning of the article mean the very beginning of the article it says or sorry say again Gart brle brle sorry I always I’m bad with names so Gart built a version of this that was a community version of it he read the Json file he was able to rip through the partitions he did everything in the Verte Pac engine so I know his solution 100% was flat files reading flat files and getting to the Delta

49:07 flat files and getting to the Delta structure and loading the parquet files into vertac engine without any other compute so the fact that they’re alluding to his article is an indicator to me that like yeah this is a standalone a vertec engine only compute things now to your point Seth you’re always going to need some computer to read them I’m just saying you don’t need a SQL server or a datab briak compute anymore you can now just throw tables in a in a your dat L Gen 2 use this and now you don’t need to stand up a SQL server

49:37 you don’t need to stand up a SQL server or have a datab bre SQL endpoint running anymore to load those tables it will just work in Import in desktop Bro think about that with some

49:48 desktop Bro think about that with some of the workloads that are happening I’m just telling you that’s yeah all right I can translate his sess face right now I know his mind is being blown as we speak no no no it’s it’s not that easy it’s not that easy that’s what it’s not that easy it’s not that easy that’s this is the point I want to make I think this is a huge moment this is why I was like we got to talk about this because I think this is a major a major thing okay so the major things because I I’ll go back a bit because when I asked you guys the initial question of what do

50:18 you guys the initial question of what do this change there were two questions here but I’m very intrigued that you guys took it two different way I’m sorry as I’m thinking like large scale how’s this going to work I see I see in the blog article if you have a folder on your local BC with the Delta oh yeah man I just keep pares on my local all the time I time I just machine yeah no no no thank you yeah which for Seth I think is confirming every all his beliefs but

50:50 confirming every all his beliefs but I think there there’s two implications here at least from where this can go and I find it very intriguing that you guys took it both directions that I initially thought where there is Delta L tables function as in part of the ETL process on fabric in lak houses which is the way Seth took it and then there is the Delta tables as part of a semantic model process which is the way that Mike initially took it when I asked the question are those the two real use cases here I’m thinking scenarios of

51:22 cases here I’m thinking scenarios of where is this the most useful or where would this actually be part of a production workflow it’s either going to be part of your data flows gen two to push in the lak houses or it’s going to be pushed into a semantic model I would I would agree with that statement because you’re gonna what you’re going to do is you’re going to have like a raw layer of data or a bronze layer of information you’re going to need to go hey I need a data flow to go pick up that Delta table right do something to it maybe filter it do some information to it and then go to the

51:52 information to it and then go to the next land it back down again I need to write it back down to the list Lake right so we know data flows Gen 2 already has the capability of writing Delta tables in Lake it’s by default there which means the data flow engine already understands it what this is giving us this is adding the capability to the semantic model the verac engine that’s going to go read that stuff again and I think Andrew asks a great question here if we’re now using this this could be used as an import model to go access those data tables will this be better than using the Lakehouse SQL

52:23 better than using the Lakehouse SQL endpoint to import and my answer I think is if you have already done the Transformations if you already have facts and dimensions you don’t need SQL anymore like you don’t need that endpoint to run if you have everything shaped and ready to go and this has been my point all along I saw this really early on and I was like I don’t like having a SQL Server running in between whatever my data stuff is doing and what powerbi is doing because I don’t want to pay for yet another machine to run just to go Access Data

52:55 machine to run just to go Access Data that’s already stored in flat files on some system to me this is just Microsoft catching up to the Delta format and now I don’t need that I can then you can still use SQL you can still create tables you can then write them back down in Delta formats but now I don’t need to have a persistent SQL engine on all the time just to load the data to the models and I think this has been a challenge for a lot of organizations and this is a very in my opinion this is a very traditional way of thinking oh we have

53:25 traditional way of thinking oh we have to put everything in SQL then the SQL is available then I can go run my power AI against it and go get the data and I’m saying that’s an that’s an old pattern there’s there’s new patterns evolving now and this is one of those new patterns you don’t need a SQL compute anymore anymore or and to do Delta you need a spark compute available to run that now that Microsoft’s actually building this into Data flows which is not spark and now into semantic models which is verac also not spark now you can just read the

53:55 not spark now you can just read the stuff this is great this is this is great great news sorry I guess I’m too excited about this I I think you’re making some assumptions but I’m pretty good on my I’m pretty on point with my assumptions about what Microsoft’s building I’ve been pretty right I’ve had a pretty good track record track record shall shall I shall I go back to episode one of the podcast do I do I need to reference that one again talking desktop in the service

54:26 yes I’m I’m two for two now I think so many steak dinners so many steak dinners all yeah all I know is my stomach’s Filled from steak I don’t know about yours so you’re great with the future historically going back the format I don’t have a good memory I would agree with your Tommy on that one I don’t remember things very well I and I owe memory is great to say that you have a great memory that’s true things I remembered I don’t remember it but I remembered a lot I was I was right I was right 100% of time because all the things I was wrong I

54:56 forgot awesome with that we have burn through I think it’s a pretty good let’s do final summaries final thoughts here so let’s just do a quick round of final thoughts and then we’ll wrap it up here today Tommy give us your final thought around this new function power query M using Delta tables no I think this is just the beginning impl implications of really what we’re going to be able to do this is by no means the full feature set of where this capacity is going to be I think shown we’re only seeing just the beginning of the function so I’m really

55:26 beginning of the function so I’m really looking forward to seeing where this goes Seth what what are your Impressions here other than that you hear from Mike caros free compute and access to all your tables all your Delta tables bya this never I’m just I can’t remember anytime I was wrong let’s just say it that way no I I I’m I I like the feature the new function we’ll see how it integrates into the into the workflows and and then the closer we can get to I

55:56 and and then the closer we can get to I think stories where we can progress and push things that are developed at a lower level into the Enterprise spaces and use the same code it would be fantastic so if this is an Avenue towards that direction and yet another way we can interface and and still get the same performance without refactoring things I’m I’m a huge fan so awesome I think this is a smart Evolution for Microsoft I think this is allowing them Delta is now becoming I think a standard inside the powerbi and

56:29 think a standard inside the powerbi and fabric ecosystem you need to understand Delta it’s it’s going everywhere it’s changing your workloads and it’s letting you do things at scale with less cost all the time very very Pro on this this feature maybe we’ll have to Ping Chris web directly and make sure we understand exactly that we don’t need a SQL compute to go read Delta tables anymore but I’m pretty confident that that’s that is what’s happening here and that’s why I’m very excited about it I think this is going to be great for people who know how to use these things

56:59 people who know how to use these things and if you’re going to be extremely effective in the fabric and/or powerbi ecosystem this is going to have to be just part of your repertoire one thing that I’m considered of when I think about this feature is it would be really nice if these connectors were smart enough to do incremental refreshing for imported models so if I think about how this pattern should work if that Delta table is extremely large and I’m trying to load to load 500 million rows of data it would be really nice if this connector would fold

57:31 really nice if this connector would fold queries back to that table and pick the right partitions based on some incremental refresh policy either like by dates so I’m hoping that’s why they’re going this direction and maybe we get some incremental Refresh on top of this so I don’t have to read the entire table every time I want to load something but I think it’s a move in the right direction so we’ll see Chris can you please make another blog around a NE refresh for yeah road map let’s figure that out please let’s let’s get that dialed in there so thank you all very much for

58:01 so thank you all very much for listening and watching the podcast this is the candid conversation around all the the fuzzy things the people in the process parts and we have a good dose of technology in here as well but this is all the the conversation water cooler conversation around powerbi I hope you enjoyed this conversation learn something new check out the new feature go read the blog it’s in the the description below it’s also in the chat window as well go read the blog go play with some of the data let me know what you find in the comments below and we can prove Michael wrong for the first

58:31 can prove Michael wrong for the first time ever all right Tommy that’s that’s totally a joke I’m always wrong Tommy where else can you find the podcast you can find all the places where Mike’s never wrong on Apple Spotify or wherever get your podcast make sure to subscribe and leave a rating helps us out a ton you have a question an idea or a topic you want us to talk about in a future episode head over to powerbi. com and a great question finally join us live every Tuesday and Thursday a. m. Central and join the conversation

59:01 a. m. Central and join the conversation on all of powerbi tips social media channels I feel like I lobbed up too much of a softball here on this one this is this this is too easy it’s just too easy anymore anyways thank you all very much we’ll see you next time we appreciate your time and have a great

59:45 [Music] out

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.