Forum Discussion
Query on Load to Worksheet adding Extra Columns to ListObject Table
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.
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.
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.