Query on Load to Worksheet adding Extra Columns to ListObject Table

[Anonymous]

I am trying to place a Query Load at a specific Column and Row in my workbook. However, when the query loads the data to the worksheet, it adds extra columns to the created ListObject Table, depending upon the number of columns that were offset From Column A.

for .e.g.

1] If i Load the Query data to Cell B5, the Table adds 1 extra Column at the end.

2] If i Load the Query data to Cell D5, the Table adds 3 extra Columns at the end.

3] Also the Table always gets highlighted (Selected) whether loaded "Manually" OR via "VBA Code".

I am not sure if this is the default Query load behaviour or a Problem with the Query. Can someone take a look at the attached sample workbook and csv files and assess where the problem lies?

BTW, i am using Excel 2016 Pro 64 bit version and to be sure it is not an Excel issue, i just reset (repaired) my Excel to the default condition.

Here is a screenshot:

Here are the files:

Sample CSV files 

Sample Test workbook 

Hope someone has an idea and possible solution to remedy this issue.

[edhans · Accepted solution]

I am not 100% certain this is the issue @Anonymous but you are on a 4+ year old version of Excel and Power Query, both of which have had SUBSTANTIAL upgrades in the last few years. I've been using Power Query for longer than this though and I never recall having the issue you are seeing. I am also wondering if it is something to do with what you are doing with VBA. I saved the file as XLSX and stripped out all of the macros. The VBA code itself wouldn't matter, but if you are adding dropdowns and other visual elements it could mess up the formatting. I also use VBA to refresh tables, but I don't use VBA to manipulate the tables otherwise. 

You might head over to Mr Excel or an Excel dedicated site to see if anyone there has had this issue. Even though I use Excel, Power Query, and VBA, it is an increasingly small part of my world as I focus on Power Query and DAX within Power BI, so I don't want you going and upgrading just because I am not seeing the same issue on the Nov 2020 build of Excel. Having stripped out the VBA code could have impacted it - and I did that intentionally. I don't ever load anyone else's VBA code.

[edhans]

It is possible there is a bug in Excel 2016 from that long ago. I just did it in Excel from Office 365 and that extra column never shows up. Here is the file back. No matter what I do, that ghost column doesn't show up. The only thing I did was ensure all of your column data types were set. You had several "Any" types - I made them all text. But that shouldn't impact how it loads in an Excel table.

[Anonymous]

@edhansthanks for your reply. if you see my query, I have set the datatypes to text and number. It loads the colums correctly as text and number. Not sure if it appears different in office 365.

[edhans]

Hi @Anonymous - go back and look at your code. This is what your table shows before it loads into Excel:

I didn't go back and look at Ideas, but I did for Top UNIQ and Top ND. You did a simple Table.AddColumns() without the 4th optional parameter which sets the data type, and you didn't manually set it later, so those are coming across as abc/123, or "Any" type.

I am not at all certian that is your problem, but that is the only thing I changed, and of course, using a much later version of Excel.

[Anonymous]

Thanks. Is it better to upgrade to office 365 to resolve this issue? This is happening with every query. Also, when I add dropdowns (using listobject table having 1 row and 2 columns) via VBA code, above the table to filter this query on ideas or subgroups, as long as the workbook is open the dropdowns filter seamlessly. However, once I close and reopen the workbook, it shows corrupted. And when I do repair, it shows, some dialog message that some XML has been removed from the workbook from sheet3 i.e. this query sheet only.  The dropdowns table and the loaded data query table appears removed. I am not sure this corruption is happening because of these extra colums. So concerned.