Compare two files and highlight differences

[rorozco]

Hello,

I need to compare two files (File A and FIle B) and highlight the differences (in File A).

The two Files may have different columns but I only want to compare two of the columns with the same name. See below for Column info:

Identification Number (ID)	Module	Function Block	Component(s)	Failure Rate
BC01	BPC	BC Connectors	CON1A	0.109617​
BC02	BPC	FC Connectors	CON1B	0.109617​
BC03	BPC	BC to FC Connectors	CON1C	0.109617​

I want to search Column A (Identification Number ID) each cell and Compare Column C (Function Block) row if anything has changed, if so then highlight it.

Typically we get a new set of data every few weeks and there are hundreds of rows. The Identification Number ID never changes but the content in the other columns might. How can I go by doing this easily and time efficient? Currently I try to compare row by row.

Thank you.

 

[alansidman]

I would join the two tables in Power Query with the unique identifier in Column A as the field to join on.

Once joined, in PQ add a column and compare Column C of each file to see if they match.

If you post some sample data for the second table, I will demonstrate the Mcode to make this happen.

 

[rorozco]

I would join the two tables in Power Query with the unique identifier in Column A as the field to join on.

Once joined, in PQ add a column and compare Column C of each file to see if they match.

If you post some sample data for the second table, I will demonstrate the Mcode to make this happen.

Hello Alan, see below for example of second table. Can you please show me how to do this in Mcode? I would appreciate it.

Identification Number (ID)	Module	Function Block	Component(s)	Failure Rate
BC01	BPC	BPC to FIC Connectors	CON1A	0.109617​
BC02	BPC	DTD Connector	CON1B	0.109617​
BC03	BPC	DTD Power Enable	CON1C	0.109617​

 

[alansidman]

Once you have brought each table into Power Query, then Merge as new. Here is the Mcode to produce the result below

let
    Source = Table.NestedJoin(Table1, {"Identification Number (ID)"}, Table2, {"Identification Number (ID)"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Function Block"}, {"Table2.Function Block"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Same/Different", each if[Function Block]=[Table2.Function Block] then "Same" else "Different")
in
    #"Added Custom"

Book10
	A	B	C	D	E	F	G
1	Identification Number (ID)	Module	Function Block	Component(s)	Failure Rate	Table2.Function Block	Same/Different
2	BC01	BPC	BC Connectors	CON1A	0.109617	BPC to FIC Connectors	Different
3	BC02	BPC	FC Connectors	CON1B	0.109617	DTD Connector	Different
4	BC03	BPC	BC to FC Connectors	CON1C	0.109617	DTD Power Enable	Different
Merge1

 

[rorozco]

Once you have brought each table into Power Query, then Merge as new. Here is the Mcode to produce the result below

let
    Source = Table.NestedJoin(Table1, {"Identification Number (ID)"}, Table2, {"Identification Number (ID)"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Function Block"}, {"Table2.Function Block"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Same/Different", each if[Function Block]=[Table2.Function Block] then "Same" else "Different")
in
    #"Added Custom"

Book10
	A	B	C	D	E	F	G
1	Identification Number (ID)	Module	Function Block	Component(s)	Failure Rate	Table2.Function Block	Same/Different
2	BC01	BPC	BC Connectors	CON1A	0.109617	BPC to FIC Connectors	Different
3	BC02	BPC	FC Connectors	CON1B	0.109617	DTD Connector	Different
4	BC03	BPC	BC to FC Connectors	CON1C	0.109617	DTD Power Enable	Different
Merge1

Awesome thanks I will try this out right now, question what if I want to check for both columns "Function Block" and "component(s)?

 

[alansidman]

You can change the Add Custom Column to compare both fields. Delete any fields you do not require

let
    Source = Table.NestedJoin(Table1, {"Identification Number (ID)"}, Table2, {"Identification Number (ID)"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Function Block", "Component(s)"}, {"Table2.Function Block", "Table2.Component(s)"}),
    SameDiffFunction = Table.AddColumn(#"Expanded Table2", "Function", each if [Function Block] = [Table2.Function Block] then "Same" else "Different"),
    SameDiffComp = Table.AddColumn(SameDiffFunction, "Component", each if [#"Component(s)"] = [#"Table2.Component(s)"] then "Same" else "Different")
in
    SameDiffComp

Book10
	A	B	C	D	E	F	G	H	I
1	Identification Number (ID)	Module	Function Block	Component(s)	Failure Rate	Table2.Function Block	Table2.Component(s)	Function	Component
2	BC01	BPC	BC Connectors	CON1A	0.109617	BPC to FIC Connectors	CON1A	Different	Same
3	BC02	BPC	FC Connectors	CON1B	0.109617	DTD Connector	CON1B	Different	Same
4	BC03	BPC	BC to FC Connectors	CON1C	0.109617	DTD Power Enable	CON1C	Different	Same
Merge1

 

[rorozco]

I would join the two tables in Power Query with the unique identifier in Column A as the field to join on.

Once joined, in PQ add a column and compare Column C of each file to see if they match.

If you post some sample data for the second table, I will demonstrate the Mcode to make this happen.

Hello,

I am having problems brining both tables into Power Query (sorry I am new to PQ). I have file 1 called Export, file 2 called Export 2. Can you tell me exactly how I can bring both tables together?

 

[alansidman]

Highlight the table you wish to bring into PQ. Click on Data-->Get and Transform-->from table/range.