Compare columns based on two different table

[SBC]

Hi,

How to compare columns in two different table ,I have tried with  full outer join but not working as expected.

Please check sample data   provided below input of two tables and output expected.

Example:

table1:

member	deal	qty	date
T90	A20	10.5	10/18/2020
T90	A21	68.55	10/3/2020
T90	A21	68.26	10/3/2020
T90	A22	17.41	10/9/2020

table2:

Member	Deal	Qty	Date
T90	A21	68.55	2020-10-03
T90	A21	68.26	2020-10-03
T90	A22	17.41	2020-10-09

Expected output:

tbl1.deal	tbl2.Deal	Matching Deal	tbl1.member	tbl2.Member	Matching  Member	tbl1.date	tbl2.Date	Matching Date	tbl1.qty	tbl2.Qty	Matching  Qty
A20		No	T90		No	10/18/2020		No	10.5		No
A21	A21	Yes	T90	T90	Yes	10/3/2020	10/3/2020	Yes	68.55	68.55	Yes
A21	A21	Yes	T90	T90	Yes	10/3/2020	10/3/2020	Yes	68.26	68.26	Yes
A22	A22	Yes	T90	T90	Yes	10/9/2020	10/9/2020	Yes	17.41	17.41	Yes

Thanks,

SBC

[SBC]

Thanks sharma 😊

Thanks,

SBC

[AntrikshSharma · Accepted solution]

@SBC Do this in PowerQuery and use 2 columns as a key when merging queries.

let
    Source = Table.NestedJoin (
        T1,
        { "member  ", "deal  " },
        T2,
        { "member   ", "Deal " },
        "T2",
        JoinKind.FullOuter
    ),
    ExpandedT2 = Table.ExpandTableColumn (
        Source,
        "T2",
        { "member   ", "Deal ", "Qty", "Date" },
        { "member   ", "Deal ", "Qty.1", "Date.1" }
    )
in
    ExpandedT2

If you want a full outer join with DAX you can read this and modify yourself: https://www.antmanbi.com/post/full-outer-join-in-dax-in-power-bi-and-ssas-tabular

[SBC]

Hi @AntrikshSharma ,

Thanks for replying the query,I have already tried that full outer join faced issue in matching  the data.

"10/03/2022 " "qty" is existed in both table same data but when we apply conditional  it is displaying "NO" insted of showing "Yes"

If it works for you please share PBIX file will be helpful.

Thanks,

SBC