We're performing maintenance on our phone system today. If you are unable to reach us by phone, you can email us instead. For sales queries email sales@red-gate.com, you can visit our support center for help with our products, or for anything else email info@red-gate.com.

Merging tables in Power BI dataflows with PRO subscription

Power BI dataflows is a powerful self-service ETL tool. However, some features demand the use of Power BI Premium, way more expensive than the PRO version and sometimes inaccessible.

One of these tasks, for example, is the merge of two tables. Even in the same dataflow, if you have two tables and make the merge, the resulting table will be a calculated table and this requires Power BI Premium for the refresh.

requires premium

We could argue the data should be better prepared in Data Warehouse, but this is not always true. The merge of two tables is a so simple task that even having a good DW as a source you may need it.

Let’s say we have two tables, Customers and Geography. Customers table has a GeographyId field which identifies the location of the client.

initial state

In order to make the merge between Customers and Geography we need to follow these steps:

  • Edit the query for the DimCustomer table
  • Click on the ‘…’ button, “Combine Tables” menu option, “Merge Queries”

merge queries

  • In the Merge window that will be opened, select the 2nd table as DimGeography
  • Again in the Merge window, select GeographyId field in the DimCustomer table
  • One more time, in the Merge window, select GeographyId field in the DimGeography table

Merge Window

 

  • After the Merge window updates itself, click Ok button
  • In the DimCustomer table, find the DimGeography field and click the expand button

expand table

  • Select the fields you would like to include in the DimCustomers table and click the Ok button

Selecting Fields

  • Close and save the query

Making the merge between Customers and Geography will turn Power BI Premium into a requirement to refresh the dataflow.

In order to work around this problem, there is a simple solution: we can edit the query again, right-click the table Geography and disable the load option.

load table

When this option is disabled, the merge is not considered as a calculated table, allowing the refresh with a Power BI Pro account, no need for Power BI Premium. Another result is that we will not see the Geography table available in the dataflow anymore, only the Customers table.

single table