We will show you how to check to see if a value exists in another column in Power Query.
If you want to check if a value exists in another column in the same table, there are two methods we have to share.
If you want to find values in columns in two different tables, we also have a method you can use to check if they match.
Check if value exists in another column in Power Query
Sometimes you want to find out if a value exists in another column in the same table in Power Query.
There are a couple of different methods you can use to do this. We will show you how to merge queries or use a custom column.
Here’s an example table we will be using:
One value exists in both columns of the table (C4), which is what we want to identify.
Method 1: Merge queries
The first method of finding out whether a value exists in another column in your table is to use the merge queries function.
There is a’ Merge Queries’ button in the home ribbon at the top of the screen in Power Query. Click on this.
Once the Merge pop-up appears, since we are checking two different columns in the same table, select the current table and both columns you want to compare.
Leave the join type as left outer.
Once you have done this, a new column will be added to your table.
Click the button to the right of the column name to expand the column. Choose one column to expand (either) and click ‘ok’.
The new column added to your report will return values that match in both columns and return nulls for any values that do not match.
See the results of the example below.
Method 2: Use an IF Statement
Another method to find out whether a value exists in another column in the same table is to use a custom column.
Navigate to the ‘Add column’ tab on the top ribbon and click ‘Add Custom Column’.
You can then use an IF statement to return values that match in specified columns.
Here is the custom column formula we used for the example:
= if [Sample 2] = [Sample 1] then [Sample 2] else nullYou can also use the List.Contains function too to compare two columns and return a matching value.
= if List.Contains(Source[Sample 1], [Sample 2]) then [Sample 2] else null)This is the result after adding a custom column with either of the formulas.
Check if value exists in another table in Power Query
Sometimes you want to find out if a certain value exists in another column in a different table in Power Query.
Here are two methods you can use to check if values exist in different tables:
Method 1: Merge queries
The first method is by merging queries.
Simply follow the instructions above on merging queries, except when asked to select the tables and columns to merge, select the different table and the column within.
This will allow you to essentially join the two tables and compare matching values in both columns.
You can also consider using the LOOKUPVALUE function in DAX if you do not want to merge queries in Power Query.
Method 2: Use List.Contains
Another method of checking to see if a value exists in another column is to create a custom column in Power Query and use the List.Contains function.
In our example, we want to check to see if any values from Sample 1 column in Table 4 match any values in Sample 2 column in Table 5.
There is one shared value, C4.
Here is the List.Contains formula we would add to Table 5:
=List.Contains(#"Table (4)"[Sample 1],[Sample 2])This returns the following column which shows that the value C4 is present in both tables:
