PQ Functions – Ep. 217

Power Query makes it easy to build transformations—but it doesn’t always make it easy to reuse them.

In Ep. 217, Mike, Tommy, and Seth unpack what “Power Query functions” really means in day-to-day Power BI work: the built-in M function library, the custom helper functions you write when the UI isn’t enough, and the bigger question of how you keep those patterns consistent across a team.

A big theme throughout the episode: once your M starts depending on specific column names, intermediate steps, and tightly-coupled query chains, copying it into another PBIX can turn into a fragile, error-prone process. So the conversation naturally shifts to when you should graduate from “function snippets” to shared dataflows / upstream engineering.

News & Announcements

• Upgrading Power BI Architecture (Havens Consulting) — An event link from the episode description focused on modern architecture choices and patterns for Power BI teams.
• Submit an idea/topic for the podcast — A quick form to suggest future topics and questions.
• Friends of PowerBI.tips: Argus PBI — A community/sponsor link mentioned in the show notes.

Main Discussion

This episode starts with a deceptively simple prompt: what do we mean by “Power Query functions”? The answer matters, because the strategy changes depending on whether you’re:

• learning the built-in M library so you can read what the UI is generating,
• writing custom helper functions for repeated, specific tasks, or
• trying to reuse an entire transformation process across multiple reports and models.

The team also calls out the part that bites most people: Power Query work is often context-dependent. Rename a column, change a step order, or shift a source shape, and a “reusable” function can break instantly—especially when it reaches across queries.

Key takeaways from the episode:

• Treat the Advanced Editor as required reading: you don’t need to memorize M, but you do need to understand what each UI step produces so you can troubleshoot and refactor safely.
• Build a small set of helper functions for repeatable tasks (string parsing, iterating files, paging APIs), but accept that distribution/versioning across PBIX files is still painful.
• If the same transformation logic shows up everywhere, it’s a signal to centralize: move it upstream (dataflows, shared semantic models, or an ETL layer) so you don’t copy/paste business logic into every report.
• Dataflows can reduce repeated load on source systems and enable more consistent “bronze/silver/gold” style patterns—especially when you use linked/calculated entities to reuse cleansed outputs.
• Don’t confuse “I can copy M code” with “I can copy the whole process”: queries often depend on parameters, intermediate tables, and specific step names.
• Think about who needs the flexibility: business users may need fast Power Query prototyping, while the BI team needs governed, reusable assets that survive change.
• Reuse isn’t only about speed; it’s about confidence—knowing multiple reports are sourcing and transforming data the same way.

Looking Forward

If you want Power Query work to scale beyond a single PBIX, invest in reusable patterns (and readable M), then promote the transformations that matter into shared, governed assets like dataflows.

Episode Transcript

0:01 [Music] foreign good morning everyone welcome back to the explicit measures podcast I was

0:33 the explicit measures podcast I was actually thinking about changing the intro today I was actually thinking welcome back to the EMP but it didn’t run off my tongue quite well enough yet I gotta I gotta work on a new intro yeah no I don’t like that one but it is a happy Tuesday it is Tuesday and it’s going to be a busy Tuesday today unique Tuesday it is we’re going to have a lot to talk about unique New York what is that unique New York experiment it just reminded me of that what in the world I’ve never even heard of it local warm-up unique New York unique New York

1:04 warm-up unique New York unique New York if you ever did singing in choir back in the day yeah I hung out with all the drummers I was part of a Ten Tone show and dance little guy I did I literally start with wrestling and ended with doing theater and show tunes there goes well let’s move on not me I didn’t know this about you we’ll take that off Old Man River here we go thespian pasts

1:36 here we go thespian pasts that’s the big news today right isn’t that something to do with it is the Tonys oh wait excellent intros and announcement things coming out now first off I guess we could mention that the announcement has just recently been made for the admin monitoring for tenant admins has just recently gone the the public preview release will begin rolling out on May 22nd and will be deployed to all regions by May 23rd

2:06 be deployed to all regions by May 23rd so that’s today surprise it’s in and it’s awesome have you been able to jump in and look at the data and the metrics in it yet Seth I have and it’s it’s so yes right out of the gate there’s already some really interesting things that are occurring in your tenant yeah yeah like I think we so so we have multiple different premium subscriptions depending on in our embedded model or in our P1

2:38 embedded model or in our P1 and it looks like I have somebody who’s working outside of the P1 environment sharing a bunch of stuff which would be requiring end users to have Pro license so I’m gonna go attack that after the show today figure out where that’s happening well this is all right too because they this has been part of the roadmap for a while I think when we did our like top 10 release wave feature Updates this was I think all of our top fives yeah this is definitely

3:08 fives yeah this is definitely needed this is either you build it yourself or Microsoft starts helping you out here a little bit and give you some more information about what’s going on yeah it will definitely be a construct week for me to understand how I implement this within the rest of the team because it’s only 10 admin right so you’re you have to be a tenant admin what I like about this is tenant admin it automatically shows up in your workspace right and all you literally have to do is click on it and it automatically like brings in the data

3:38 automatically like brings in the data set in the report there the the small Nuance here is it automatically shows oh Court and you got to give it like five minutes for it to actually compile everything and and the model to spin up yep the data set to be there and then just like hit refresh or close it out and then come back in and then it’s all all set to go but based on the the article blog it looks like like there are like you can access the data set itself and and add to the add

4:10 data set itself and and add to the add to reports within the workspace but right out of the gate they have activity overviews an analysis page which is which is more of just the explosion of trees where you can split and separate out the different activities I got to figure out how to how how that works because you have multiple different selections you can make yeah and then just the raw activity details which is is really cool and and and and I’m very happy this is here because we I was gonna go down that route of the

4:40 was gonna go down that route of the custom Solutions and picking up all these activities and it it alludes to me thinking that this encompasses all of the activity logs like that we have access to in the API but that remains to be seen there are the two links and the announcement that point you to the the API documentation and things that have been out there does it say the the time range of how long you get the activity I don’t know if it’s on the the blog

