Can data from two systems be reconciled automatically?
Efficiency question :
It is very common to use and reconcile data from several systems. However, that data is not always presented in formats that are directly comparable.
Before it can be reconciled, the data must therefore be prepared and brought into a common format. It can then be compared, combined or analyzed across the different systems.
Power Query is particularly well suited to this work because it can retrieve the different sources and perform the required transformations step by step before the data is reconciled.
Key considerations:
The main consideration is formatting data from the different systems so that it becomes comparable.
Fields, formats or the way certain information is presented may vary from one source to another. The required transformations must therefore be determined before the data can be reconciled correctly.
Power Query can build this transformation sequence and apply it each time the data is refreshed.
Challenges:
Differences may exist between data from the various systems. Some may require additional transformations before the information can be compared.
Power Query’s M language can perform these transformations step by step. It can be used to standardize or normalize the format of data from different sources.
The challenge is to identify the differences correctly and define the transformations required to obtain comparable data.
Technology:
Power Query, which is integrated into Microsoft Excel, is particularly well suited to retrieving, transforming and reconciling data from multiple systems.
Its M language defines the steps needed to bring data into a common format before it is compared or combined.
At the time this sheet was written, in September 2026, Power Query is integrated into Microsoft Excel and using it in Excel does not require additional licensing fees.
Developer's advice:
When differences between two systems are significant or many variables must be considered, avoid building a solution that requires users to master Power Query or its M language.
Instead, a mapping table can establish relationships between data from the two sources. It can show the values used in the first system and the corresponding values in the second.
The transformations can then be built directly into the Power Query query. Users no longer need to intervene in the processing steps; they can simply refresh the query to retrieve, transform and reconcile the new data.
This approach keeps processing complexity inside the solution while giving users a simple process to run.