A little automation goes a long ways!
Hello 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.
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).
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:
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:
Step 3: Press the MAGICAL format button and watch the beast turn into the beauty:
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…
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.