Help - Blending two data sets (full outer join)

[Anonymous]

Hello All - 

I need help with power bi. Looking for the best way to move forward. I have a query which references our SSMS database and pulls data from some tables there. I also have an oracle database, but it is referenced as a linked server object (aka query-able from SSMS) and pulls data from some tables there. I want to know when these queries don't match, so I am doing a full outer join with exclusions. I have this query run in SSMS and takes about 45min, and I paste the data into excel as an export. It takes a while because the oracle database is hella slow, but only when queried (Agile PLM is quick), reason unknown, even slow through SQL developer for basic stuff. Unfortunately, when I push the query into Power BI it times out. When I break the query's down into each subquery, I have noticed this type of join is unavailable in power bi. How do I resolve this? Since I'm on the power bi forum, more interested in the power bi application solution perspective on how to do the correct type of join or some other work around. I am interested in when only the databases don't match.

N

[Anonymous · Accepted solution]

Hi @Anonymous 

I build two tables and use Merge and append in Power Query Editor to have a test.

TableA:

TableB:

You can build a new query in Power Query Editor and use this M query:

= let

    Source = Table.NestedJoin(A, {"PRO"}, B, {"PRO"}, "B ", JoinKind.FullOuter),

    #"Expanded B " = Table.ExpandTableColumn(Source, "B ", {"PRO", "Cost", "User"}, {"B.PRO", "B.Cost", "B.User"}),

    #"Filtered Rows" = Table.SelectRows(#"Expanded B ", each [PRO] = null or [B.PRO] = null)

in

    #"Filtered Rows"

Or you can achieve your goal step by step:

1.     Merge TableA and TableB(Full Outer)

2.     Expand B in merge table:

3.     Filter Merge table by each [PRO] = null or [B.PRO] = null

= Table.SelectRows(#"Expanded B", each [PRO] = null or [B.PRO] = null)

Result:

You can download the pbix file from this link: Help - Blending two data sets (full outer join)

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hello @Anonymous -

Thank you for the detailed walkthrough. It seems promising. One unfortunate snag, upon applying the filter step, Power BI is now presenting an error. I assume the is implying the filter on A cannot reference B, or vice versa. I'm stuck here, unable to confirm this works/doesn't work.

N

[Anonymous]

I ended up solving this by re-writing the query to all go through ssms and the oracle segment was written with dynamic sql inside openquery. This then pushes the outer join from power bi into sql. I'll mark the previous answer as solution because seems reasonable though cannot confirm it works 100%.

N

[amitchandak]

@Anonymous ,Not sure I got it

In M refer :

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

In Dax you have crossjoin

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[Anonymous]

Hi @amitchandak -

No, both of these are not applicable. The issue is through the merge operation, the types of joins are limited and what is needed is not a selectable choice.

A cross join is definitely not the way to go. 

N