Power Query Unmatched Report Showing Null values for all columns when some columns match

Question

Unmatched Report (Left-Anti &amp; 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?&nbsp;&nbsp;&nbsp;

sergeibaklan · Answer

HockeyFan11&nbsp;
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.&nbsp;
Better if you give small sample and what you'd like to receive, i.e. to find mismatch for which columns.

hockeyfan11 · Answer

SergeiBaklan&nbsp;&nbsp;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.&nbsp;

sergeibaklan · Answer

HockeyFan11&nbsp;
Without key columns it's not clear what and how to compare.
Here

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?&nbsp;

hockeyfan11 · Answer

How do I set a key column say I have a unique identifier (Product ID's in this case) how would I set that as the key identifier in power query so it knows that I am looking for matches based on that criteria? I would like to see in Power Query that if there is a partial match (I.E;) the product Id's match and some columns also match but some do not, I would want the columns that match values returned as non-null i.e; the matching value, while the columns that are not a match return as null.

sergeibaklan · Answer

HockeyFan11&nbsp;
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] &gt; 1... and change it on ...each [Count] = 1...&nbsp;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-&gt;Pivot table-&gt; Value as value column and in Aggregation select Don't aggregate.
Select all columns but ID and replace error on null.
Here we are.

Forum Discussion

Power Query Unmatched Report Showing Null values for all columns when some columns match

5 Replies

Resources