Forum Discussion
Adding columns with group by slows refresh
Hello,
When I do not group my data, I get the following error message when trying to refresh in service: "The amount of data on the gateway client has exceeded the limit for a single table. Please consider reducing the use of highly repetitive strings values through normalized keys, removing unused columns, or upgrading to Power BI Premium."
So I grouped my data, but with each new column I add in the grouping, my data refresh significantly slows down in service and I get a time out error "Before the data import for finished, its data source timed out. Double-check whether that data source can process import queries, and if it can, try again."
Any ideas of solutions/workarounds or is my only option to upgrade to Premium?
Thank you!
What format are the files?
It they are XLSX, do you have the option to convert them to CSV? CSV performs much better.
Do you have the option to put them somewhere online so the gateway isn't needed? SharePoint or similar? (no guarantees this will help but it removes a potential bottleneck.)
It is also possible to setup incremental refresh for files to ensure later refreshes are faster.
7 Replies
Hello, thank you for your response!
Locally on my desktop there are 10.3 million rows which refresh nicerly.
The issue I am dealing with is when I publish the report to PowerBI service and try to refresh the data via a gateway set up that runs on a remote server, I get an error. If I do not group my data, the error message reads "There is too much data on the gateway" So I grouped my data to fix this as seen in the screenshot below, but noticed each column I add in the bottom section of the groupby adds 30 minutes to my refresh time in service. (I added one column at a time and noted how long it took to refresh the data) Adding a 4th column took over two hours which is the limit for pro users which is the license I am using, so I get a timeout error.
I did not specifically remove all unneeded columns but I grouped the data on all relevent columns so the columns not included in the groupby are gone.
I did not use external tools to analyze my model since the error is happening in Service, but I would assume a quicker refresh in Desktop = quicker refresh in service.
This table does not interact with any other tables so this is pretty much my data model, and I can create dummy data it that would be helpful.
Here is a screenshot of my "Advanced Editor".
Thank you so much!
Sorry, those screenshots are too small to read even when I zoom.
Can you post the code as text from the advanced editor?
Hi meirlicht ,
Thanks for your feedback.
If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Best Regards,
Henry
