Power Query refresh removes manually entered data

[nryan]

Hello all,

I've used Power Query to build a table and I want to enter data into the table after the fact. When I do this however and then refresh the table all the data goes away. The conditional formatting and data validation remain but no data.

Here's some more info that may or may not matter:
The table I've set up in PQ is somewhat complex. It's a combination of 2 tables that I transformed quite a bit before appending one to the other. The first table is TrainingMatrix. The second is PersonnelList. Here's what it looks like:

TrainingMatrix Table:                                                       PersonnelList Table:
        A            B              C             D                                   A            B
1     Doc ID     Category X     Category Y     Category Z                    1      Name        Title
2      001                         Yes            Yes                        2       Tuck         Eng
3      002           Yes                          Yes                        3       Dale         Tech
4      003           Yes           Yes                                       4       Ned        Neighbor

PQ transforms and appends tables into this table:
        A            B           C       D        E
1     Doc ID      Category     Tuck     Dale     Ned
2      001           Y
3      001           Z
4      002           X          (This stuff will be
5      002           Z          filled in manually.)
6      003           X
7      003           Y

It's great because I can update the two source tables (add new rows/columns, add "Yes" to categories) and the Power Query table updates to match. I don't know if what I'm trying to do is possible. I hope so. I've been looking for solutions for a few hours now with no luck.

Thank you in advance. I've used this forum for VBA questions before and the responses have always helped me.
Cheers!

 

[nryan]

That video really helps. Infinity thanks to you Marcel. I'm going to try to implement this into my real tables.

You solved my problem sir. Thank you so much.

-Nick