Comparing Two Columns from different tables and list out un-matched records.

Question

Dear experts,Please help me by providing the solution to the query: by comparing two columns of the same kind and listing out the unmatched records. The logic should read "common company" and "common order" from both tables, compare line numbers from both tables, and return unmatched line numbers as shown in the output table.For Example:&nbsp;Table1:&nbsp;Company&nbsp; &nbsp; &nbsp; &nbsp; Order&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Line Number&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;319816&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;319816&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;319816DNKA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;319734&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10DNKA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;319734&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20DNKA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;319734DNKA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;319734&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;331606&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;331606&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;331606&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40&nbsp;PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;331606&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50&nbsp;Table2:Company&nbsp; &nbsp; &nbsp; &nbsp; Order&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Line NumberPBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;319816&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;319816&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;319816&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5DNKA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;319734&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10DNKA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;319734&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20DNKA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;319734&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30DNKA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;319734&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;331606&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;331606&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;331606&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;PBLG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;331606&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;Output:&nbsp;ComapnyOrderResul ColumnPBLG319816Missing Lines: 4,5DNKA319734Missing Lines: 30PBLG331606Missing Lines: 40,50Thanks,Sareen

djurecic · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;You can get most of the way there using the EXCEPT function:https://powerbidocs.com/2020/11/10/except-dax-function-in-power-bi/#:~:text=EXCEPT%20DAX%20function%20is%20used,Table%20Manipulation%20DAX%20Functions%20category.&nbsp;

anonymous · Answer

Hello&nbsp;djurecic&nbsp;,Thanks for your inputs on my query. I tried to using EXCEPT Dax for comapring two Line Numbes from diffrent tables by creating Variables. But Power BI thowns this error.&nbsp;Note: Two Line Numbers columns from two different tables and tables having indirect mapping.&nbsp;Could you pleaes help on it.Thanks,Sareen

djurecic · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;With EXCEPT, you need to use the table name, not a selectedvalue from a table.

anonymous · Answer

Hi@dj,Tried as you mentiones, but need to compare two columns in in different tables with EXCEPT.&nbsp;&nbsp;Can you please eloborate with query for comapring two columns.Thanks,Sareen

v-yinliw-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
You can try this method:
New a table:
Table =
UNION ( EXCEPT ( 'Table2', 'Table1' ), EXCEPT ( 'Table1', 'Table2' ) )

The result is:
Result = FILTER('Table','Table'[Line Number] &lt;&gt; BLANK())
&nbsp;

Hope this helps you.
Here is my PBIX file.
&nbsp;
Best Regards,
Community Support Team _Yinliw
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;

Forum Discussion

Comparing Two Columns from different tables and list out un-matched records.

6 Replies

Related content

How to Count from two different table Un-matched record with New table

How to apply Slicer filter on un-matching record between two different table.

Compare different tables

Comparing values between different tables

Compare two columns in different tables

Comapny	Order	Resul Column
PBLG	319816	Missing Lines: 4,5
DNKA	319734	Missing Lines: 30
PBLG	331606	Missing Lines: 40,50