5:11 I don’t know if it’s on the the blog itself yeah but it looks like it’s probably the same window the 90 days no it’s actually a 30 day 30 day window I think is what it is at least it’s a 30 up front and I don’t know if that’s like they load 30 and then they’re going to continue to increase but it’s a 30-day window this is where Roy Romano’s solution still comes so in handy because yeah we we put together like we had a Powershell script for one project and we were able to

5:42 for one project and we were able to track that for two years so one of the things we were reporting to our executive stakeholder was look at our year over year look we have that yes you do need to have that at some point yeah and like this is all wrong great but I wouldn’t say substitute that don’t run your normal log the historical data is so important to have too so although I I think I think this is a good step in the right direction yeah I will I do have a gripe though the icon that they use for the workspace is like the very first Power bi icon

6:12 is like the very first Power bi icon they’ve ever used I didn’t notice that it’s like this little like it’s this little stacked like circular thing with like the three bars stuck on one side this is the very first like power bi icon that’s what they made so they’ve been playing a while yeah it looks a little bit too much like the original Power bi icon or logo or whatever you want to call it but that’s literally my only gripe on the on the works and again I think you can change that too because you could edit the workspace yeah would be my opinion or maybe maybe you can I’m looking to see if I can adjust that right now

6:44 if I can adjust that right now I like your I like yeah Seth I think you’re right about the 30 days things I think that’s what I’ve heard I’m trying to dig up on the on the blog I think that’s what it was previously for all admin reporting was about a 30 day window I haven’t seen any note around it extending beyond that but it would be nice if you were on premium to say hey here’s a storage account just just give all the data and stick it here or you all the data and stick it here or just have some process know just have some process where I could turn it on and say keep more or keep how many months you want like I think that would be helpful too speaking of which though it’s it’s not just premium

7:15 just premium right now correct goes into Pro as well right wow so it does show like that the shared capacity and things like that which is interesting but to your point if it goes premium or if there’s a premium version like my first thing is can I connect into the data set because I want to modify it right yeah I want to pull as much data as possible because the the 30-day activity window is good but if I’m looking at things I’ve intentionally gone and taken action on

7:46 intentionally gone and taken action on to resolve I would want to see different different yeah and this goes back why I think you need to own it yourself because it’s probably like the live connection you can connect to it in a power bi desktop but it’s still it’s it’s a live connection there’s no way I think to really take this from an imported point of view and this is where actually having the raw data changes in terms of how much you can do with it this is saying You Can’t customize it but this is going to spawn

8:17 customize it but this is going to spawn all kinds of weirdness it’s going to be like someone’s gonna be like hey look I can do a data flow that can go hit a data model and then return a table and save it and like there’s going to be all kinds of weird Solutions on top of this like they want to keep this information but they don’t want to actually like build it’s honestly it’s a lot of work to build all the API like if you’re going to go forward from the apis all the way into a reporting solution with the activity logs and all the things that are there there’s a lot of work to do there’s a lot of pieces that work 30 minutes minutes if you’re using Ruby solution which also uses Powershell and the commandlets

8:47 uses Powershell and the commandlets which are not technically reliable enough so I’m saying if you want to start from scratch it’s a lot of work fine yeah just getting the data is a part of the problem the other half the information is okay once you have the data you got to model it you got to put it in a way that makes sense for like your report audience now again Ruby did a great job on his his GitHub project actually I’ll see if I can pick him up yeah his project even if you do a custom solution like so like we built before rui had is we had a full custom solution pushed to Json push to CSV put our own model together and we connected

9:17 our own model together and we connected it with the scanner API and refresh history and yeah it took a lot of work but the insights from that they actually have that and again to own it that’s exactly right there’s there’s so many but that’s that’s the point right yeah it it takes a lot of work yes right the benefit of having something out of the box which there may be comments in here already like hey it’d be great if you started with this out of the box because yeah this this gives you enough insight to fix big problems right that could be

9:48 to fix big problems right that could be occurring or see immediate usage and then drive the conversation of hey you guys you like all these insights I’m providing you I could do a lot more but it’s going to take some more more work like of course once you start feeding that right that that oh you can give me you can give me 60 90 do we know like can we get the full year we could do that but it’s going to take some work so this is the carrot thing right

10:13 this is the carrot thing right and this is what I was hoping would have been out there or I could have leveraged a lot easier as opposed to just trying to invest and go do a bunch of work around admin which I know I need to do at least this helps me facilitate that story so the the probably the most powerful metric we had using the admin API and using activity log was a two and four week rolling to say what’s our two week pre and then first previous two weeks and then a four week versus the previous four weeks because when you look at 30 days it’s fine but you’re

10:43 look at 30 days it’s fine but you’re trying I see that the increase of those reports because again it’s all about the context and what’s the context you want with this this is great place to start if you have nothing but I can’t say yeah yeah I agree and again this is this is for people who have so many organizations I talk to they’re like well we should probably start thinking about administering power bi and so oh okay well here’s here’s I bi and so oh okay well here’s here’s and again it can start it mean and again it can start it doesn’t have to start at a corporate level where there are a lot of organizations that are starting to power

11:13 organizations that are starting to power bi in a department and they’re trying to figure out like their data space for their particular group and so in that in that department they’re acting like a small business to the broader part of the organization and I can’t tell you how many times I’ve had the conversation of let’s start in a team figure out the rules of the road and then roll out to the rest of the organization that that makes the most sense because then you can work out the Kinks for your organization figure out what are the right policies and things you want to put on top of stuff so yeah so oh

11:43 to put on top of stuff so yeah so oh yeah Alex is already pushing us along to the to the conversation now and just just so everybody’s aware it’s in that cool green theme color color yeah it is yes exactly right anyway moving moving on to moving along for Alex so we have to give some promotional efforts here to our friends we have some really great training coming up I’ll put the link in the chat window here as well if you want 50 off 50 sorry you’re not 50 50 off read Havens and Steve

12:15 not 50 50 off read Havens and Steve Campbell are doing some incredible training they’re coming up with a online course that’s going to help you to build power bi Enterprise grade models and help create sharing policies and strategies it’s coming on June 13th and 15th it’s going to be a four day a four hour session for for three days and it’s going to cover enhanced model design Enterprise grade modeling features deploying and managing content managing your model life cycle so if you want to go join that we’d highly recommend you check that out there is

12:46 recommend you check that out there is a code as always here’s a promo code in the chat window and the link to go get to the the event the code is PBI tips 50 off 0493 so if you want to go check it out check it out that’s a great session Steve and Reed are great trainers I do a lot of training already so we’d highly recommend picking up their stuff anyways just want to throw them out there that’s the link will also be in the description as well in case you want to jump in and join their class so I figured they’d help us out a lot Steve is also a really big developer of one of the the tools that we give away

13:17 one of the the tools that we give away for free called Power bi Version Control so I don’t have we’ve played around with that one a little bit it’s a power app that connects to a SharePoint page and basically helps you build a Version Control type model where you can check in check out things from from SharePoint and use that to keep a a version of your file around so very very helpful tool it was probably the first version control tool that I’ve ever seen for power bi out there and the only still it’s still the only one out there that’s true that’s true I’ve seen some

13:47 that’s true that’s true I’ve seen some other people do some creative things PBI tools came into the into the mix here and they’ve done a little bit more investment on the git side of things for reports but this is really the first tool that came out around anything that’s Version Control related which is actually super exciting so I’ll throw this down there as well this is Steve Campbell and Anthony Escobedo spent a good amount of time developing and building this tool so you want to go download that it’s free you can go check it out it’ll help you Version Control your models all right enough of the announcements on

14:18 all right enough of the announcements on to the topic for today for Alex let’s talk about power query functions now that I know Alex is here I just want to get out right out right off the gate like M code power query just it’s one of those really overrated things I think oh my God oh my goodness are fighting words hey man you you poke you poke the host we can poke back anyway excellent all right all right so jumping in here let’s just just give a brief overview okay Tommy

14:50 just give a brief overview okay Tommy give us a little intro here about where you feel like this is going power query functions what are we talking about what’s what’s the topic for today I think you’re right this this can take a lot of directions but at least from from my point of view my experience in the power bi world power query has always been one of the elusive tools from a consistency point of view in terms of we’ve created something standard what is power query all about it’s the Transformations the ETL to take something raw and to actually get

15:21 something raw and to actually get something structured accurate and consistent however for the longest time one developer would have the issue of trying to copy and paste how do I reuse something that I’ve created that I know is standardized that I know is correct or accurate and more importantly especially if there’s a lot of complex Transformations take away the engineering side or if your company even had the engineering side all this used to be done or still is done in the power bi World there are now other methods and

15:51 bi World there are now other methods and alternatives to create things consistent not just for a single developer but also for a team of developers or a team of authors say hey product information’s here hey the the fixes we made to our sales transaction history is here we have data flows now but there’s still hurdles and I think there’s still things that teams and bi professionals are working on to keep power query not only consistent where we’re using the same thing but up to date within

16:21 same thing but up to date within multiple reports and multiple models so this this begs in the question here is where do you see this whole function thing fit power query already is a functional language everything you write so again for those who are like newer to the power query space right that you have the formula bar at the top you can then click a lot of buttons and what happens is you get these steps that appear on the right hand side so you get these transformational pieces that are coming along and so the the the idea here is

16:52 and so the the the idea here is there’s already a rich function language behind power query so are you talking about custom functions writing your own functions what are you what are you talking about here are you just talking about the standard functions I’ve well again there’s that’s a really good distinction there’s the mix of technically every line is a custom function that you’ve created whether or not you’re actually defining it and again Alex correct me if I’m wrong but there is creating a query that’s the standard output of I’m going to connect to SQL I’m going to do these Transformations and here’s my final output table but there’s also custom

17:23 output table but there’s also custom functions functions that you may need to reuse where I need to iterate over multiple tables in a folder or I need to iterate over a API that shows me the next page ID at the output there’s a lot of iterative functions that we need to do or we need to reuse because it’s great here but I cannot I have to recreate the entire wheel now with parameters for any other new report also functions do not flow out of power

17:54 also functions do not flow out of power or data flows so you can’t in a sense reuse it if you’re creating a function in data flows you can’t output that function and then reuse it in a report okay so what you’ve covered a lot of different things here so I think I want to I want to pair down and kind to I want to pair down and take a couple of your topics in a lot of take a couple of your topics in a lot of areas there’s a lot of areas so first one I think I’m thinking about when I’m thinking about power query functions I’m thinking about when I start thinking about I think about the generic basic ones up front right there’s like a source one there’s like Excel workbook one where you

18:24 Excel workbook one where you could actually grab a workbook and decode it and get the information out of an Excel workbook I know so I’ve done a lot more work I at some point Microsoft changed how they default loaded your files from a folder so if you would like initially when you would load files from a folder it was you’d have to go to the folder you had to write some functions you’d have to get those individual files binaries combined into a single binary and then you could open up the file and then read everything together so Microsoft at some point made it a little

18:54 Microsoft at some point made it a little folder option that you would read from a folder and it would create all these automatic functions for you it would here’s the here’s the sample file here’s the the initial load step and all these other functions would be created for you which is cool because it’s building a number of custom functions for you immediately on top of your data set good good feature what I found for me I liked it to be simple I didn’t like it adding a whole bunch of extra folders and functions for me so I started building like a little library of like custom functions that I would use over and over again and I would say

19:25 use over and over again and I would say this when we start merging into the the helper yeah James you’re perfectly right on the right endpoint it’s like the helper function so these are little functions that can do a simple little thing right I have a long URL string I need to parse something out of it or I have a it’s just very simple things that you need that are very custom to what you want to build and so I really like having a library of these custom built or custom created functions and then they all become accessible to your power bi file as soon

19:56 accessible to your power bi file as soon as you load them into an M query so I have found that m key has a great library of custom M functions that she uses I think she has a GitHub around all of them which are incredible and so I use them all the time but to your point Tommy these are all stuck in the file that I’m using there’s no ability for me to move power query functions across like an entire tenant or an entire like either just between one notebook to the next you have to recopy them and there’s

