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



## lezawang (Jul 19, 2021)

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 (Jul 19, 2021)

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 (Jul 19, 2021)

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


----------



## Momentman (Jul 20, 2021)

lezawang said:


> 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 (Jul 20, 2021)

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 (Jul 20, 2021)

lezawang said:


> 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 (Jul 20, 2021)

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 (Jul 20, 2021)

alansidman said:


> 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 (Jul 20, 2021)

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 (Jul 20, 2021)

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


----------



## lezawang (Jul 19, 2021)

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 (Jul 20, 2021)

lezawang said:


> 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.


Once you load once from PQ, "Close and load to" becomes greyed out. The workaround to at least get to load to a different location or o choose a different option is how @alansidman describes it


----------

