Format all the M code of Activeworkbook.

modFormatMCode.bas

' Format all the M code of Activeworkbook.
' Dependency: Microsoft XML, v6.0 (C:\Windows\System32\msxml6.dll)
' Developer: Md.Ismail Hosen
' Email : [email protected]
' Whatsapp: +8801515649307
' LinkedIn : https://www.linkedin.com/in/md-ismail-hosen-b77500135/
' Facebook : https://www.facebook.com/mdismail.hosen.7
' Youtube : https://www.youtube.com/channel/UCL-q7_WvISkw0Ox9FRBBzmw

Public Sub FormatActiveWorkbookMCode()
    
    UpdateAllPowerQueryCode ActiveWorkbook.Name
    Application.StatusBar = False
    MsgBox "All Code has been formatted"
    
End Sub

Public Sub UpdateAllPowerQueryCode(Optional OfBook As String = vbNullString)
    
    Dim CurrentBook As Workbook
    If OfBook = vbNullString Then
        Set CurrentBook = ActiveWorkbook
    Else
        On Error Resume Next
        Set CurrentBook = Application.Workbooks(OfBook)
        On Error GoTo 0
    End If
    
    If CurrentBook Is Nothing Then Exit Sub
    Dim CurrentQuery As WorkbookQuery
    For Each CurrentQuery In CurrentBook.Queries
        Debug.Print " >> Formatting : " & CurrentQuery.Name
        CurrentQuery.Formula = GetFormattedMCode(CurrentQuery.Formula)
        Debug.Print
    Next CurrentQuery
        
End Sub

Public Function GetFormattedMCode(QueryCode As String) As String
    
    '@Ref : https://www.powerqueryformatter.com/api
    'Instanciate Http Caller and set Content Type
    Const API_URL As String = "https://m-formatter.azurewebsites.net/api/v2"
    Dim HttpCaller As MSXML2.XMLHTTP60
    Set HttpCaller = New MSXML2.XMLHTTP60
    HttpCaller.Open "POST", API_URL, False
    HttpCaller.setRequestHeader "Content-Type", "application/json"
    
    'Prepare BodyJSON and Send the json.
    Dim BodyJSONMap As Dictionary
    Set BodyJSONMap = New Scripting.Dictionary
    BodyJSONMap.Add "code", QueryCode
    BodyJSONMap.Add "resultType", "text"
    Dim BodyJSON As String
    BodyJSON = ConvertToJson(BodyJSONMap)
    HttpCaller.send BodyJSON
    
    Dim ResponseMap As Dictionary
    Set ResponseMap = ParseJson(HttpCaller.ResponseText)
    If ResponseMap.Item("success") Then
        Debug.Print " >> Formatted Successfully "
        Debug.Print
        GetFormattedMCode = ResponseMap.Item("result")
    Else
        Debug.Print " >> Error to get the Formatted code. Here is the response text"
        Debug.Print " >> " & HttpCaller.ResponseText
        Debug.Print " >> Return Back Original Code"
        Debug.Print
        GetFormattedMCode = QueryCode
    End If
    
End Function