Duplicate vs Reference Power Queries

[Raddle]

Well they say that Power Query is nothing without control ... (couldn't resist soz) but I am lacking the control in this case.

So I have a query say ten columns of data.
User wants a subset of this data with only 5 columns.
I created a PQ reference to the original query and removed the 5 unwanted columns.

I then have a script which loads all queries beautifully into tables, BUT for the subset query it loads all ten original cols into the table, not the 5 that I trimmed it to.

I have an array with all the query names and then this script

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & item & ";Extended Properties=""""" _
, Destination:=Range("$b$5")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM " & item & "") ' this is where you need to concatenate the array value name
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = item ' this doesn't need concatenating
.Refresh BackgroundQuery:=False
.TableStyle = "TableStyleMedium10"
.AutoFilter = False
.ShowTotals = True

Is there anything you can suggest for a neat solution here? Is the problem the SELECT * I wonder ?

I am otherwise left with either a full dupe of the query, and then remove the unwanted cols, or a macro to dupe the tab and then remove the cols which seems daft.

Thanking you in advance.

 

[Raddle]

OK so ... as per, my own fault entirely. If you have a SELECT * in the create table routine, you will obvs get ALL cols. What is needed is a second 'create' routine, with just the required cols named.

For clarity this is nothing to do with Duplicate queries vs References, this is about how I was transitioning the query into the Excel table.

Sorry to waste folks' time.