Forum Discussion
Compare 2 sheets with pivot or not what dropped and what is added in the new month
Steiny88 such tasks are easiest achieved with Power Query, as demonstrated in the attached file. Would that work for you?
Steiny88 such tasks are easiest achieved with Power Query, as demonstrated in the attached file. Would that work for you?
Riny_van_Eekelen I think it's working fine, but can you please tell me how you did it so I can follow the same steps in my actual worksheet?
Thank you!
Steiny88 In short, these were the steps I followed:
1) Format both data ranges as tables
2) Connect to each of them with Power Query (PQ)
3) Merge (as new) the old table with the new one, Left Anti Join
4) Merge (as new) the new table with the old one, Left Anti Join
5) Load both queries back to Excel.
A "left anti join" will only keep rows that exist in the first table. i.e. the one at the top in the Merge screen. Thus, in PQ, the first table that sits at the top is called Left. And the last one at the bottom is called Right. Don't ask me why 🙂
Thus in step 3) you identify the ones that were removed and in step 4 the ones that were added.
Now, if you are totally unfamiliar with PQ, you will not understand much of this. Then you need to put some effort into learning the basics. A good place to start would be on the site in the link below.
https://exceloffthegrid.com/power-query-introduction/
Riny_van_Eekelen No, I am familiar, I was just wondering about the merge how it works but I saw it so it's all good. Thank you very much!!!
