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?
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.
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
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!