editing a PQ, got "This won't work because it would move cells in a table on your worksheet"

[lezawang]

Hi
I have a table which has 3 columns. I created a PowerQ to then merged 2 columns. I loaded the new table back to excel sheet using "Close and Load to.."-->Existing table.
Then I decided to change/edit the name of a column, so I edited the name of a column in Power Q editor and then when click on Close and Load drop down arrow,

1) I only see "Close and Load" the other option "Close & Load to.. " option is grayed out ? Why is that?
2) When I clicked on "Close & Load", I got this error message: "This won't work because it would move cells in a table on your worksheet" Why is that

I just found that, if I "Load & Close" the PowerQ and let excel put it in a seprate sheet, then if I edit the PQ the then "Close & Load" I wont get that
error message but I also still can not select : "Close & Load To..." options

Thank you so much.

 

[Momentman]

One way in On Excel, You can go to Data>Queries/Connections, on the queries and connections pane that opens, right click your Query and select "Load to", that way you can change where the data gets loaded to, table, report, connection...

 

[lezawang]

Thaqnk you. I tried that but "where do you want to put the data?" option is grayed out. Thank you

 

[Momentman]

Thaqnk you. I tried that but "where do you want to put the data?" option is grayed out. Thank you

I guess it is loaded to a Table, do you want to load it to another table, because the idea is to load it differently from what already exists(Connection/Pivot..), hence it being greyed out

 

[lezawang]

Thank you very much once again. Could you please explain what do you mean by "load to a table" ? I did not understand this part. I usually click on Close & Load to.. then Select a cell in the sheet and click ok. Thank you.

 

[Momentman]

Thank you very much once again. Could you please explain what do you mean by "load to a table" ? I did not understand this part. I usually click on Close & Load to.. then Select a cell in the sheet and click ok. Thank you.

When you load to a "cell", you typically have more data than one cell, so that data loaded into the Excel sheet is stored in an Excel Table.

So my question is do you want to load to a different cell or what are you trying to do with the "Close and Load to"?

 

[alansidman]

If you wish to change the location, click on the Load to as Momentman has indicated. Load to a connection and then try it again and it will give you the option of loading to another location.

 

[lezawang]

If you wish to change the location, click on the Load to as Momentman has indicated. Load to a connection and then try it again and it will give you the option of loading to another location.

Thank you very much for your reply. Close and Load to --> Connection is grayed out. I can not select that option. Only Close&Load is available. Thank you.

 

[alansidman]

I am not referring to Close and Load in PQ. Click on Data-->Queries and Connections. Right click on the table shown in the new window. Select Load To. Select Connection. You will be advised that your link to your output table will be deleted. Click ok. Now repeat the process and now select where you want the output to go when you select Load To.

 

[lezawang]

Yes, I understand now. It works. Thank you all for the help. I appreciate it.