Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help - Blending two data sets (full outer join)

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 datab...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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