Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Thanks sharma 😊
Thanks,
SBC
@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
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
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |