Forum Discussion
Compare 2 Columns and state if values match
Dear,
I have read multiple threads here on this website, but I cannot manage to apply any of the solutions posted for my problem. I need to compare two colums in different tables in PowerQuery, whether there is a match or not a match this should be posted in a new colum at the most right side of the 1st table that is compared (main data table). Both colums are numbers in regard of value and a normal and direct relation is established between the tables (these colums form this relation actually).
Sad Enough I cant share the data due to NDA applicability.
the more information I can provide: the first table is the 'main data' table. The second is the 'exception' table. When there is a match, the value in the new column in the main data table has a 'negative impact' vice versa when there is no match.
Eventually the purpose of this calculation will be that there are 2 comparisons made with the main data (maindata vs table1 = output '+' or '-', maindata vs table2 = output '+' or '-'). The outcomes of these both, create a valid or non valid outcome when compared (+&+ = good, anything with - is bad). Knowing this, the validation of the latter will be used as a variable in certain analysises.
However, I have been researching for a several days now. But I can not seem to be able to do this.
Please help!
Hi Anonymous
You can mock some dummy data to help describe the problem and show the expected output. Otherwise it is still difficult for us to understand what you want to achieve.
If I understand it correctly, you want to know if every value in a column of the "main table" exists in a column of the second table, right? If so, you can try the Merge queries feature. Merge queries overview - Power Query | Microsoft Learn
For example, I have two tables like below, I want to have a new column to compare the values in Table A.
In Table A, select "Merge Queries" feature. In the Merge window, select the column in both tables, and select Left Outer join kind.
Then you will have a "Table B" column in Table A. If a table value has rows, it means there is at least one match. If it is an empty table, it means there is no match.
Click Expand icon on "Table B" column header, switch to Aggregate and click the drop-down arrow to select Count (Not Blank) for this column.
Then add a conditional column to have the output you want based on the value in "Count (Not Blank) of Column1" column.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
Hi Anonymous
You can mock some dummy data to help describe the problem and show the expected output. Otherwise it is still difficult for us to understand what you want to achieve.
If I understand it correctly, you want to know if every value in a column of the "main table" exists in a column of the second table, right? If so, you can try the Merge queries feature. Merge queries overview - Power Query | Microsoft Learn
For example, I have two tables like below, I want to have a new column to compare the values in Table A.
In Table A, select "Merge Queries" feature. In the Merge window, select the column in both tables, and select Left Outer join kind.
Then you will have a "Table B" column in Table A. If a table value has rows, it means there is at least one match. If it is an empty table, it means there is no match.
Click Expand icon on "Table B" column header, switch to Aggregate and click the drop-down arrow to select Count (Not Blank) for this column.
Then add a conditional column to have the output you want based on the value in "Count (Not Blank) of Column1" column.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.