Forum Discussion
merge two table with full outer join values
i have two tables in like m1 ,Acutals
M1 data like
Location Product values
1A04 MKS 51
CK04 PKS 51
BA04 LKS 52
CA04 XKS 53
DA04 RKS 54
Actual value
M1 data like
Location Product values Ct val.
1A04 MKS 51 43
BA04 LKS 52 34
i want output like
Location Product values cat val
1A04 MKS 51 43
BA04 MKS 52 34
CA04 MKS 53 0
DA04 MKS 54 0
CK04 PKS 51 0
i am trying full outer join but i am getting both table matched .null values not visible ? any idea
Hi Anonymous
(1)You can use Append function in Query Editor .Append two tables and return a new table like this :
(2)Delete the duplicate rows with null in Ct val. And then replace the null value with 0 .
(3)Close & Apply to Desktop view , and the result is as shown :
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Anonymous
(1)You can use Append function in Query Editor .Append two tables and return a new table like this :
(2)Delete the duplicate rows with null in Ct val. And then replace the null value with 0 .
(3)Close & Apply to Desktop view , and the result is as shown :
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous Are you doing this in Power Query Editor? Can you share your Advanced Editor code?
Hi Anonymous ,
Hope you are following the below steps. Since after following these steps I am getting expected output.
1. Selecting merge queries as new
2. Merge tables with location column from both the m1 and actual table:-
3. Take CT_value column only from actual table:-
4. Replace null with 0 in ct_value column
final output
Please let me know if i am missing something.
Thanks,
Samarth
I haveing two table i want to mapping table 2 value to table 1 .some values different that values also return . I
TABLE 1 :
LOCATION PRODUCT M1 M2
1A04 DA001145 5 6
1B04 DA001145 15 16
1D04 DA001145 35 76
TABLE 2 :
LOCATION PRODUCT ACT VALUE
1C04 CA001145 5 1
1B04 DA001145 15
1K04 KA001145 45
i want to return output matched rows and unmatched row also like
LOCATION PRODUCT M1 M2 ACT value
1A04 DA001145 5 6 0
1B04 DA001145 15 16 15
1D04 DA001145 35 76 0
1C04 CA001145 0 0 5 1
1K04 KA001145 0 0 45
