Forum Discussion
PowerBi Time Out error
I'm merging couple of objects in Salesforce, around half a million records. Worked fine until last merging but when I merged a csv with it & then expanded columns, it took lot of time & ultimately timed out. Kindly help.
8 Replies
Hi. I'm sorry to say that power query merge operations have horrible performance :S
You may want to try some magic from Chris Webb blog. For example:Hope that helps,
As per Chris's solution one Join columns should be already sorted. Not true in my case. Moreover its timing out during Expand Columns step.
Hi Addy2020 ,
Please check if the following posts are helpful to you:
https://community.powerbi.com/t5/Desktop/How-to-set-command-timeout-option/td-p/487941
https://community.powerbi.com/t5/Desktop/Query-Timeout-on-update/td-p/53459
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Thanks for your response. But its timing out after 5 hours, it should not be taking that much time. Its half a miillion Salesforce record I'm merging with 17k csv file. Any tips to reduce that time?
Hi Addy2020 ,
I listed some optimization operations, please check the reference link for specific information.
Data reduction techniques for Import modeling:
Remove unnecessary columns
Remove unnecessary rows
Group by and summarize
Optimize column data types
Preference for custom columns
Disable Power Query query load
Disable auto date/time
Switch to Mixed modeA DirectQuery model can be optimized in many ways, as described in the following bulleted list.
Hide the one-side column of relationships
Avoid relationships on calculated columns
Keep measures simple
Do not use Power Query relative date filtering
Examine the use of calculated columns and data type changes
Avoid complex Power Query queries
Avoid relationships on "Unique Identifier" columns
Set relationships to enforce integrity
Avoid use of bi-directional relationship filtering
Limit parallel queriesReference: Optimization guide for Power BI
Data reduction techniques for Import modeling
Optimize data source performance
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Addy2020 ,
Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem, such as screenshots.Best Regards,
Stephen TaoHi Anonymous ,
Not yet. What you provided is a lot of suggestions. Will take me time to try each of them in an already slow PowerBi Salesforce connection with more than half a million records.
Hi Addy2020 I know this reply is very late, but were you able to find a solution? I've used the Salesforce connector but I had some issues with my data. As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:
After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:
There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.
