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.

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.

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”

• 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

 

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

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

• 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.

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.

The post Merging tables in Power BI dataflows with PRO subscription appeared first on Simple Talk.

from Simple Talk https://ift.tt/30Lhwv8
via