Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SBC
Helper II
Helper II

Compare columns based on two different table

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  qtydate
T90A2010.510/18/2020
T90A2168.5510/3/2020
T90A2168.2610/3/2020
T90A2217.4110/9/2020

table2:

Member   Deal QtyDate
T90A2168.552020-10-03
T90A2168.262020-10-03
T90A2217.412020-10-09

 

Expected output:

 

tbl1.deal tbl2.DealMatching Dealtbl1.member tbl2.Member Matching  Membertbl1.date tbl2.DateMatching Datetbl1.qty tbl2.Qty Matching  Qty
A20 NoT90 No10/18/2020 No10.5 No
A21A21YesT90T90Yes10/3/202010/3/2020Yes68.5568.55Yes
A21A21YesT90T90Yes10/3/202010/3/2020Yes68.2668.26Yes
A22A22YesT90T90Yes10/9/202010/9/2020Yes17.4117.41Yes

 



 

Thanks,

SBC

 

1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

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

AntrikshSharma_1-1667458931120.png

 

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

 

AntrikshSharma_0-1667458730667.png

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

View solution in original post

3 REPLIES 3
SBC
Helper II
Helper II

 

Thanks sharma 😊

 

Thanks,

SBC

AntrikshSharma
Community Champion
Community Champion

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

AntrikshSharma_1-1667458931120.png

 

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

 

AntrikshSharma_0-1667458730667.png

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

Hi @AntrikshSharma ,

 

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

SBC_0-1667459898189.png

"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 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors