Batch Format Excel Queries With VBA

I realize that there are a lot of tools out there to help you with DAX, Power Query, and all sorts of Excel and Power BI things. I am sure any one of those tools does this more efficiently than what I am about to write about.

For folks that are using high end tools with Power BI: I realize that this is not going to knock your socks off. My work involves a ton of ad hoc analysis of data that I may receive via a CSV from another organization entirely. Excel continues to shine in many instances.

I tend to try to build out things just for the sake of just learning how to do it. So, formatting my Power Query code in Excel was something I wanted to do with an API. I asked myself, could I use my favorite Power Query formatter to do this?

PowerQueryFormatter.com has been my go-to. It reminds me of Poor Man’s SQL Formatter which I also use. I am a sucker for well-designed (and free) things.

I noticed it had an API. I fired up Postman and sent a POST request with some M. It was very easy to use. Then I thought, “How do I do this for all queries, without having to leave Excel?” Enter VBA.

Call APIs with VBA

Microsoft MVP Paul Kelly has a great starter video for doing this. He explains everything so clearly and even provides a template example that you can use. Here is the video:

One thing you will need is a JSON Formatting Library available here, which Paul also talks about in his video.

After watching the video, I wrote code to batch format all Power Queries. For safety’s sake, I added the same queries as new queries with “_formatted” appended to the end of the name. I will probably change that for myself, but in order to avoid angry emails I kept it safe for this article lol.

The Code

Here is the code from my API module:

Public Function FormatPQ(query As String)

    Dim strQ As String
    Dim jsonQ As String
    Dim body As String
    Dim fullbody() As Byte
    Dim strResponse As String
    Dim objResponse As Object
    Dim url As String
    Dim parameters As String
    Dim request As New WinHttpRequest
    
    On Error GoTo Err:
    
    'Grab the query text
    strQ = query
    
    'Convert to json
    jsonQ = JsonConverter.ConvertToJson(strQ)
    
    'Craft the body for the request
    body = Chr(123) & _
           """code""" & " : " & jsonQ & "," & _
           """includeComments""" & " : " & """true""" & "," & _
            """resultType""" & " : " & """text""" & _
            Chr(125)
    
    'Conversion so that the API will accept the request - unique to VBA
    fullbody = StrConv(body, vbFromUnicode)
    
    ' Set url and parameters
    url = "https://m-formatter.azurewebsites.net/api/v2"
  
    
    ' Send Request
    request.Open "POST", url, False
    request.setRequestHeader "Content-Type", "application/json"
    request.Send fullbody

    'If it is not accepted, tell the user
    If request.Status <> 200 Then
        MsgBox "Error: " & request.responseText
        Exit Function
    End If
    
    
    ' Get the response
    strResponse = StrConv(request.responseBody, vbUnicode)
    
    ' Parse the response
    Set objResponse = JsonConverter.ParseJson(strResponse)
    
    'Get the reformatted query
    strResponse = objResponse("result")
    
    'Return it reformatted
    FormatPQ = strResponse
    
Err:
    MsgBox "The following error occurred: " & Err.Description
    
End Function

Public Sub ReformatQueries()


    Dim wq As WorkbookQuery
    Dim strFormatted As String
    
    
    On Error GoTo Err:
    
    'Loop through each query in the workbook
    For Each wq In ActiveWorkbook.Queries
    
        'Reformat each query
        strFormatted = FormatPQ(wq.formula)
        
        'Add it as a new query - append _formatted to name
        Call modPowerFunctions.AddPowerQuery(wq.Name & "_formatted", strFormatted)
        
    Next wq
    

Err:
    MsgBox "The following error occurred: " & Err.Description

End Sub

I also reference a helper Sub (that I found online, forgot where) in another module (“modPowerFunctions.AddPowerQuery”):

Public Sub AddPowerQuery(formula_name As String, formula As String)
    
    On Error GoTo Err
    
    Dim myConnection As WorkbookConnection
    Dim mFormula As String
    Dim mFormulaName As String
    Dim query As WorkbookQuery

    mFormulaName = formula_name
    
    mFormula = formula
    
    mFormula = modTextFunctions.RemoveLeadingBreaks(mFormula)
    
    mFormula = modTextFunctions.RemoveTrailingBreaks(mFormula)
    
    Set query = ThisWorkbook.Queries.Add(mFormulaName, mFormula)

Done:
    Exit Sub

Err:
    MsgBox "The following error occurred: " & Err.Description
    
End Sub

End result:

Reformatted Power Query Code

Conclusion

Again, this may have been done in a more efficient way somewhere else and there are tools out there than make this simple. For my own development, I wanted to continue practicing API calls in an application I use all the time.

Next up, SQLBI’s DAX Formatter!

Bitnami