Track Action items (Source file gets updated weekly)

[DairyG1]

Hello All,

Hoping you can give me some options.

I have an order list (xlsx) where action items are added to the file to track.

The file changes weekly with new orders added.

I created a connection to the live order file using power query and added a column to track the action items. 

As the connection is refreshed weekly, new orders get added and actions items no longer align to the previous weeks orders. They shift to another order which is totally incorrect

Can anyone suggest approach to keep the actions items aligned whilst allowing a refresh to pick up the new orders the next week

Hope i am clear, thank you for your time.

[#2]

@DairyG1 

That's only if you have unique ID:s for the records. The trick is to query again returned by it table from spreadsheet and merge with itself on ID. That will sync with manually added column. Expand it after the merging, you will have combined query.

Please note all above shall be done within one query, not as separate queries.

[#3]

@SergeiBaklan - Thank you for taking the time to respond.
I'm pretty new to power query would you be able to expand on the detail above or link some examples? I will try this from my side this morning.
Thank you

[#4]

@DairyG1 

Please check Self Referencing Tables in Power Query - Excelerator BI