merge two table with full outer join values

[THENNA_41]

 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 

[v-yetao1-msft · Accepted solution]

Hi @THENNA_41 

(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.

[THENNA_41]

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       

[Greg_Deckler]

@THENNA_41 Are you doing this in Power Query Editor? Can you share your Advanced Editor code?

[THENNA_41]

i am trying merge table option in power bi desktop 

[Samarth_18]

Hi @THENNA_41 ,

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

[THENNA_41]

i have unmatached rows between i want get that rows i have updated my question . i have done merge query but i want unmatched rows also 

[Samarth_18]

Hi @THENNA_41 ,

Something surely we are missing since if you see my comment we are getting those data as well which is not matching for example in location column values CA04 and DA04 are present in merged table which is not matching in both tables.

Could you please share your steps what you followed and also share your output what you are getting so it would help us to understand where we are going wrong.