A little automation goes a long ways!

P3 Community Discovering Formatting

SQLBI DAX FormatterHello P3 Nation, (can this be a thing?)…I’m excited to show you a piece of software that I’m confident near 100% of you will utilize! I’m here to talk to you about an amazing piece of software provided by the industry professionals over at SQLBI. Among the many great services, training’s, software, & utilities they provide is a powerful tool called DAX Formatter. Honesty the first time I came across this online I felt like a caveman discovering a new tool, and now I…

Use It. Every. Day.

We’ve all taken the easy route before…
PowerPivot DAX Formula Window

Like many of my fellow PowerPivot, DAX, and M language enthusiasts I do my best to keep my workbooks and my code neat, orderly, and I even use inline comments if I’m feeling dedicated to my craft. Sounds great on paper doesn’t it? However many of us sometimes don’t apply as much effort as we’d like to give ourselves credit for and forgo the formatting or notes.

Forgoing code formatting has definitely come back to bite me in the err…posterior months later when I came back to review my formula, only to find a giant blob of code with no discernible breakouts.

If any of you have ever had code that looks like the monstrosity to my right (don’t judge me…), then prepare to hold on to your socks for a nearly automated solution (I promise I’m not being dramatic). Smile

Positively painful, nearly impossible to read, an abomination, not even something a mother could love, should be tossed out with the bathwater…ok ok enough with the clichéd analogies I promise.


We CAN make it “Cleaner, better, formatted, stronger.”

The wonderful software wizards over at SQLBI know how to turn the ugly into the beautiful. It’s honestly as easy as 1, 2, 3.

 

Step 1: Go to the DAXFormatter website:

SQLBI DAX Formatter Window

Step 2: Grab a chunk of code (that you’re honestly considering giving up for adoption at this point) and paste it into the editor window:

SQLBI DAX Formatter Window With Text

Step 3: Press the MAGICAL format button and watch the beast turn into the beauty:

SQLBI DAX Formatter Window AFTER Formatting

 

You’re then given a list of options to then export this newly transformed formula anywhere you need it. It’s really that simple! If I was to attempt to format my formula to this standard manually it would easily have taken me MUCH LONGER, and even then I doubt it would have looked this clean. This tool has saved me innumerable hours each month and I find this useful for at least 80% of my DAX formulas.

But wait…there’s more!

Not only do we have this fabulous website, the engineering wizards at SQLBI have also gifted us with this tool (and so MANY others) built right into an Excel add-in, let’s all take a moment to let the mic finish dropping… This wonderful tool set is called Power Pivot Utilities and I cannot emphasize enough how useful its features are. For the purpose of this post I’m only mentioning the DAX Formatter feature but don’t worry, I’ll cover the rest of this tool’s best features in a future post. Oh and did I mention that this add-in is…

COMPLETELY FREE!

Excel PP Utilities Ribbon DAX Formatter

Don’t you love it when you can have your cake and eat it too? Once you’ve installed the add-in for Excel you’ll see a new ribbon called PP Utilities. Within that tab you’ll find a checkbox for “Auto DAX formatting”. This will auto-format all your DAX Measures and Calculated Columns whenever you refresh the workbook…can’t get anymore convenient than that! With that ladies and gentleman until next time. Hopefully this tool will be as useful to you as it has been to me. Smile

  Subscribe to PowerPivotPro!
X

Subscribe

Reid Havens

Reid Havens is a Principal Consultant for PowerPivotPro and the Owner of Havens Consulting Inc. His main goal is to collaborate with individuals and organizations by helping them analyze data to understand their business. Using his formal backgrounds in technology and organizational management Reid has worked with various local and multi-national companies. He is also an adjunct professor at Bellevue College, guest lectures at the University of Washington, and provides corporate trainings centered around teaching Business Intelligence, Reporting, & Design. 

This Post Has 12 Comments

  1. Great post Reid! I too found this tool and not only set it as a favorite…I set a hotkey on my mouse to point this this website 🙂

    I love that it’s part of PowerPivotUtilities, and I’ve actually spoken to MS about including it in Power BI (the one place it’s missing).

    I’ll add a new idea on the community forum (if it doesn’t exist already). This is HUGE and an amazing gift from The Italians. Thanks again Alberto and Marco!

    1. I agree! Having a built in formatter would go a long way to help people understand how DAX walks through a query. The PBI team is pretty good about listening to user input so fingers crossed it gets enough of a voice!

  2. Great post! Dax studio is another great tool which also works with power bi desktop. I learned about it from same brilliant SQLBI site. I actually use DAX studio to write all my complex formulas and then paste them to the client tool.

  3. Hi James, I’ve also had that issue. It’s something I’ve seen happen with multiple Excel add-ins from various companies, so I suspect it might be more of a systemic issue with Excel rather than a problem with just Power Pivot Utilities. Hopefully it’s something that gets solved in the future.

  4. Hi guys, thank you very much for the kind words about DAX Formatter and Power Pivot Utilities! I would like to highlight that all the credits for Power Pivot Utilities go to Bertrand d’Arbounneau, from Paris. He was one of my students there, and after a few months he came up with this tool and… it was too good to not share with the community! We just published it! 🙂
    He internally uses DAX Formatter for the code formatting, but all the VBA code is from Bertrand!

  5. I agree that these tools are great! I use Dax studio for Power BI Desktop and import the measures and columns of templates and finished models into excel for reference or rebuild of PBIX files. Thanks Bertrand & Marco!!

Leave a Comment or Question