A while back a give a session at the element61 Microsoft Business Analytics Day, a free event where the capabilities of  the Microsoft BI platform is demonstrated alongside client testimonials. I gave a session called "Drilling across Analysis Services cubes using Power Query", which talked about how you can use Power Query to do a drill across analysis over multiple SSAS cubes. Drilling across combines facts from multiple fact tables through their conformed dimensions, something that is not possible if your data is split up against multiple cubes. I even wrote an article about it: Drilling across Analysis Services cubes using Power Query.

Basically the solution is to create a query for each fact table and then combine them using the Merge transformation in Power Query, which basically is a Left or Inner Join. At the end of the session, I got the question if it's possible to do a Full Outer Join instead, to which my initial response was "Ehrmmm...". It's not possible through the user interface, so I gave the advice to create a query that contains the cross join of the dimensions you want to use, then left join all the fact tables against that query and at the end filter out any rows where all the facts are empty. This advice still stands for people who do not want to write a single line of code in Power Query. However, when reading Chris Webbs excellent book about Power Query and more specifically the chapter about M, I realized there is a more elegant solution out there.

In this blog post, I'll use the following data as a source:

MergeSource

You start out just as usual, by creating two queries on top of the these tables and then merging them together in the user interface.

MergeGUI

After clicking OK, you get the following result:

MergeResult

The import part here is the M formula in the formula bar. There is actually room for a final, optional parameter: JoinKind. If we simply add JoinKind.FullOuter, we get our desired result.

MergeResult_FullOuter

Of course, we still have to expand the NewColumn column to retrieve the Category and the Value from the right input.

 MergeResult_Expand

And finally we have to do some cosmetics to get a decent presentable result:

MergeFinalResult

I added the following transformations:

  • Renaming columns
  • Adding a custom column taking the left category, unless it is null, then the right category is taken
  • Removing the old category columns
  • Moving columns around

Conclusion

Doing a Full Outer Join in Power Query is really straight forward and you don't have to be an M guru to adapt the code.