Excel Unplugged

Modifying Power Query M code with VBA

The core of this blog post is a VBA code that will create a copy of a Power Query M code, modify it, create a new sheet and load the result of the modified Query to the new Sheet. This code was written and tested in Excel 2016. And if you want to follow along, here is a blank file to follow along. We will start with a simple Excel Table on Sheet2.

Automating-Power-Query-M-Doce-With-VBA

Now we create a Query that will read from our Table. We select a cell within the Table and go to Data/FromTable/Range

Automating-Power-Query-M-Doce-With-VBA

This will open a Query Editor. The first thing we want to do is to change the name of the Query in the Properties section of the Query Settings Sidebar. This is a very important step, as we need to feed the name into a VBA Input Box (you could also hard-code it into VBA but I wanted to make it dynamic). I called it BasicQuery1

Automating-Power-Query-M-Doce-With-VBA

At this point we add an additional step to the query. In this step we will filter the Company column to keep only records of Company 1.

Automating-Power-Query-M-Doce-With-VBA

This step is the part of the Power Query M code that we will be changing with VBA. At this point if we choose the Advanced Editor command in Power Query Editor either on the Home tab or the View tab, we would see this

Automating-Power-Query-M-Doce-With-VBA

Now we’re finished with the Query and select Home/Close & Load dropdown and select Close and Load To…

Automating-Power-Query-M-Doce-With-VBA

And we select Only Create Connection and OK

Automating-Power-Query-M-Doce-With-VBA

With this, Phase One is completed. Now we have our basic Query that we will modify with VBA.

Automating-Power-Query-M-Doce-With-VBA

Now we get to Phase Two. We kick it off by creating an Excel Table with a list off all unique Companies from our original table.

Automating-Power-Query-M-Doce-With-VBA

We will step through this table with our VBA code and create a copy of our BasicQuery1 using these Companies as parameters for the final step (filtering of the Company column). We will also use the Company name for the name of the New Query and we will use it for the name of the newly created Worksheet where we will load the result of the Query. And now the VBA code:

Sub PQDynamicToSheets()

Set aw = ActiveWorkbook
Dim ws As Worksheet

a = InputBox("Name Of The Basic Query?", "Query Name")

'-----Read Basic Query
Set q = aw.Queries(a)
t = q.Formula

'-----Read Filters
Set tbl = Sheets("Sheet1").ListObjects(1)
u = tbl.Range.Rows.Count - 1

'-----Go Baby Go
For i = 1 To u

'-----Current Filter
    nm = Sheets("Sheet1").Range("A" & i + 1).Value
    p = nm
    
'-----UpdateQuery
    t1 = Replace(t, "Company 1", nm)

'-----Create New Query
    p = aw.Queries.Add(p, t1)
    
'-----Create A New Sheet To load Your Query Into
    Set ws = Sheets.Add(Before:=Worksheets(1))
    ws.Name = nm
    
'-----Change The Load Properties for New Query, so it loads on the new Sheet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & p & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & p & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    
Next i
End Sub

The code revolves around the For statement. So, For each and every row in the first table on Sheet1, it remembers the Company name from that row and feeds it into the Update Query step which replaces the “Company 1” part of the query to whichever company was read from the current row. Once that is done, the next step of the code creates a new Sheet and names it after the currently selected Company. The last step just changes the load properties of the Query. Keep in mind, that our Query is just a copy of BasicQuery1 and as such it only creates a connection and does not load the result of the query. This final step changes the goal of the load to a Table on the newly created WorkSheet.
So as we run our code, we get this

Automating-Power-Query-M-Doce-With-VBA

Four new Queries loaded to four new Sheets. Each with data just for that one Company. Eternal Happiness 😊

Comments 7

  1. Sandeep Kothari says:

    This is amazing! A combo of PQ (G&T), VBA & Pivot Table.
    Could you also share your finished file?

    1. @ Sandeep

      Sure thing,you can download the file with the link below

      http://excelunplugged.com/wp-content/uploads/2018/05/PQandVBA.xlsm

  2. Sandeep Kothari says:

    Great! Thanks.

  3. Jørgen Wulff Rasmussen says:

    Hi Gasper,

    Even if, we have been talking a lot over several years in the Amsterdam Summit, I completely forgot about your blog.

    This is a great article helping me in my Development for clients.

    best regards
    Jørgen
    http://www.exceldoktor.dk

    1. Hi Jørgen. Great to hear from you.

      So now you will never forget about Excel Unplugged 🙂

  4. Shlomi says:

    Wow! realy mind blow solution.
    It gave me of a possiable solution which I wanted to connect to web site in order to pull Rate of exchange BUT I want to keep the old ones as well, and not replace them with the new ones every time the query is running.
    with your method I can make VBA code to copy, every time the query running, to summary rate of exchnage sheet.
    Thanks!

    1. Now some Web sites give you an XML pull of rates that includes history and is faster than a Web pull, but I am glad that this article helped you!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: