As a follow up to a previous post on how to call an API and format all of your Power Query code in Excel, here is a walk through on how to do the same thing with DAX queries in Power Pivot.
Add a reference to the Microsoft WinHTTP Services
Add a reference to the Microsoft HTML Object Library
After that, there are three pieces of code that work together to make this happen. Together, they loop through all of your measures and reformats them using an API call to www.daxformatter.com.
The first piece makes the call to the API:
Private Function DAXFormatter(name As String, formula As String) As String ' Set url and parameters Dim url As String Dim parameters As String Dim response As String Dim strName As String Dim strFormula As String Dim request As New WinHttpRequest On Error Resume Next strName = Replace(name, " ", "") strFormula = Replace(formula, " ", "") url = "https://www.daxformatter.com/" parameters = "?fx=" & strName & ":=" & strFormula & "&r=US&embed=1" Debug.Print url & parameters ' Send Request request.Open "GET", url & parameters 'request.SetRequestHeader "[NAME]", "[VALUE]" ' OPTIONAL request.Send If request.Status <> 200 Then MsgBox "Error: " & request.responseText Exit Function End If 'Call function to parse response response = ProcessDAXHTML(request.responseText) DAXFormatter = Split(response, ":=")(1) End Function
The second piece is a function that processes the response once it is received:
Private Function ProcessDAXHTML(text As String) As String Dim html As New HTMLDocument Dim resp As String On Error Resume Next html.body.innerHTML = text With html resp = .getElementsByClassName("formatted")(0).outerText End With ProcessDAXHTML = resp End Function
The last starts the process by looping through all of your measures, looping through them, calling the API and passing the DAX, and then saving the reformatted version:
Public Sub ReformatDAX() Dim mm As ModelMeasures Dim m As ModelMeasure On Error Resume Next Set mm = ThisWorkbook.Model.ModelMeasures 'For each measure in the model, Format the measure For Each m In mm m.formula = DAXFormatter(m.name, m.formula) Next m End Sub
Here is a quick video walking you through it: