Monday, April 11, 2022

Merging ADX queries in Power BI

Merging ADX queries in Power BI

 

Why merge

 

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.

What is the preferred way to join ADX tables?

 

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 secret is merge

 

The steps we have to follow are the following:

  1. Create a query to bring the table SalesFact in the help cluster and uncheck the setting “enable Load”

DanyHoter_0-1649659631795.png

 

 

  1. Create a query for the table Products and also uncheck enable load.
  2. Merge the FactSales query to the Products table based on the ProductKey column.
  3. Choose Left Outer join. This is the default and the only type of join that can be translated (aka as folded) by the connector to KQL.
  4. Click on the icon in the header of the newly created columnSalesFact

DanyHoter_1-1649659631797.png

 

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

  1. Create a query for the Customers table and this time leave the setting enable load checked.

         Rename the query to a name like Sales because this is the table that eventually will be visible in the model.

  1. Merge the Customers table with the Products table which also includes the FactSales

       Again, use Left Outer Join and use the CustomerKey column.

  1. Expand and select columns from the Products table and the fact table into the final table
  2. If you look at the native query , you can see that the tables are joined when the dimension is first which is the efficient way to do in KQL

 

 

 

 

 

Posted at https://sl.advdat.com/37Abnemhttps://sl.advdat.com/37Abnem