How to automate a power query in VBA?

Question

I have data in sheet 1. Normally I go to power query and do my transformations, then close, and load to an existing sheet 2.

I would like to automate this using VBA, where I can just run my power query automatically and populate the transformation to sheet 2.

Macro recorder doesn't seem to allow me to record the steps. And there isn't much online about doing this.

Trying some simpler code:

Sub LoadToWorksheetOnly()

'Sub LoadToWorksheetOnly(query As WorkbookQuery, currentSheet As Worksheet)
    ' The usual VBA code to create ListObject with a Query Table
    ' The interface is not new, but looks how simple is the conneciton string of Power Query:
    ' "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name
     
    query = Sheets("Sheet6").Range("A1").value 'here is where my query from power query is. I put the text from power query avanced editor in another sheet cell.
    currentSheet = ActiveSheet.Name
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
        , Destination:=Sheets("target").Range("$A$1")).QueryTable
        .CommandType = xlCmdDefault
        .CommandText = Array("SELECT * FROM [" & query.Name & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    End With
     
End Sub

Here is my issue when trying to load to new sheet manually.

Answer 1

I disagree with the premise of the above answer; VBA is absolutely suitable for automating PowerQuery and is particularly efficient for repetitive work. The trick is to create first the query you need in PowerQuery, then use the Advanced Editor to capture the M. Copy it and store it, either in a cell in the workbook, or in a separate text file.

The method is described in detail by Gil Raviv. For convenience, I store my M in text files instead of the workbook and load it with:

Function LoadTextFile(FullFileName As String) As String
  With CreateObject("Scripting.FileSystemObject")
    LoadTextFile = .OpenTextFile(FullFileName, 1).readall
  End With 
End Function

The nice thing about text files is that they are independent of excel and can re-used by many workbooks.

Here is some M:

let
// load the reference file (variables are shown in capitals;  
// variable values are replaced with strings from the excel control workbook)
    Source = Excel.Workbook(File.Contents(PATH_AND_NAME), null, true),
    ImportSheet = Source{[Item=SHEET_NAME,Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(ImportSheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ACCOUNT", type text}})
in
    #"Changed Type"

Once loaded into VBA (from either workbook or text file), the M can be edited within VBA, for example by substituting placeholder words, or by using the M command names to locate and alter lines if necessary, e.g.

    ' create the M script to read the M file that will do the import
        M_Script = LoadTextFile(M_Source)

    ' insert the path
        M_Script = Replace(M_Script, "PATH_AND_NAME", """" & qSource & """") 

    ' insert the worksheet name
        If wksName <> "" Then M_Script = Replace(M_Script, "SHEET_NAME", """" & wksName & """")

The next step is to load the query. I do this using the technique described by Gil as follows:

Dim qry As WorkbookQuery

If DoesQueryExist(qName) Then 
    ' Deleting the query 
    Set qry = ThisWorkbook.Queries(qName) 
    qry.Delete 
End If 

Set qry = w.queries.Add(qName, M_Script, qSource)

' We check if data should be loaded to Data Model 
shouldLoadToDataModel = ThisWorkbook.Worksheets(1).Cells(13, "D") 

' We check if data should be loaded to worksheet 
shouldLoadToWorksheet = ThisWorkbook.Worksheets(1).Cells(13, "E") 

If shouldLoadToWorksheet Then 
    ' We add a new worksheet with the same name as the Power Query query 
    Set currentSheet = Sheets.Add(After:=ActiveSheet) 
    currentSheet.Name = qName 

    If Not shouldLoadToDataModel Then 
        ' Let's load to worksheet only 
        LoadToWorksheetOnly qry, currentSheet 
    Else 
        ' Let's load to worksheet and Data Model 
        LoadToWorksheetAndModel qry, currentSheet 
    End If 
ElseIf shouldLoadToDataModel Then 
    ' No need to load to worksheet, only Data Model 
    LoadToDataModel qry 
End If 

Gil's code allows for importing data to either the data model or a worksheet. The OP requires the second, and if the method is followed, the transformed data should appear in the worksheet.