How to speed up "Apply Query Changes" after updating Power Query.

[Anonymous]

When I make changes to Power Query and then apply changes PowerBI thinks forever. What are some things I can do to figure out what is causing my refresh to take so long?

[Terrance]

It turned out that the folder where the input CSV was being held was extraordinarily slow. When we moved it to test alternatives, the speed problem went away.

I am not sure that you can avoid multiple rereads of an input Excel file in Power BI.

[StephenK]

What kind of datasource(s) are you using and how many rows?

Are you doing any big transformations like merging queries?

[Anonymous]

My data sources are Excel and SAP Business Warehouse. I'm not sure on the # of rows (how do I figure that out?)

Regarding merging...yes I have a ton of merging queries. 

[StephenK]

Row count in power query you have to use the count rows function under the Transform tab.

Easier way is to go into table view and PBI will give you the row count in the bottom left corner.

Have you removed all unecessary columns from your model and filtered the data down to just the measurement period you will use in your analysis?

[Anonymous]

Across 8 tables my largest table is 90K rows. Most of the other tables are around 20K rows.

Yes, I only have needed columns and time period in question.  

[StephenK]

Your merge queries are definitely going to slow things way down. Don't know if there is a way around that.

Depending on the granularity you need, you could try aggregating using Group By in PQ to reduce your size more.

I'm assuming your model is also properly optimized by breaking into fact and dimension tables?

[Anonymous]

Please tel lme more about Group by in PQ and using a fact and dimension tables? I'm not familiar with these. 

[StephenK]

See here for guidance on fact and dimension tables:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Group By is on the home tab of PQ.

You can reduce the size of your data model by reducing your granularity. Say you have transactions by day, but you are only analyzing by year or month. You can select your year or month column and aggregate your data by this column. You lose the granularity of drilling down to the day level, but you reduce your row count and potentially improve performance.