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!
- Use Table.Buffer when referring to the first query from the second ( e.g. Source = Table.Buffer( Query1 ) )
- Turning off parallel load may help. See: https://blog.crossjoin.co.uk/2019/03/26/power-bi-caching-parallelism-and-power-query-refresh-performance/
- For your step 2 where you query invoicing off of project IDs, from my understanding you are making an API call for each project. You should look into ways to query in bulk to decrease the number of API calls. E.g.
- get the start/end of all projects and do one invoice api call for all projects in the min start and max end
- get all invoice lines with one call, then merge on projects. Even though you are returning more rows, the 1 v thousands of calls to api would probably speed things up
- even something a bit ham-handed where you do a call with a filter like, id eq project1 or id eq project2 or id eq project3.... for however many you can fit into filter argument before hitting character limits that usually exist with GET
- Or, similar to above, put the full filter into the body of Web.Contents and convert to POST where you won't have character limits
- Use dataflows
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!