20:26 next you have to recopy them and there’s no way to keep that function up to date which would be really cool there was like a library that like created this this reusable function layer what we used to have we used to have one power bi desktop report that had all the custom functions or reusable queries I would use nothing was loaded in it was basically called like the master library and Alistair Library yeah it’s a great idea and you just open up the desktop file so it’s easy to copy paste because to your point all well and good

20:56 to your point all well and good everything we set up into this point but there’s a sudden stop point where it’s not reusable it lives in an island in its own file or it’s in its own report and that makes it really hard for not just trying to reuse something but if you’re on a team and something has to change you have to make an update there again they live all independently of one another another Alex Alex is like on fire right now so Alex is is throwing down all kinds of

21:27 Alex is is throwing down all kinds of nodes here around information like he’s he’s already going like what would it look like for chat TPT with respect to queries because again if I have a version of a query or a function it lives only in that desktop file if now if I have seven other reports that are utilizing that I have to update each file individually totally but but you’re already at a a senior or or intermediate developer level that’s already thinking about okay I

21:57 that’s already thinking about okay I have a function I need to then educate my team about what that function is and then figure out a way to relatively easily distribute that function to all team members and so again in my mind here it would be really nice if you had like that common Library where you could say hey I’m going to point you basically make a single query in power query and say point to this GitHub and you point to a GitHub and then it loads all these like PQ functions already automatically for you into a into the library so you could have them that would be slick if you could do something like

22:28 slick if you could do something like that or or maybe someone can build that and we can use it just real quickly there’s two areas here I want to distinguish there’s custom functions where you’re talking about doing a simple transformation maybe it’s an iterate over a files or maybe it’s over a single row or a column but I think there’s the other side here that’s probably more Pro prevalent is trying to take the raw data or or something unstructured and creating that final output table that’s going to need to be reused and I think that is where a

22:59 to be reused and I think that is where a lot of people probably are trying to find a better solution for because that I’m going to need more often like a master customers or something rolling where you need the output to always be the same in each table table see those are two completely different things though right yeah the conversation’s been interesting to me so far because how much of reusing these specific power query functions functions it is is what we want to talk about

23:29 it is is what we want to talk about right because there’s Nuance in connecting to data sets Etc versus the the the output of power query which is the data set itself right like if we’re talking about reusable data sets right where even some some people are talking about data flows right like are you building your Solutions in such a componentized way that you’re saying this is how I

24:00 way that you’re saying this is how I clean this data and then if you want to interact with it you do it at this point after we cleanse it using multiple different things power query functions Etc I don’t I don’t think people are building that way all the time though right because you the the value here is the value that we can like solve how do we best reuse power query segments right or yeah certain bits of M code between a team or is the value we

24:31 between a team or is the value we haven’t we have a consistent output that we want to reuse and it just so happens that it’s being generated by power query I I would make the argument if you’re probably beginner you’re probably not feeling too comfortable with functions much less trying to create a library of them of creating your own custom functions and I think a lot of beginners start with I’ve created this great version of the table or the script version of our customer information use this this and it technically they’re both in a sense outputting something just one has

25:02 sense outputting something just one has parameter one doesn’t so I I guess what is the only way you can you could potentially do that so the the only way for which what are some methods in which you could you could do that right well with power query it’s they’ve made it dead symbol with data flows it’s hard not to use that custom functions there’s really no solution I know I’ve seen people try to use a GitHub we’re connected to GitHub try to extract the text and make a function out of that but that’s not a

25:34 function out of that but that’s not a really a solution if you are creating custom functions to try to reuse Mike I don’t know how you did that because it’s not a oh I didn’t say I did it I just thought it would like be nice if you could do it like yeah that could be something where I would say Microsoft should invest in something so again this is one of these things where I’m like the community is already building libraries of things that they want to reuse or use over and over again why doesn’t Microsoft continue to to make it easier for the community to invest back into the actual product where we have like a library where we can go hey

26:04 like a library where we can go hey download this file or update this thing or connect to this open source stuff and then allow it to be brought into our tools when we need to again there’s probably also a security and a risk thing that Microsoft’s is overlaying as well but I also feel like as a developer of what we’re building here we should be able to have a bit more control you’re you’re giving us all these great great tools Microsoft but yet we don’t have any capability to like like slightly enhance them with what the community is building let the community develop all these helpful things and

26:34 develop all these helpful things and then it Force multiplies what Microsoft’s doing because then they don’t they don’t have to build every function they can let the community optimize and tweak and and build these better Solutions it just makes it easier for the community abroad to use and leverage them what’s hilarious is this actually actually was something available in Excel with power query back in the day where if you signed into Power query you could actually save your functions and queries I don’t know if you remember this I’m sure sure Alex can speak for this too but I are back in the day if you signed into Excel

27:05 back in the day if you signed into Excel and you could actually then save your queries and reuse them in multiple files whether it’s a function or a power query that and that was on the roadmap on the release wave and they took it off so unfortunately what’s what’s the value here between listing the functions which you can do in park or in power bi and you can dump it into a table and you can understand what the functions do versus what you’re describing is it is it the fact that it it’s an example of

27:35 it the fact that it it’s an example of how to use the function in your particular ecosystem it was something that I can reuse to Mike’s Point if I have to do the same transformations in all of my reports or all at least all my tables extracting URLs or iterate over multiple files in SharePoint which right now is building from the ground up I can easily just pull that function in and just whatever in my new file or my new query just pull in whatever that new parameter is so in terms of saving a ton of time but also keeping it consistent

28:10 and that was available no Nuance that the data influences depends on the function but if let’s say it’s a parameter for reusing a folder all you have to do is point to something binary binary and if you’re doing something very custom we’re just like just create the functions and then append the tables I’m not doing all the extra clicker things unique to that table well I’ve just created my one query now so I have a look so in some cases I have a little bit of so I’m I’m tossed on I’m waffling on two different sides of this

28:41 waffling on two different sides of this story right on one side of this I’m saying if you’re doing that common stuff over and over again in all your data sets one you just need to figure out how to centralize your data sets more so you’re then picking a more common central area where there is again we’re talking about like do I bring that engineering further upstream or do I keep it closer Downstream towards the power query and the power bi data set right in on my one side of myself I’m thinking when I’m starting to see the ReUse of that same function over and over and over again that tells me there’s

