September 3, 2024

Power Query Idea: Import M Code extensions from a Power Query Gallery

by

…and then Fabricus wrote the idea down into the scroll…

I posted an idea to LinkedIn recently regarding ensuring that there is parity between Power Shell and Power Query. What do I mean by that? Basically, with Power Shell you can import community libraries from the Power Shell Gallery and save time developing your own custom solutions, searching the internet, or copy and pasting from who-knows-where. I have benefited from this community-supported feature.

Now, Power Query has what I would say is a vibrant community of people that can do crazy stuff with M. However, there is no comparable feature and gallery for M. If you are proficient in M, it is after digesting a lot of content, maybe paying for training, and getting books like “The Definitive Guide to M”.

But let’s face it: not everyone has the time for that or even the resources. I was in a position before that had zero dollars budgeted for purchasing training. I was still tasked with trying to find ways to automate and streamline things. I wasn’t IT, but I was asked to do IT/Data things.

Back then, I could write some VBA and if I needed to figure something out, I could either ask a colleague or wade into the abyss that is the internet. While either Mr. Excel or StackOverflow often had the answer, I often did not find a ready-to-go VBA Class that I could use.

The Idea

The idea I posted has 5 pieces (I am high maintenance). None of them are really new ideas at all. I watch and read a lot of content (a lot). I have heard or read someone/multiple people talking about one or more of these. So, I take no credit for them.

The idea:

  • Create functionality where M is used to import extension files to wherever it needs to go so that you can use custom extensions easily whenever M is used.
  • Similar to the concept of an environment in Python, the code could be local to the project (.pbip), the workspace, or the solution (Power Platform dataflows).
  • For Power BI desktop, create the functionality to easily search the Power Query gallery and import extensions to your project or add extensions that are global for your Fabric workspace
  • For Fabric/Power BI Service workspaces, create the functionality to easily add packages that are global for that workspace and well as easily searching the Power Query gallery and importing extensions
  • For Power Platform, create the functionality to add M extensions to your solution for the Power Query Gallery and any dataflows in that solution should be able to use functions in that extension

Discussion

There are a lot of moving parts here, so I figured I would explain a little more.

Power Query is not just Power BI. Obviously, you can use it in Excel. You can create a dataflow in Fabric. You can also create a dataflow in Dataverse. Additionally, you can use it in Power Automate. If you are still using SQL Server Integration Services (SSIS), you can use it there too.

So, M touches way more things than DAX does. But in terms of functionality, support, and third-party apps that work with Power BI and other Microsoft apps, M does not have the same level of “things” out there to help you. There are exceptions (notably Power Query How – Home of the M Language), but there is not a whole lot. DAX obviously has a whole suite of tools made and maintained by the SQLBI folks and Measure Killer and a bunch of other ones I am leaving out, but nothing very specific (that I can think of).

If you do have the time to learn to be proficient in M in spite of this, you can find yourself doing a lot of cool things. But the UX from a developer/person-who-gets-it-done perspective can be improved. While you can find a lot of resources out there to help you, including custom functions that others have developed, it can be a time suck. This is where extensions and community comes in. The Power Shell community has done it, why can’t we have the functionality so that the Power Query community can too?

From a technical standpoint, there are probably things that need to be worked out. When you import an extension to a Power Platform solution, how does it get referenced? Can it reference environment variables? If you import an extension to a Fabric Workspace, how does it get defined so that any dataflow can use it? How can you look up extensions in the Power Query Gallery and import them into Power BI Desktop? In a Fabric Workspace? In a Fabric Tenant (where any workspace can reference them)? In a Power Platform solution? The list goes on…

That being said, just pieces of the idea would do a lot of good in my opinion.

Perusing Fabric Ideas

Of course, I knew that there were ideas out there that already mentioned some, most, or all of what I posted. I used Power Query (of course I did) to scrape the site and did my best to gather them all up. If you made it this far and have a little more time, consider upvoting one or more of the following. Many of them are directly related to the idea I posted, some presuppose that you could do what I posted, others depending on a path chosen could be check off along the way.

Closing Thoughts

Again, I did not have an original thought here. I essentially reiterated things other people have said over the years. This may be one of those things that get lost in prioritization and I am assuming the backlog of features to develop is long. I figured though it would still be a good exercise to try and put all of this together, since the benefit of something like this would greatly improve the collective coding experience with Power Query.

Tags:

Comments are closed.

You may also like