I want to analyse the changes between two large datasets. (power query)

Question

I have a monthly report which has a set number of columns, 102, but it is an extract which gains new data every month. Currently at around 27,000 rows.

I would like to find a way to find any changes which have occurred in the dataset, such as information changes to existing information which has been updated as the month passed. And also find a way to work out which new rows have been added, i.e data which wasn't there in the previous month but is there in the new months data.

I have tried Power Query Merge function but I can't get it to work. I have also attempted Xlookups for each cell, but given the size of the dataset it seems a bit excessive.

Ideally I would be able to copy the new report into an excel sheet, or power BI each month and automatically have an output with all the changed cells and new rows added.

Any help would be much appreciated.

Answer 1

Hi @Toby

Power Query is used to process data from a source and automatically refresh the output when the source data changes. However, it doesn’t support tracking historical changes or maintaining a change log. Similarly, XLOOKUP is designed to retrieve values from processed data, but Excel itself doesn’t provide native functionality to track changes over time.

Since your requirement involves monitoring or logging changes, these tools may not fully meet your needs. You may want to consider Power Automate for this scenario. To receive more accurate and scenario‑specific guidance, I’d recommend starting a new thread in the Power Automate forum, where community members and moderators regularly focus on automation‑related use cases and can provide more targeted assistance.

Apologies for redirecting you to a different community. The current category primarily supports Office 365 related questions, and expertise around Power Automate may be limited here. Posting in the appropriate forum should help you get quicker and more relevant support.

Thank you for your patience and understanding, if you have any other questions or need further assistance, feel free to ask.  

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Answer 2

i think the safest path is: load both months into PQ, full‑outer merge on a stable biz‑key, then add a tiny row‑hash so PQ shows only rows whose hash moved before u diff the cols. just be sure the key is truly uniq first — that one check keeps the diff clean.