question

sifar-6363 avatar image
0 Votes"
sifar-6363 asked erinding-msft commented

Query on Load to Worksheet adding Extra Columns to ListObject Table

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 behavior or a Problem with the Query itself. It would be nice if someone could review the attached sample workbook, Query 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:
41507-test.gif
Here are the files:

Sample CSV files

Sample Test workbook


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


power-query-not-supportedoffice-excel-itpro
test.gif (2.1 MiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@sifar-6363
Had a quick look at your case. Can't repro the issue with Excel 365 v2010 Build 13328.20408 => Can't help

0 Votes 0 ·

1 Answer

erinding-msft avatar image
0 Votes"
erinding-msft answered erinding-msft commented

@sifar-6363

I didn’t reproduce your issue with Excel 2016 Professional Plus on my test environment.

You could try to open Excel in safe mode to eliminate interference from add-ins and startup items.
Press Windows+R, type excel /safe, click OK.

If nothing is wrong when opening Excel in safe mode:

  • Move the files in C:\Users\<User Name>\AppData\Roaming\Microsoft\Excel\XLSTART to other location temporarily, then check if the issue still exists.

  • If it doesn't work, go to File>Options>Add-Ins. Towards the bottom of the window, where it says Manage, click the Go button. In the dialog box, uncheck the add-ins to disable them.

Besides, you could also create a copy of the Query data to check the issue.

Regards,
Erin


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@sifar-6363

Have you checked if my answer is helpful for you?
If you have any updates or other problems, please let me know.

0 Votes 0 ·