Smarter way to Compare 2 excel tabs?

[kevdragon1]

Hi,

I have software that generates reports. Part of my job is to see the difference between these 2 reports. So I am basically trying to compare version 1 to version 2.

I was wondering if there was a smart way that I can compare these reports. What I would like would be to have 3 tabs in the excel file. Tab 1 has Version 1, tab 2 has Version 2 and tab 3 is comparing both tabs.

Here is an example:

Version 1 :

Balance sheet	Dec 2020	Jan 2021
Asset 1	1000	1500
Asset 2	3000	3500

Version 2:

Balance sheet	Dec 2020	Jan 2021
Asset 1	1500	1000
Asset 2	3000	0
Asset 3	5000	6000

Compare:

Balance sheet	Dec 2020	Jan 2021
Asset 1	500	-500
Asset 2	0	-3500
Asset 3	5000	6000

Something like this.

As you can see the report can sometimes have different lines from version to version that was not included before (asset 3). Sometimes some "Balance sheet" items will disappear aslo.

The way I solved this was to copy the "Balance sheet" items from the most recent version (version 2) and then use an XLOOKUP to find the same items in the previous version. Then I would do a difference to see the impact.

I have reports going for 10 years....so there is a lot of formulas and I am posting because I wanted to see if there was a better way to do this.

Thx

 

[Alex Blakenburg]

Power Query would be ideal for this.
Below is Jeff Lenning (Excel University) with a 4.5 min video that will give you the idea of what you could do.
Since you want to show all values from both versions you will want to use a Full Outer Join.

The Explanatory web page for the Video is:
Slow to Fast 3 - Excel University

 

[kevdragon1]

Power Query would be ideal for this.
Below is Jeff Lenning (Excel University) with a 4.5 min video that will give you the idea of what you could do.
Since you want to show all values from both versions you will want to use a Full Outer Join.

The Explanatory web page for the Video is:
Slow to Fast 3 - Excel University

thank you... this worked partially. I was able to merge the 2 tables and I actually had all the lines from both versions. How do I do the difference between 2 merged columns?

Also with 2 tables and connections, this seems overkill. Isn't there an easier way?

 

[Alex Blakenburg]

You can combine the queries to make it into a single query but it would most likely mean you have to modify the actual M Code.

I took a slightly different approach using Append rather than Merge.
For the below to work unmodified you would need to make sure your headings are the same in both Versions

To use the below
• Give the tables the same name as I have.
• Load the 2 tables as connection only and leave the query name as being the table name.
• Go into Home > Advanced Editor and replace the code for these 2 queries with the code below under 1 & 2
• Create a blank query and copy in the code under item 3 below into that and close and load that query to Excel.
Each month just copy in the new data to the 2 tables and refresh the output query.

1) I called the Version1 table tblVersion1 and used this as the query name as well
Connection ONLY

let
    Source = Excel.CurrentWorkbook(){[Name="tblVersion1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Version", each "Version 1")
in
    #"Added Custom"

2) I called the Version2 table tblVersion2 and used this as the query name as well
Connection ONLY

let
    Source = Excel.CurrentWorkbook(){[Name="tblVersion2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Version", each "Version 2")
in
    #"Added Custom"

3) Then an Append Query that uses the above names tblVersion1 & 2
Close and Load to Excel

let
    Source = Table.Combine({tblVersion1, tblVersion2}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Balance sheet", "Version"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Version]), "Version", "Value", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Version 1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Version 2"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value1", "Subtraction", each [Version 2] - [Version 1], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"Version 1", "Version 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Subtraction", "Difference"}}),
    #"Pivoted Column1" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute]), "Attribute", "Difference", List.Sum)
in
    #"Pivoted Column1"

 

[kevdragon1]

You can combine the queries to make it into a single query but it would most likely mean you have to modify the actual M Code.

I took a slightly different approach using Append rather than Merge.
For the below to work unmodified you would need to make sure your headings are the same in both Versions

To use the below
• Give the tables the same name as I have.
• Load the 2 tables as connection only and leave the query name as being the table name.
• Go into Home > Advanced Editor and replace the code for these 2 queries with the code below under 1 & 2
• Create a blank query and copy in the code under item 3 below into that and close and load that query to Excel.
Each month just copy in the new data to the 2 tables and refresh the output query.

1) I called the Version1 table tblVersion1 and used this as the query name as well
Connection ONLY

let
    Source = Excel.CurrentWorkbook(){[Name="tblVersion1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Version", each "Version 1")
in
    #"Added Custom"

2) I called the Version2 table tblVersion2 and used this as the query name as well
Connection ONLY

let
    Source = Excel.CurrentWorkbook(){[Name="tblVersion2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Version", each "Version 2")
in
    #"Added Custom"

3) Then an Append Query that uses the above names tblVersion1 & 2
Close and Load to Excel

let
    Source = Table.Combine({tblVersion1, tblVersion2}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Balance sheet", "Version"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Version]), "Version", "Value", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Version 1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Version 2"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value1", "Subtraction", each [Version 2] - [Version 1], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"Version 1", "Version 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Subtraction", "Difference"}}),
    #"Pivoted Column1" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute]), "Attribute", "Difference", List.Sum)
in
    #"Pivoted Column1"

Hi,

Thank you for this.

Wondering what this code does as I was able to recreate this myself but these 2 lines I am not sure I understand. Thank you so much for your help

 #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Version 1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Version 2"}),