cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SBC
Helper I
Helper I

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 I
Helper I

 

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors