January 13, 2023
I have two tables that are the same size in regards to columns and rows; 130 columns by 153 rows. These are from 2 different databases. I have been tasked with finding the differences between the 2 tables. The columns contain a mixture of numbers and text, but the tables match each other in regards to columns being the same. So, if column 50 is text in one table, it will also be text in the other table.
Other than pulling them into power query and doing a true/false statement on each cell. Is there a better way? I am only 6 months into my power query journey, so I really appreciate the help.
January 31, 2022
Hopefully you have unique ID's in both tables so that you can match records that should be the same across all columns. Unpivot both tables and then merge the two based on ID and column (name or number). Add a custom column identifying the rows where the value in table 1 does not equal the value in table 2 and filter on TRUE.
The attached file contains a small scale example, but it should also work with your 130 X 153 tables. Unpivoting will result in tables 19890 row, which isn't going to be a problem for PQ. And remember, you don't have to load these unpivoted tables back to an Excel sheet.
If it doesn't work for you, please come back and attach a file with two tables, say each 10 X10, that truly represent how your real data look like.