Recording Power Query actions

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a workbook called Data.xlsx that contains a single column of numerical data, numbers 1 to 10 in cells A1 through to A10.

In another workbook, I start by recording a macro, taking these steps:

1. Clicked Data
2. Get Data
3. From File
4. From Workbook
5. Chose the file, Data.xlsx and clicked Import
6. Selected the worksheet (Sheet1) containing the data
7. Clicked Transform Data
8. Clicked the filter dropdown and selected greater than 3
9. Clicked OK and Close and Load

Stopped the macro recording button.

This is the code generated:

Code:
Option Explicit

Sub Macro1()

    Range("A1").Select
    ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\MyFolder\data.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Sheet1_Sheet,{{""Column1"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", each [Column1] > 3)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sheet1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Sheet1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Sheet1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

However when I try to run the above code, I get an error message:

Code:
Run-time error

A query with the name 'Sheet1' already exists

What does it mean? Is it refering to the workbook Data.xlsx or the workbook which I am recording the macro? I tried changing the name of the worksheet in both workbooks (from Sheet1 to Sheet10) but the error remains.

Thanks
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I've never tried to record a macro of a Power Query -query. To me, the choice is usually either a macro or a Power Query.

Looks like the macro starts with a line that creates a new query called "Sheet1". If you recorded the macro looks like you forgot to delete the existing query. The error says there's already a query by that name. What if you tried just refreshing the existing query (Data => Refresh All)? Maybe you don't even need the macro? But if you do, change the query name into a variable (something with current date and/or time should be a good one because it's unique every time you run it) and see if your code works better.
 
Upvote 0
I've never tried to record a macro of a Power Query -query. To me, the choice is usually either a macro or a Power Query.

Looks like the macro starts with a line that creates a new query called "Sheet1". If you recorded the macro looks like you forgot to delete the existing query. The error says there's already a query by that name. What if you tried just refreshing the existing query (Data => Refresh All)? Maybe you don't even need the macro? But if you do, change the query name into a variable (something with current date and/or time should be a good one because it's unique every time you run it) and see if your code works better.

Thanks

The reason I recorded a macro that mimics my PQ actions is I want to know if it's possible to automate PQ.

Before PQ came along, I was writing VBA to manipulate data and the user could click a button to do that.

Now most of the manipulating is done using PQ, I suppose one would do that manually first, then write the VBA code to refresh.
 
Upvote 0
Now most of the manipulating is done using PQ, I suppose one would do that manually first, then write the VBA code to refresh.

This is exactly what I do.

This can be run from a command button if desired.

VBA Code:
Sub Macro4()
    ActiveWorkbook.RefreshAll
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top