cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FAA
Helper I
Helper I

how to work with FULLER OUTER Join table

Hi All,

 

I have four data sources. 

2 are dimension tables for the SKU codes and for the Market hierarchy.

 

The other two are:

One source contains the order data (S1). The other contains (S2) constraints data.

I have connected S1 and S2 via full outer join and getting something like this:

 

S1.CodeS1.MarketS1.OrderS1.DateS2.CodeS2.MarketS2.ConstraintS2.Date
121abc100007/20/2021121abc20007/20/2021
    130abc50007/28/2021
150xyz10006/20/2021    

 

Now the issue is that I m unable to connect it to dimension table because of some blank rows. If I connect it via S1.code then I miss some data in Source 2 (eg for code 130). 

In excel I coul add a column which says if S1.code or S2.code is blank, we should take the not blank value. But how do I do this in PowerBI so that I can create a relationship and all data is captured?


Would highly appreciate any help on this!

1 ACCEPTED SOLUTION
MrSujay
Resolver II
Resolver II

Hi @FAA 

You can add a custom column and add below DAX formula.

 

SCode = IF(AR[S1.Code] = BLANK() ,AR[S2.code],AR[S1.Code])

 

 

MrSujay_0-1627458266094.png


Now Just create relation ship with dimension table using column Scode = S1.code and Scode = S2.Code.

to know more about how to create a relationship follow this document.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Thanks,

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

2 REPLIES 2
MrSujay
Resolver II
Resolver II

Hi @FAA 

You can add a custom column and add below DAX formula.

 

SCode = IF(AR[S1.Code] = BLANK() ,AR[S2.code],AR[S1.Code])

 

 

MrSujay_0-1627458266094.png


Now Just create relation ship with dimension table using column Scode = S1.code and Scode = S2.Code.

to know more about how to create a relationship follow this document.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Thanks,

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

Thank you so much. Turns out was pretty simple 😊

Really appreciate your help!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.