Using Power Query for data reconciliation across two sources with a common ID
Summary
This outlines a practical method to reconcile and compare data fields across two systems, like Salesforce and XML sources, using Power Query with a common ID. It demonstrates unpivoting columns and merging queries with multi-column keys to identify differences clearly. Salesforce teams can apply this approach to validate and troubleshoot data discrepancies between Salesforce and external systems effectively. The technique leverages Power Query features not commonly known such as multi-column key joins and comparison custom columns.
Takeaways
- Unpivot other columns in each query to normalize data by Id and Attribute.
- Use multi-column merge keys (Id and Attribute) to join datasets in Power Query.
- Choose inner or outer joins to control which records to compare or include.
- Add custom columns to flag differences between source values for easy identification.
- This method helps reconcile and compare Salesforce data with external sources efficiently.
There are many ways to reconcile data, especiially for those with a sql db at their disposal. We had data in two systems that was supposed to be the same but had some discrepancies so I took a stab at sorting through it using power query and landed on a remarkably straightforward approach. In my case one source was XML and the other salesforce. Power query has some odd behaviors with how it handles some data types in xml files, but i will leave those out and hope XML will disappear and make the world a better and safer place. This exercise assumes you have two queries in a single power query file - one for each data set, and the column headers are the same in both files. If needed, there are ways to use a mapping file to transform the column names, which others have written about.