Formatting M Code with Power Automate Desktop

Previously, I had been using Github Copilot to help me generate code for a Visual Basic app in Visual Studio. I got pretty far. Farther than I could’ve imagined. However, it increasingly became harder to develop a consistent theme for the app. I wanted to make things that helped with Power Query development, but a lot of what I needed to do involved manipulating the screen through UI automation.

What do I mean? As far as I can tell, there is not a great way of adding Power Query code programmatically as you can with DAX. I could be wrong, but for the life of me I could not really find a good way to do it. I could do all sorts of things to manipulate M code, but I could not find a way to add Power Query to my Power BI file from this external tool. As far as I know, this is not supported.

So what am I left with? Manipulating the UI. What could I use to do this? All sorts of things, but I tried Power Automate Desktop. I got pretty far, but noticed over time that workflows would break, probably because I referenced something on the screen that is variable. I was able to open Power Query and add M code.

Stepping back, I hope to continue using this, as I really see Power Automate Desktop as another tool in the toolbox for anyone working with the Power Platform and Power BI.

So in this article, I will show you how to format M code with Power Automate Desktop.

The Workflow

It’s a pretty simple one:

• Add an input variable called Code
• Set a variable equal to this PowerFx function which escape double-quotes for PowerShell:

=Substitute(Code,Char(34),Concatenate("`", Char(34)) )

• Run a PowerShell script
• Copy the output to your clipboard

How Can I Use This?

• Create a Desktop Flow
• Make sure it is PowerFx enabled

• On the far right, click the {X} icon

• Add an Input variable named Code. Click Save.

• In the middle area, click the white area and paste the code from this Gist
• Save the workflow and run.

Running It

Here is a video running the workflow:

Wrap Up

I think Power Automate Desktop (PAD) may be a good way to streamline workflows. It was a little weird at the start, but once I figured out how to pass things back-and-forth between PowerShell and PAD, it was relatively easy to use the PowerShell code here.