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.

I set up the query, load to sheet 2. I dont know how to repeat this once I do it once. 2nd time it will not let me load to sheet 2. I thought it would be handy to have a code to do this for me. — JonnyBoy
Yes. I've updated my code , I want to do a query on sheet 1 and show the results in sheet 2 using vba, please see new code. — JonnyBoy
@Qharr, 1) This is correct logic I want to do, refresh then load to sheet 2, however I am having trouble loading it to sheet 2. I am right clicking on the query from show pane, then load to, then table. But where the data should be loaded is not letting me click (see image above). 2) Is there a vba code to automate refresh and load to sheet 2? — JonnyBoy
@QHarr thanks. Do you know if there is an automatic way to update this? doesnt have to be vba? — JonnyBoy
@QHarr, I am doing an internal query from excel sheets, joining two sheets to one. (sheet 1) — JonnyBoy

mer_curius mer_curius · Accepted Answer · 2019-02-05T12:36:36

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.

How to automate a power query in VBA?

3 Answers