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

[Anonymous]

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: 

Table1:

 Company        Order           Line Number                                     

PBLG               319816              10
PBLG               319816              
PBLG               319816
DNKA              319734             10
DNKA              319734             20
DNKA              319734
DNKA              319734             40
PBLG               331606             20
PBLG               331606             30
PBLG               331606             40 
PBLG               331606             50

Table2:

Company        Order           Line Number

PBLG               319816              10
PBLG               319816              4
PBLG               319816              5
DNKA              319734             10
DNKA              319734             20
DNKA              319734             30
DNKA              319734             40
PBLG               331606             20
PBLG               331606             30
PBLG               331606            
PBLG               331606             

Output: 

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

Thanks,

Sareen

[djurecicK2]

Hi @Anonymous ,

 With EXCEPT, you need to use the table name, not a selectedvalue from a table.

[Anonymous]

Hi@dj,

Tried as you mentiones, but need to compare two columns in in different tables with EXCEPT. 

Can you please eloborate with query for comapring two columns.

Thanks,

Sareen

[v-yinliw-msft]

Hi @Anonymous ,

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] <> BLANK())

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yinliw-msft ,
Thanks for your inputs. UNION  and EXPECT are work, when both the tables having same number of columns. Coming to real scenario, I have two tables which don't have same numner of columns and they are in directly mapped with DIM table with common column. Can you please provide solution on this case like by creating variables. And in the output tables, only single column can see for missing line Number for a sing order with comapny.
Like: 

Company	Order	Result Column
PBLG	319816	Missing Lines : 4, 5

Appriciate if can provide the solution.

Thanks,

Sareen

[djurecicK2]

Hi @Anonymous ,

 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.

[Anonymous]

Hello @djurecicK2 ,
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. 

Note: Two Line Numbers columns from two different tables and tables having indirect mapping. 
Could you pleaes help on it.

Thanks,

Sareen