29:12 over again that tells me there’s potentially a problem with what we’re doing and we should actually be having a broader discussion around hey there’s this common file type or information or something we’re using over and over again maybe that should be brought into a lake and we should just pre pre-calculate or pre-build all these things before the business gets their hands on stuff so I I think potentially there’s this idea of like being able to centralize more of these common data engineered things on the other hand I’m torn because I want as much flexibility in the businesses hands as I possibly

29:42 in the businesses hands as I possibly can and make it easy for them to build let’s call it lack of a better term prototypes early versions of What the broader bi team should be developing so I I’m very torn on like on one hand when we talk about centralizing functions and centralizing things I feel like we’re moving more towards a let’s produce our Transformations upstream and then simplify our power query on the other hand I don’t want to hold back the business to be able to produce what they want to produce with whatever custom

30:12 want to produce with whatever custom function they want to build does that make sense what I’m saying there yeah I’m getting pulled two different ways right now that raises an interesting question that I’ll propose to to the two of you is there things power query can do better from the cost and efficiency to build that data engineering tools or platforms cannot do cannot do that’s a great question

30:39 that’s a great question I’ll say on one hand power query do better in cost and efficiency versus yeah yeah I’m not going to necessarily say efficiency but I think cost power query wins wins for lower for lower and I think for lower items for lower hanging fruit power query wins because it’s included in a pro license you can run power query to your blue in the face until you run out of capacity or something Falls over there are

31:09 something Falls over there are thresholds to this right so you can’t go load 10 million records with power query all the time that will eventually fall over but then you can start implementing things like incremental refresh you can start loading small amounts more smarter like you can do some interesting things with power query that keeps the weight of what power query is doing down to a minimum and an efficiently loading data so I think in some ways the cost sometimes is not comparable for smaller pieces of data as far as a UI Microsoft I think is invested a ton of time on making the UI

31:39 invested a ton of time on making the UI really easy to use for power query and so and so in Gill’s book when he talks about you in Gill’s book when he talks about if you’re a power query user know if you’re a power query user 80 85 maybe even 90 of your time you could just scoot around the UI and get most of your data needs met you don’t need to go write function that’s very specialized and this is where I’m kind specialized and this is where I’m also touring too right I’m torn of also touring too right I’m torn because we’re talking about a very specialized skill at some point like if you’re getting into the custom function writing you have a very specific need and you’ve now had to take the time to

32:09 and you’ve now had to take the time to learn how to write power query functions that meet your needs I would answer that in a way Greg would probably make fun of me because I always bring up the data but it’s data size dependent I think right because if it’s if if you have smaller data sets that power query can just rip through then yes it’s much more efficient both in terms of cost and efficiency because of the rapid

32:40 you have to access that data manipulate it and and get value out of it in reporting immediately as you grow in data sizes I think the cost perspective first goes yes it’s more cost effective because it’s very much allows us to do iterative development and or like solve the big problems first and then figure out how we would want to Enterprise ETL pipelines instead of doing things through Power query and just have our process run through a singular model or

33:10 process run through a singular model or a singular report then it flips and then it’s like it’s no longer cost efficient because then to your point you would have sprawl across like many different reports or models doing the same activities as opposed to just sourcing from the same data set that’s created but dataflows gets you around that right if you want to reuse data sources an efficiency standpoint largely depends on the data size right like I think the minute you hit a certain threshold powerful query is going to struggle at the same time too

33:42 struggle at the same time too power bi is very aggressive with Source systems so when you talk about efficiency like the point if you if you have multiple models and multiple power query processes nailing the same SQL Server instead of one streamlined one that you would do in a different tool and just create the data set then yes that’s that like it’s going to create problems so no one and I I feel we can’t have it’s very hard to have this conversation in 2023 without saying the

34:12 conversation in 2023 without saying the dependency and the need of data flows here here this would be a very very different conversation and I without the use of data flows if that wasn’t a feature available what it allows from the bronze level gold let me let me dive deeper in that question where not So Much from the data load but can can you run business dependent queries business dependent reporting off of data flows and off of Power query Alum 100 yeah okay you can do that so and I think that

34:42 okay you can do that so and I think that puts part of the argument here then where so with with data flows and I really think this really takes transition rather than the custom functions but because I think that some people have said this is a good idea where it’s acting like python modules which would be cool but I think from what we’re talking about with some of the consistent tables queries that we’re trying to do data flows really really shine we’re The Source system that you’re talking about the load oh yeah that’s an issue but one of the things Microsoft’s really

35:14 but one of the things Microsoft’s really pushed and works incredibly is that bronze silver gold within dataflows using the oh my gosh I can’t remember the terminology calculated load oh but the the linked entities and calculated entities that allow you to pull from another data flow where it’s not pulling on the source I and the amount of Transformations that can be done and the amount of ETL that can be done in power query alone

35:44 done in power query alone utilizing now as that’s a little more enterprise system outside of just desktop with with the bronze silver gold and dataflows in two workspaces is something that I can see maybe never has the grow up story in terms from a data engineering I I don’t know about that okay right so because the the same the way I think about this is is can within a power within a pbix file right that I’m I’m building things within PBX

36:15 that I’m I’m building things within PBX files all the time your gripe is I can’t reuse any of the power query within any of these things and there’s about no visibility to them right right valid argument so how do you solve that problem okay well like we could use a data flow and the output of the data flow is a reusable object that we can now use and extend in different models but how many data flows do you have and how many of those data flows like ultimately make their way into think are you reusing some of the same connections are you reduplicating things so I could

36:46 are you reduplicating things so I could make the same argument there and then that’s where you you level up into the Enterprise where it’s like okay if I’m extracting data from a source system and we’re going to reach a gold level or a certified level of data or governed then I should have a single path that then has very few or well-defined paths out of that in I have a product and this is the way we view that product throughout the organization related to this certified data set that we have governed

