How to set up status tracker using Power Query without overwriting statuses?

[Anonymous]

How can I set up my Power Query so that when it refreshes, it doesn't overwrite my statuses AND any new Contacts added via refresh will have the Status="Not Started"

Here's the original:

Contact           Status

Person A         In Progress

Person B         Completed

Person C         Completed

This is what I would like it to look like AFTER I refresh the queries:

Contact           Status

Person A         In Progress

Person B         Completed

Person C         Completed

Person D         Not Started

Person E          Not Started

What I don't want it to do:

Contact           Status

Person A         Not Started

Person B         Not Started

Person C         Not Started

Person D         Not Started

Person E          Not Started

Thank you!

[BA_Pete]

Hi @Anonymous ,

Power Query is not a data warehouse. The refresh process is:

Wipe all existing data > Import latest data fom source > Perform transformations

If the [Status] values in your source data change, then that is all Power Query will be able to import.

My recommendation would be, if possible, to also import some other columns from the source that give you the information you need to reproduce the logic that evaluates the status for the row. You can then recreate the row status as you want it during the 'Perform transformations' stage of the refresh process.

Pete