How to add an excel column that is in sync with power query table

[Martin sherk]

So i have a power query connection/Table in Excel, i added a new column called Comments (column F) and i fill it manually

The problem is this comments column is not in sync with my power query table and when i refresh the query, the comments column becomes messed up, how to make my comments column in sync with the power query connection/Table

Book1
	A	B	C	D	E	F
4	Invoice No.	Payer	Cus.	Currency	ES	Comments
5	1	A	E	USD	R	Paid
6	2	B	F	USD	W	waiting conf.
7	3	C	G	USD	E	check
8	4	D	H	USD	E	mail
Sheet1

 

[Matt Allington]

So i have a power query connection/Table in Excel, i added a new column called Comments (column F) and i fill it manually

The problem is this comments column is not in sync with my power query table and when i refresh the query, the comments column becomes messed up, how to make my comments column in sync with the power query connection/Table

See my article here Self Referencing Tables in Power Query

 

[Martin sherk]

See my article here Self Referencing Tables in Power Query

i was already reading and applying your article! the only article on the internet solving this issue. didn't know that you yourself will reply here!
what are the odds! haha .. thank you, kind sir! lifesaver.

 

[Martin sherk]

See my article here Self Referencing Tables in Power Query

May i ask you, if i have 4 tables under each other.. which propreties do u choose to not mess up the data or display error messages when you refresh queries?

does the above options are the best options?

 

[Matt Allington]

What do you mean "4 tables under each other"? Do you mean 4 source tables appended? The most important thing for this technique is you must have a unique key for each row that does not change. This means you can't add the key in PQ, it must be added at the source. Assuming you have a key in the tables, you could concatenate the file name and key name

 

[Martin sherk]

What do you mean "4 tables under each other"? Do you mean 4 source tables appended? The most important thing for this technique is you must have a unique key for each row that does not change. This means you can't add the key in PQ, it must be added at the source. Assuming you have a key in the tables, you could concatenate the file name and key name

i have 4 table queries under each other, and i wish to find a way where can i refresh all tables when the source data changes without messing up form or leaving extra unneeded rows.
Do you please have an article for what unique key means or a way to make each table unique?

Book5
	A	B	C	D	E
1	Code	Customer	Reference	-	*
2	A	F	SALARY	100	0
3	B	G	SALARY	100	0
4	C	H	SALARY	100	0
5	D	W	SALARY	100	0
6	E	S	SALARY	100	0
7
8
9
10
11	Code	Customer	Reference	Pay	-
12	A	F	SALARY	100	0
13	B	G	SALARY	100	0
14	C	H	SALARY	100	0
15	D	W	SALARY	100	0
16	E	S	SALARY	100	0
17
18
19
20
21	Code	Customer	Reference	Pay	-
22	A	F	SALARY	100
23	B	G	SALARY	100
24	C	H	SALARY	100
25	D	W	SALARY	100
26	E	S	SALARY	100
27
28
29
30	Code	Customer	Reference	Pay	-
31	A	F	SALARY	100
32	B	G	SALARY	100
33	C	H	SALARY	100
34	D	W	SALARY	100
35	E	S	SALARY	100
Sheet1

 

[Matt Allington]

In short, you can't. Query tables need the ability to grow. You should not place them underneath each other like this.