Merging ADX queries in Power BI
The merge operation (Table.NestedJoin) is the M language equivalent to creating relationships between tables in the model.
The resulting ADX operation is join.
You can join ADX tables by writing KQL, by using relationships or by merging queries in Power Query.
In this article I’ll show how to use merge in a way that produces efficient KQL queries without the need to write any KQL syntax.
Assuming we have a star schema model with one fact table - SalesFact and two dimension tables that need to be joined to it – Customers and Products.
The correct way to join these three tables is:
Products join
( Customers join FactSales on CustomerKey)
On ProductKey
How can we convince Power BI to issue this kind of query without writing it specifically?
The steps we have to follow are the following:
Choose the option to expand and not to Aggregate.
Select the columns you need from the fact, don’t forget the CustomerKey column.
This merge + expand combination will bring columns from the Fact table to the Product table and increase the number of rows to the number of rows in Fact that have a matching ProductKey n the Products table.
Warning: Any rows in the fact table that do not meet this condition will not appear in the resulting merged table
Rename the query to a name like Sales because this is the table that eventually will be visible in the model.
Again, use Left Outer Join and use the CustomerKey column.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.