Reformatting DAX Queries in Power Pivot with VBA

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

Prerequisites:

Add a reference to the Microsoft HTML Object Library

VBA

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

Video

Here is a quick video walking you through it:

Bitnami