Forum Discussion
Power Query Unmatched Report Showing Null values for all columns when some columns match
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.
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.
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?
- 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.
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.
