Power Query and manual column that should not change when query refreshes

[querymonster]

Good afternoon,

I need help with the following: I have a query with data imported via an API that I want to refresh from time to time.
I want to add two columns next to that table:

• Column M "DADO DE ALTA" with the status of the provider (Yes/No)
• Column L "Fecha alta" with the date of the status change.

I want users to be able to change the data in columns M and L manually directly in the file. However, if the query is refreshed, my data end up completely mixed up as there is no reference between the columns and the query. I really don't know what to do anymore. Can anyone help? Thanks a bunch!!

 

[AliGW]

Here's the tutorial I followed to deal with this issue: https://exceleratorbi.com.au/self-referencing-tables-power-query/

 

[querymonster]

Thank you! I had already found this tutorial but that did not seem to work (or I did not understand well..).
Table 1 already comes from a PowerQuery and when I refresh, everything refreshes..
When I select “Close and Load To”, it only creates a connection and deletes table 2, so my comments are deleted as well.
Also, my excel version does not seem to have the "Power Query/ from Table" option (see screenshot). Do I need to be in the first table for that?

Thank you!!

 

[GraH - Guido]

Simple technique below:

Load your table with comments again in Power Query (Go iside this table then click "From Table"). Rename this query too "Qry_comments". Save that query as connection only.
Do not press refresh until the end of the procedure.

Revisit you current query, the one generating the table. At the final step, merge with the Qry_comments query. To make it work their needs to be a key column on which you can link both queries.

Expand the table, selecting only the comment column.

Saving this query causes a refresh.

It can happen you have now 2 comments column. You can delete the second one. Normally that's a one off action.

Now test the solution. Add somewhere a test comment and refresh all.

This should work.

 

[querymonster]

Genius!!! thank you!!!

 

[GraH - Guido]

Glad to help. Genius? That's a first