Merging Queries is making my BI unusable

[alicia_pbi]

I have a rather large, complex Power BI that just spins and spins in the Query Editor.  I am at the stage where I need to update or add steps and the query just spins.  If I let it run it eventually (after 4+ hours) will load what I need but I am on a deadline and do not have time to wait 4+ hours per step.

* I am not pulling data from SQL so I am not able to minimize data through SQL query

* I do not have direct access to the cube so I am importing data and am not able to minimize the data load

* I have deselected Enable Load for queries I do not need in the desktop version

* I am removing duplicates & changing cases to ensure that there is not an issue

* I am updating my joins to be INNER instead of LEFTOUTER

I started having issues when I had to start merging queries with different sets of data.  I do realize that I can use the relationship functinality but that will require the build of many measures and I believe it would be much simpler to just use the merge.  Is there any way that I am able to use 5+ merges and have my query actually load?

Note: I am not able to share the query for review due to business information within the query.  

Thanks!

[trebgatte]

Try using Grouping instead of remove duplicates, as remove duplicates has to load everything into memory first. I've found grouping to be much faster when working with large data sets. 

Also, you may not have your filters in the right place for folding, resulting in everything being downloaded first. Check out https://docs.microsoft.com/en-us/power-bi/guidance/power-query-folding for more information.

Hope this helps.

--Treb, Power BI MVP

Blog:  https://marqueeinsights.com/category/power-bi/

[alicia_pbi]

@trebgatteThanks!

When I read the folding article it references finding the Native Query option being enabled.  In one of my queries, it is not enabled even at the Source step (step 1).  My source is Analysis Services to a cube that I have access to Import data but I am not able to see the cube so I am not able to write an MDX expression.

With this in mind, as I understand your feedback there is an inefficient order of operations in my steps which is causing my query to run so long.  Do you know if there is a listing of the efficient order of operations (i.e. filter before grouping)?

As a side note, is it possible that using the reference v. rewriting a query could cause additional refresh time?  If one is more efficient I could likely update my queries to see if that helps also.

Thanks!

[trebgatte]

@alicia_pbi  The connector should automatically fold filtering and grouping back to the data source without requiring you to write an MDX statement.

The goal is to reduce I/O as much as possible before sending to PBI. So, if I want monthly sums for the current year, the first transform I'd make after connecting would be filter for the current year, then do a grouping afterward. These should fold back to the data source. Do all other transforms after you do what is necessary to only retrieve the data set you need.

If you use Reference, which I do quite a bit, I believe it runs the entire set of statements again. Reference is also useful when you have related tables that you need to keep in sync. If you refresh one, it'll refresh all related tables as well.

Hope this helps!

--Treb, Power BI MVP

Blog: https://marqueeinsights.com/category/power-bi/