Have you created a Power BI model for your Dynamics 365 data and surprised by the long refresh times.

 Most Power BI experts suggest to import your tables and create Merge Queries to add related columns to your tables. This allows the queries to be performed at the data source prior to importing into Power BI.

Prior to adding new columns through the use of Merged Queries you will find refresh rates for data with < 100,000 records to usually only take a few minutes.

Add some Merge Queries and all of a sudden it seems that the refresh rates increase by factors of 10x or more. I had a model with 6 tables from Dynamics that had a refresh of 2 minutes. After adding 11 Merged Queries this refresh rate went up to 35 minutes. This was unacceptable for clients looking to view updated data by refreshing manually. Below shows a merge column when created from the query editor.

The same table already contains the parent account GUID as shown below.

The column below with the red X is the result of a Merged Query and the Account Name column is the result of a Calculated Column:

Account Name = LOOKUPVALUE(accounts[name],accounts[accountid],contacts[_parentcustomerid_value])

The trick here is to get the columns from Dynamics using the value field / column “_parentcustomerid_value” instead of the lookup “parentcustomerid” column that is used for the merge queries.

So next time you are using Power BI bring in the related tables that contain the lookup data which are usually quite small and create your lookup columns from the data tables. Make sure that you only import tables and columns that are absolutely necessary.