Data Comparison take five using Power Query
This is a series of articles with solutions that evolve as Excel evolves.
- Data Comparison take one, AND or EXACT
- Data Comparison take two, EQUALS
- Data Comparison take three, EQUALS and HYPERLINK
- Data Comparison take four, PivotTable
- Data Comparison take five, Power Query
Excel 365
Do you have two sets of data, with a common column, where you want to compare amounts and find remaining balances? Essentially reconciling two sets of data.
Here is way to do that using Power Query.
Overview
With two sets of data linked by a common value
Power Query will import the data and append the two sets of data together.
The data will be grouped by the common value, filtered and exported to Excel.
The remaining amounts are the “unreconciled” amounts
Import the data
With your first set of data in a table, saved in a Workbook.
Navigate to the Data menu tab and click on Get Data -> From File -> From Workbook
Select the file with the data and click on the Import button.
Select the Table with the data and click on the Load button.
Repeat the same import steps with the second set of data.
Edit the second import
Right click on the second set of imported data and click on Table -> Edit Query
Highlight the column with the amount.
Select the Transform menu tab then click on Standard -> Multiply
When the Multiply window is displayed, enter -1 into the Value field.
Click on the OK button.
The sign convention on the amounts will be reversed. Navigate to the Home menu tab and click on Close & Load.
Append the data
Right click on the first set of imported data and click on Table -> Edit Query
Navigate to the Home menu tab and dropdown on Append Queries and select Append Queries as New.
Select your first data set in the First table field.
Select your second data set in the Second table field.
Click on the OK button.
The two data sets will be combined into a new data set.
If the column headings between the data sets differ but represent the same data, rename the columns to match during the import process. This will allow Power Query to append the data sets correctly.
Use Ctrl + Click to select the columns that you want to keep.
These should be the common data column and amount column at minimum.
For this process to work well, they should contain data that is common to both data sets. The Date column in the example in the example would be a bad column to select as the invoice and date and payment dates are different for a single invoice.
Navigate to the Home menu tab and click on Remove Columns dropdown -> Remove Other Columns.
Use Ctrl + Click to select all columns except for the amount column.
Navigate to the Home menu tab and click on Group by.
The Group By window will be displayed.
Enter a name into the New column name field.
Change the Operation field to Sum.
In the Column field select the amount column.
Click on the Ok button.
The data will be grouped.
The amount column will be removed and replaced by a new column.
Click on the new column dropdown, in the filter section untick the 0 value and click on the Ok button.
The zero values will be removed.
Navigate to the Home menu tab and click on Close & Load.
The data will be returned to Excel.
This method works very well, to match or reconcile, large data sets containing multiple data points with the same common values.
The process can be done without using Power Query by manually consolidating the data and using a PivotTable to group the data. The PivotTable gives you the ability to drill down to see the rows making up the remaining balances.
The advantage with this Power Query approach is that the source data can be updated and the process run in one step by navigating to Data menu tab and clicking on Refresh All. The multiple manual steps in the PivotTable method are avoided.