Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
46 | |
28 | |
25 | |
18 | |
17 |