Forum Discussion

SBC's avatar
SBC
Helper III
3 years ago
Solved

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

 

DAX Averages
DAX Commands and Tips
DAX Power BI
Power BI Help
Power Query
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    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

3 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    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's avatar
      SBC
      Helper III
      3 years ago

      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 

       

  • SBC's avatar
    SBC
    Helper III
    3 years ago

     

    Thanks sharma ğŸ˜Š

     

    Thanks,

    SBC