Power Query adds new column to original table after editing a column

[lezawang]

Hi
I have the table below, it has 4 columns: Item, Jan, Feb, Mar
I merged 2 columns in Power Query editor then loaded it to the sheet. The I edited the power Query and Column Jan label from Jan to Hello, then loaded
the table back to excel., What PQ did, added Jan2 to the original table? and it is an empty column! Why is that? Thank you very much

Book1
	A	B	C	D	E
1	Item	Jan	Jan2	Feb	Mar
2	item1	1		1	1
3	item2	1		1	1
4	item3	1		1	1
5	item4	1		1	1
6
7	Item	Hello	Merged
8	item1	1	11
9	item2	1	11
10	item3	1	11
11	item4	1	11
12
13
Sheet1

 

[RoryA]

You're saying that Power Query altered the source table?

 

[shaowu459]

I know your problem. First you upload your table to Excel, result is:

Then you changed column name "B" to "CC" in power query editor and load the table to Excel, you see there is an empty column added after column "B" in the source table:

I record the screen and it shows when you refresh, the result table expand itself and then delete a temporary column in the table. however, an empty column was added in the worksheet.

 

[lezawang]

I know your problem. First you upload your table to Excel, result is:
View attachment 43118

Then you changed column name "B" to "CC" in power query editor and load the table to Excel, you see there is an empty column added after column "B" in the source table:
View attachment 43119

I record the screen and it shows when you refresh, the result table expand itself and then delete a temporary column in the table. however, an empty column was added in the worksheet.
View attachment 43122

Yes that is what happend to me. Why that is happening. Now as you suggested, I refereshed the Query and to my surprised it added another empty columns to both the original table and to the table that power query created. Another refesh and adding another columns to both.. why is that? and how to avoid that? Thank you again

 

[shaowu459]

Yes that is what happend to me. Why that is happening. Now as you suggested, I refereshed the Query and to my surprised it added another empty columns to both the original table and to the table that power query created. Another refesh and adding another columns to both.. why is that? and how to avoid that? Thank you again

Upload the result table to G1 or other unused column or a new worksheet.

 

[RoryA]

It's generally not a good idea to stack tables underneath each other on the same sheet anyway. I suspect you have the 'Preserve column sort/filter/layout option' checked?