37:18 certified data set that we have governed metrics on we have slas we have all these things that are captured in this pipeline that is probably not a data flow flow at least in in terms of like as you scale because it’s easier to centralize around those things and and start off and have a reusable object as opposed to like maybe what I’m saying is re reducing the number of touch points right is is the common theme that would allow an organization to understand and

37:48 allow an organization to understand and have more and more and more confidence in in how and where data comes from so I really like these opinions and again I want to go back to your comment there Tommy so I Seth I agree with your summarization there I think that’s a very it there’s there’s a lot this is where I feel like there’s there’s a lot of work being done in the business and people are going to use power query they’re going to find Value from it they’re they’re not going to be the maybe the most robust or the cleanest solution all the time but at least you’re having

38:18 the time but at least you’re having people to not get blocked by data needs I want to go back to your comment tell me around using dataflows because I really liked where you were going with that and talking about this idea of like bringing down data and then having entity as it’s table it’s it’s a computed tables or something like that where you’re actually able to stitch multiple data flows together and Link the data together I’ve seen some very solid business cases in the business where the it organization was just moving too slow and so the business wanted to just get some stuff done and make a process that they could manage

38:48 make a process that they could manage and own to be able to deliver value to what their reports are needing at their their leadership level right so I felt there’s a lot of value in being able to leverage these data flows and interestingly enough we could jump between data flows that were in the Pro workspace versus a premium workspace so we were you didn’t have to worry about your premium capacity falling over because the data flow was being abusive you could just put it in a pro workspace and let it just chew and then you could pick it up somewhere else in addition to this

39:18 in addition to this also thinking about some of these data flow pieces the data flow today’s current state is mimicking a little bit of what the the lake house architecture is doing so a data flow inside powerbi. com picks up some data does a transformation to it and it writes it back down as a CSV file and a in a model. json file that describes how the data is is being brought together which I think again really is the future for where Microsoft and all these data companies are going it’s going to be this this storage layer and this compute

39:49 this this storage layer and this compute layer databricks is already doing it they’re very heavily in the space and so I think this lake house type architecture even though it really it’s not the same pattern that we see like in data bricks or other snowflake or other other Solutions yet but the data flow is really doing a lot of the lake house type work but on under the hood be ahead of what the user is doing so I think I’d like to see more investment around and this is maybe what things we’ll see later in the future here but like I’d like to see more

40:20 here but like I’d like to see more investment on that type of architecture I think that’s the way to go I’ve been developing this architecture for the last five years with companies and it’s definitely a winner it it keeps cost down you can have large amounts of data you can do data from source to reports within under two seconds or two minutes we I’ve been doing some very fast reporting pieces here so it gives you a lot of flexibility on what you can develop and build in this tool and so I feel like this is a it’ll be interesting to see where this thing is is taken in the future

40:49 this thing is is taken in the future because I think there’s a lot of potential here and it fits very well with this lake house type architecture story it’s been it’s been doing that though right like they’ve they’ve leveraged a lot of the same ecosystem Parts it’s just bringing the business folks along for the ride the the interesting thing here is is like where where I I think part of my head is it is it’s the outputs of these processes that are more important than sometimes like the ReUse

41:20 important than sometimes like the ReUse aspects of things and and the reason I I say that is if if I think about a a a a process that I built in power query right Tommy we we connect to an API I need to loot like I need to then like pull a table of information I need to extract an ID then that ID I’m going to iterate over to pass into another API then I’m going to expand that table and now I’ve I’ve got an iterator function I’ve got a connection I’ve got a set of

41:53 I’ve got a connection I’ve got a set of things of objects within power within this power query experience that are inextricably linked it’s not the same as like everything being in the same single SQL store procedure right like I have a bunch of code it executes this this this this I can literally copy and paste that into a different system I can change some table names and it would do the same things provided it’s like I’m going from Dev to test or something like that with power query

42:25 something like that with power query I can copy the M code but if I copy the process I can’t copy the process right and it’s the process of all of these interconnected things within this this pain that is a value for reuse and I can’t copy that I can’t go unless I’m gonna create a power bi file that says here’s the raw connection I’ve done nothing else and I’m going to call this file my

42:55 I’m going to call this file my connect to API and loop through to get whatever the the thing is and then that’s the base but then I’m forcing myself to reuse that pbix file as opposed to maybe I want to incorporate this in a different model that I have now I’ve got to copy out all of these pieces make sure that they all conform to the same model don’t do a DOT one because it’s the same name as something and then everything breaks and it it is it’s convoluted but if you understand the

43:27 convoluted but if you understand the components and everything that you’re trying to do can you get reuse out of those different things absolutely so is there a Time Savings in all of that block of M code being able to copy and paste from one Advanced Editor to another absolutely there is and you can do that and just copy paste is it centralized no but I so so that part falls apart from my like equation from SQL because all my store procedures are in the same place like so if I really

43:57 in the same place like so if I really want if I name things correctly and I said this is the doer of this thing well that that’s the procedure that does that thing right there’s no look up for me within power query across all my files that says hey I know I built this look up do this thing in power query where is that like God dang you just can’t yeah you just took me on a mental Journey because I was thinking about what you said and I wanted to push back and technically you could but you have to be so self-conscious of the functions

44:27 to be so self-conscious of the functions that you’re using to not break it or your function your function would have to be so bare that it wouldn’t really solve a lot because in power yeah which again is that worth the effort in power query if you rename or reorder columns and that column does not exist in a new table breaks right so it’s not just plug and play a few parameters especially if the goal of the function is not just something automated it is too really save yourself some time rather than just connect to a table and append it if I’m

44:57 connect to a table and append it if I’m trying to do a lot more my gosh I need everything to align perfectly because that’s the way power query works it’s not just plug and play a few parameters we’ll ignore everything else unless you’re very conscious of what you’re doing doing which again the amount of effort you have to test that out and trust me I know know is not worth that effort or worth the squeeze especially because I can’t grab that anywhere so that’s a really good point I think another great point you guys were

45:27 another great point you guys were talking about with the data flows and the data engineering if you’re going to have a library what’s another essential part if that’s going to work from an Enterprise level it’s the organization and all well and good with the with the bronze silver gold with data flows but if you’re relying on that for everything my gosh that’s going to get complicated and disorganized very quickly no folders everything everything has a propensity to to devolve into chaos yeah but does that mean that those those

