PowerShell, ImportExcel, and Formatting DAX?

ImportExcel is a really cool PowerShell module. I’ve used it before to export data from SQL Server and then upload that data to Power BI Report Server. Chaining things together with PowerShell made it relatively straightforward to implement and the code was easy to run.

A while back I posted a video on how to reformat all of your DAX queries in Excel with VBA. I was reminded of this video after looking at examples of ImportExcel and how you could use it. One option was to make an Macro-Enabled Excel file with VBA modules automatically.

I wondered: Could you use ImportExcel to automatically make an Excel with this VBA code? Turns out you can.

The Code

I had to make a few tweaks to the VBA. I had used early-binding, where you define your references up front by checking them off in the VBA editor. Switching to late-binding made this possible as I did not want to check off references manually.

Here is the gist with the entire PowerShell code.

The first thing you want to do is open PowerShell and run this command to install ImportExcel:

Install-Module -Name ImportExcel

Next, save the code in the gist as a .ps1 file to your computer. Afterwards, right-click it and run it with PowerShell to test it out.

Make a dummy table and then add some dummy DAX measures. To run the macro, simply run it like this:

Wrap Up

There may be some cool applications for this. this could be handy in situations where you have an ad hoc analysis to do and need some VBA to assist. Rather than making a whole Add-In, firing up a ready-made PowerShell script could get you off and running without the need of an Add-In that may have way more than you really need.