how to work with FULLER OUTER Join table

[Anonymous]

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.Code	S1.Market	S1.Order	S1.Date	S2.Code	S2.Market	S2.Constraint	S2.Date
121	abc	10000	7/20/2021	121	abc	2000	7/20/2021
				130	abc	5000	7/28/2021
150	xyz	1000	6/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!

[MrSujay · Accepted solution]

Hi @Anonymous 

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

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

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.

[Anonymous]

Thank you so much. Turns out was pretty simple 😊

Really appreciate your help!