45:58 but does that mean that those those things don’t work within ecosystems no it doesn’t right there’s a lot of value that these tools can provide to the business but you’re putting this is this is where the complexity of introducing all of these things to end users who have no concept about how these things operate that it will always devolve into chaos like can they can they meet their needs absolutely is it a one-time thing probably but there’s no restrictions correct because there’s value still

46:29 correct because there’s value still behind them when we start thinking about like eventually all it does I think is push us into different levels of conversation right is there immediate value in having tools like this that allow us to match update absolutely there is proof of concept solving big problems right away does it create its own issues sure down the road if the organization wants to move into a different level of analytics and say hey what we’ve found now that we have all these models and reports out in the wild is there’s a

47:00 reports out in the wild is there’s a wild inconsistency in data here it’s really important for us to lock in on this thing okay well that elevates your solution making then right okay if we’re gonna if we’re gonna do that where do all these data sets come from okay we need to create more structure around that maybe that’s data flows right everybody needs to plug into product it’s out here everybody needs to like plug into sales person it’s over here is that a better standardization for the organization you bet it is but then ultimately that’s going to Bubble Up to

47:31 ultimately that’s going to Bubble Up to the point where it’s like okay we still have inconsistencies what do we need to do and then it’s like okay well we can centralize all this like for the things that really matter maybe we should you that really matter maybe we should plug in and and build a different know plug in and and build a different pipeline in a lake house and then put some governance around it right and if we do that then we can also apply slas and then we can guarantee that the data sets that we’re producing throughout this for the executive leadership team all aligned to what is expected within the organization does

48:02 expected within the organization does that automatically make all of these other reports in like no longer valuable no they’re just in a different state of certification right and I think all that really boils down to me thinking about the functions and at least that part two not just the reusable tables are there even if there was a way where you can like install a function the way a power query language is it is so dependent on the table and the

48:34 so dependent on the table and the columns it’s going to reference that it doesn’t even if you was worth the squeeze to create a package or module it wouldn’t make sense because every function would have to be so bare every function is dependent on the query and the tables to choose but that’s my original question right like you can you can output in a power query file like all of the list of functions put them into a table yeah sort it and get the definition of like what you’re looking for I I think taking this away like down to its base level which is probably how I

49:04 its base level which is probably how I should have started if you think about the ReUse of power query right what I would encourage brand new users of power query to to do is get comfortable with the advanced editor because everything that you’re doing from a click drag drop whatever is creating that list of function that Transformations right and when you understand like what all of those actions are doing in the context of power query then you can understand

49:34 of power query then you can understand how to reuse them right so we’re talking about the column names yeah like what do you need to do well you need to crack open the advanced editor understand where in the process you you manipulated something and either remove a column or modify the column or a lot of that just becoming comfortable and not even necessarily understanding all of the different functions and all of the different operators and changes that it’s doing it’s just understanding like how it calls out on those specific things will put you so far ahead in

50:06 things will put you so far ahead in reusing power query code for different purposes that like as a developer you’ll know where you did that and if you needed to what I would say is getting into new spaces where I’m heavily using apis I I’m actually tackling this problem right now where I probably will carve out a pbix file that is doing like the iterator thing yeah right because going through that takes a while to figure out like how do you do this like what are

50:36 like how do you do this like what are the steps what are the processes and then like having that file as a reference point is it going to be immeasurably helpful in the future yeah and to Mike’s first point if you want to create a function or something reusable across different tables or different situations and you want to create a function out of that you have to know event editor that that 10 everything else is

51:04 editor that that 10 everything else is very base or bare but to do those really amazing things that can be reused you have to that’s that 10 where you it’s not the UI where it’s understanding how the functional language Works where I can create a command here but not reuse it until five steps later and understanding the output but that takes a lot of time and that’s not you takes a lot of time and that’s not yes or no I don’t so this is where know yes or no I don’t so this is where I think the community needs to help right so this is this is where I think the broader part of the the learning of

51:35 the broader part of the the learning of people so I just put a link in the window here for github’s Keys M language Library she’s got it probably about I don’t know 50 60 maybe different functions here around text around type around dates records Ms are statistical functions just a whole bunch of functions that are just created and so it these are these are custom functions you can go use and to your point the earlier Seth if we’re thinking about what we’re trying to build you’d have to boil down you have to think like a programmer you have to

52:06 to think like a programmer you have to think about what is the simplest version of code I can build that solves a very specific problem and I think where I’m I’m I’m leaning on this right now is there’s a lot of good stuff out there I don’t even know what to Google I don’t even know where to go to find this stuff yeah I don’t even know how to write a function and if I did write a function in power query what is what is the description what’s the documentation choose what are the input parameters there’s actually there’s actually a good amount of documentation that needs to go along with each one of these functions to explain to you why it’s there how it

52:38 explain to you why it’s there how it works what it does and so I feel like unkey’s done a really good job of like here’s some simple equations these are things that she has been working on and yeah it’s it’s absolutely incredible but she has a whole section in the M code it talks about documentation here’s the name here’s a description here’s the longer description it’s a category and she does this Source version author examples so she’s actually doing a really good job in these examples of exactly how this stuff is supposed to work again I find it incredibly helpful

53:08 work again I find it incredibly helpful I just feel like going back to Alex’s Point Way earlier which was how could this stuff help us with the use of like things like chat jpt and so this makes a lot more sense to me where I should be able to throw these kinds of functions at chatgpt chatpt should be able to document them and collect a library of these things and then you should be able to just throw there should be like a you to just throw there should be like a chat GPT 4M functions and then you know chat GPT 4M functions and then you can just go ask it a question hey or just like commands like add to my

53:38 just like commands like add to my repository yeah well you have to pull that into proper documentation yeah exactly we’re getting to a point where where it’s it’s it’s it’s it’s just supposed to be absorbing all this stuff in this is this is why AI exists is to make this stuff a whole bunch easier to consume a whole all this low effort work where not low effort but in documentation stuff no one loves to do it no one does it well and and so we burn so much time documenting things well that throw AI at this stuff never say you burn time

54:08 this stuff never say you burn time documenting things is not a waste well it’s it’s it’s the part that people like to skip because it’s just time consuming it’s not a waste no way to put that yeah it’s just time consuming I’m like I’d rather just be building cool Power query things as opposed to documenting stuff well that’s literally the definition of technical waste I just want to build stuff and not worry how I did it but no that’s it exactly but but honestly even with the chat GPT again the way power query is built the power of it or the the utility of it is its biggest

54:39 the utility of it is its biggest fault because it relies so much on the tables and the queries that are coming in again there’s very simple things you can do that can break everything where you’re just renaming a column that doesn’t exist unless you have something manually in power query we say ignore it but the only way to do that is to know the event editor I I don’t know it’s it’s one of those where I think part of this is having that secret sauce to me is the language understanding the language and being

55:10 understanding the language and being pretty proficient in it I don’t think there’s too many ways around it I I think I think I like to learn best from examples so I go find people who are smarter than I am in this space who’ll read their books go look at their functions go find libraries of things that are already working and then read through their functions because there’s a certain way that they’re doing it and as long as you there’s a couple Core Concepts you have to understand around M once you I think you get some of those Core Concepts in your like once you understand there’s these things like data types and like how the the function passes information between each of the

55:41 passes information between each of the the lines in the in the code once you get over those initial hurdles then you can start reading other people’s language I would say if I was going to spend time on a skill I would be spending time on finding other functions and reading them as if you were and writing your own lines comments what is this line doing what is this line doing what is this line doing because this amount of stuff I’ve learned from Chris Webb on his M code awesome awesome on GitHub with different functions and things that people already written I’ve learned a lot from them as

56:12 written I’ve learned a lot from them as well so that’s what that’s how I learn and so to me this is where I’m like Microsoft’s missing the boat here because there are people in the community who understand things way better than I do I need access to their content I need to understand what they’re doing so I can learn it as well and having a tool or tools that support that would make all this custom function writing stuff a whole lot better so I definitely think you need to use it I’m still very torn on where and what scale and what size scale you need to use all these things for and when you when do you transition into an I. T organization

56:44 you transition into an I. T organization versus keep it the business and it’s not it’s a great story but it’s still a messy story in my mind yeah I agree so Mike what does our future Overlord think about how we can reuse power query functions and queries so I started asking a couple questions here so I said I try to ask the question for chat GPT what is the best way to learn about power query functions in power bi and so it did it did a pretty good job but then I went over to the the Bing chat and tried to ask it a couple more questions

57:14 questions and so I’ll yeah just for fun just for fun so the the standard chat GPT answer was follow these steps go check out the Microsoft documentation they’re definitely online tutorials and courses go check those out as well in J Engage The Power bi Community which was great so go to community. powerbi. com check out their ask question there’s a whole section on the community that’s talking about M and power query so you should be able to go there and look and ask questions there definitely check out blogs and websites I I would say that’s something

57:45 websites I I would say that’s something I’ve very much have used this was a good example here practice with sample data to gain hands-on experience place a build a sample data set create a small amount of data create small projects or scenarios where you can require that the data transformation occurs and is manipulated Microsoft also provides some sample data sets but you can go to Kegel or data. data. gov and you can use sample data sets from there so I thought I thought that was a very relevant example like yeah that’s what I do I start with

58:16 like yeah that’s what I do I start with a very small data set that has a very specific problem and I work on that and don’t try to build your custom function in your massive 1 million Road table initially try and solve it locally first on a smaller set and then figure out what that function looks like to reuse it in a bigger context I thought that was good was good experiment Explorer don’t hesitate to experiment with power query functions and in power query the best way to learn is hands-on experience and trial by error there we go I want to see everyone else’s code and so I can learn from that so that’s that was my example right there at the end

58:46 there at the end the the other thing I asked about chat jpt I said said chat GPT have you heard about it’s it’s not about the cell because so it says yes I’ve heard of it’s not about the cell it’s a website that provides context on how to learn power power bi and Excel I was like all right good good who is the author of it’s not about the cell the author is Alex Powers he’s a program manager for Microsoft where he spends his days crunchy numbers and his Knights torturing his wife and cats with

59:16 Knights torturing his wife and cats with the sound of keyboard Strokes so you’re knowing by the overlord you you have been scanned by the the overlord and you are now on its radar so I I’ve definitely on all those responses I gave it a big thumbs up inside the chat GPT engine so I’m already influencing the algorithm towards the it’s not about the cell website so anyways hilarious I thought I would throw that one in there as well anyways we all appreciate your time the chat of our chat here not chat GPT

59:47 the chat of our chat here not chat GPT the chat comments have been on fire everyone’s been really talking a lot there’s a lot of comments going on here so thank you very much for chat for jumping in and talking and communicating it’s been super fun a lot of great points points thank you Greg baldini and Alex powers for really jumping in here and helping us out as well you have corrected me as far as the number of rows you should be concerned about there’s there is people in chat that are loading hundreds of millions of rows into Power query so my my 10 million rows was an understatement of

60:17 10 million rows was an understatement of what you can do in power query number so so with with that we’ll say thank you so much for listening if you like this episode if you like talking about this stuff if you like investing your time and and spending a little bit of effort learning about more things that water cooler conversation around Powerbag we’d really appreciate it if you share it on social media or talk to other people at your work or your business share that with them that you found an interesting podcast that’s helping you have fun laugh enjoy it’s good content but also learn some things and

60:48 content but also learn some things and maybe hopefully pull away some items from this that you can then take back to your your daily workflow and and be better more efficient data engineer or RBI developer or whatever you are in your in your work with data Tommy where else can you find the podcast you can find it anywhere it’s available on Apple Spotify make sure to subscribe Oliver rating helps us out a ton join the conversation live every Tuesday and Thursday at all of power bi tips social media channels we appreciate you all very much thank you so much and we’ll see you next time

Thank You

Thanks for listening to the Explicit Measures Podcast. If you want more episodes, subscribe here: https://powerbi.tips/podcast