Oct 09 2023 11:52 AM
Oct 09 2023 11:52 AM
Unmatched Report (Left-Anti & Right Anti Appended into one) in Power Query returns Null Value for all columns if one column doesn't match but the others do. Is there a way to return a null value for the column that does not match but to fill in the columns that do match. (In the example below only column 3 is truly unmatched with AA being different from AC.) However, columns 1 and 2 do match, is there a way to have those values displayed instead of shown as null?
Oct 09 2023 12:39 PM
With such technique we remove column with resulting table for Left Anti, in opposite keep only it and expand for Right Anti, only after that append queries.
In your case it's not clear as well on which columns tables are joined.
Better if you give small sample and what you'd like to receive, i.e. to find mismatch for which columns.
Oct 09 2023 04:26 PM
I am trying to find mismatches for all columns shown there, so all columns have been selected. Is there a better way to do this in power query with that objective in mind? I would like to see non-null values if there is a match in one column but not the other.
Oct 10 2023 12:50 PM
Without key columns it's not clear what and how to compare.
we have two identical tables but sorted differently. Will we compare row by row or for each row in one table will try to find similar rows in another one? If the latest based on what:
will we compare first row of first table with first or second row of the second table or with both?
Oct 11 2023 07:32 AM
Oct 12 2023 04:53 AM
Let try on such sample
Both tables have ID, but not necessary all of them are in both tables.
For each table we select ID column and Unpivot other columns.
Append resulting queries.
Reference append query, here select ID and Attribute, Keep Duplicates. in formula bar find within generated formula ...each [Count] > 1... and change it on ...each [Count] = 1... Thus we keep only not repeating ID for both tables. Here remove Value columns and add custom column with the same Value name and null as a value.
Reference append query again. Select ID and Attribute, Keep Duplicates, select all columns, Remove Duplicates. With that we keep records with the same ID exists in both tables.
Append above two queries to new one, select Attribute column->Pivot table-> Value as value column and in Aggregation select Don't aggregate.
Select all columns but ID and replace error on null.
Here we are.