Forum Discussion

HockeyFan11's avatar
HockeyFan11
Copper Contributor
Oct 09, 2023

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

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 ...
BI & Data Analysis
excel
HockeyFan11's avatar
HockeyFan11
Copper Contributor
Oct 11, 2023
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's avatar
SergeiBaklan
Diamond Contributor
Oct 12, 2023

HockeyFan11 

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.

Resources