Forum Discussion
How to reduce calculation time on a query?
If it were me, I would do testing to pinpoint exactly what steps are adding time to the refresh. Like, how long is the refresh when you just refresh/load Query1? What is the timing of doing Query2 but just the initial load and expand? As you add in each new step, you can gauge the overhead that it's adding and perhaps figure out exactly where you are getting the big performance hits.
When I'm tuning queries in Excel, once I identify certain Power Query transformations that are too expensive, if there is not a more efficient way to write it in M, I'll move the transformation from Power Query to a DAX calculated column in the Power Pivot model. As in, rather than load directly from Power Query -> Sheet, you load Power Query -> Power Pivot model -> sheet. See here about loading tables from Power Pivot model, the article refers to these as "reverse linked tables": https://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/
Another thing to consider: avoid merging your project and invoice tables but instead load them into Power Pivot and add a relationship. Make sure that you have a primary key in your project table (probably, project ID - ensure it's unique in project table). Then you can easily* pull over related columns as needed either in the model or in the DAX that you use to define your tables as described in previous paragraph.
* or rather, as easy as you are familiar with DAX. If you are new to DAX it's probably a whole other can of worms to get into, but it's necessary in the long term if you are going to use Power BI in desktop or Excel.
Thanks MarkLaf. I went back and worked on the relationships again and I found your second last paragraph very useful. The calculation used to take 10 mins and by working on the relationships and removing all merging in between invoice and project tables, it now takes around 1m30s 🙂
Thank you!
Hi thank you for your suggestions. Unfortunately this post is mainly for Power query in excel. So dataflows and parallel load won't work here. I tried table.buffer but there was no improvement. Lastly, I have to do the query like this to fetch the API because this is how it is structured at the supplier's end. You have to bring in all projects id with Function/projects and then do an API call for each project ID like this Function/projects/projectid/invoicingdata where project id starts with 1. Any other suggestions are most welcomed.
If it were me, I would do testing to pinpoint exactly what steps are adding time to the refresh. Like, how long is the refresh when you just refresh/load Query1? What is the timing of doing Query2 but just the initial load and expand? As you add in each new step, you can gauge the overhead that it's adding and perhaps figure out exactly where you are getting the big performance hits.
When I'm tuning queries in Excel, once I identify certain Power Query transformations that are too expensive, if there is not a more efficient way to write it in M, I'll move the transformation from Power Query to a DAX calculated column in the Power Pivot model. As in, rather than load directly from Power Query -> Sheet, you load Power Query -> Power Pivot model -> sheet. See here about loading tables from Power Pivot model, the article refers to these as "reverse linked tables": https://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/
Another thing to consider: avoid merging your project and invoice tables but instead load them into Power Pivot and add a relationship. Make sure that you have a primary key in your project table (probably, project ID - ensure it's unique in project table). Then you can easily* pull over related columns as needed either in the model or in the DAX that you use to define your tables as described in previous paragraph.
* or rather, as easy as you are familiar with DAX. If you are new to DAX it's probably a whole other can of worms to get into, but it's necessary in the long term if you are going to use Power BI in desktop or Excel.
Thanks MarkLaf. I went back and worked on the relationships again and I found your second last paragraph very useful. The calculation used to take 10 mins and by working on the relationships and removing all merging in between invoice and project tables, it now takes around 1m30s 🙂
Thank you!