Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
THENNA_41
Post Partisan
Post Partisan

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 

 

 

1 ACCEPTED SOLUTION
v-yetao1-msft
Community Support
Community Support

Hi @THENNA_41 

(1)You can use Append function in Query Editor .Append two tables and return a new table like this :

Ailsamsft_0-1627535715373.pngAilsamsft_1-1627535715375.png

(2)Delete the duplicate rows with null in Ct val. And then replace the null value with 0 .

Ailsamsft_2-1627535715376.pngAilsamsft_3-1627535715377.png

(3)Close & Apply to Desktop view ,  and the result is as shown :

Ailsamsft_4-1627535715378.png

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.

View solution in original post

7 REPLIES 7
v-yetao1-msft
Community Support
Community Support

Hi @THENNA_41 

(1)You can use Append function in Query Editor .Append two tables and return a new table like this :

Ailsamsft_0-1627535715373.pngAilsamsft_1-1627535715375.png

(2)Delete the duplicate rows with null in Ct val. And then replace the null value with 0 .

Ailsamsft_2-1627535715376.pngAilsamsft_3-1627535715377.png

(3)Close & Apply to Desktop view ,  and the result is as shown :

Ailsamsft_4-1627535715378.png

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
Post Partisan
Post Partisan

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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

i am trying merge table option in power bi desktop 

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

 

 

Samarth_18_0-1627446590032.png

2. Merge tables with location column from both the m1 and actual table:-

Samarth_18_1-1627446673336.png

3. Take CT_value column only from actual table:-

Samarth_18_3-1627446803644.png

4. Replace null with 0 in ct_value column

Samarth_18_4-1627447045230.pngSamarth_18_5-1627447084770.png

final output

Samarth_18_6-1627447113882.png

 

 

Please let me know if i am missing something.

 

Thanks,

Samarth

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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 

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.

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors