Reply
Frequent Visitor
Posts: 3
Registered: Friday

Dataset and tranformations...cannot refresh on web service

Hi guys

I’m stuck. I’m working on a report in Power Bi, in Desktop I have created a dataset using 10 excels that are all under 100MB each, I have had to append 5 into one table and 5 into another. From there I have taken all my figures columns and unpivotted them, made 3 new calculates columns, unpivotted them, the re-pivoted the original attributes to the values.

However when I try and refresh the dataset in desktop, it crashes, if I create the dataset using smaller model, publish to the online service, put the original model back and try and refresh it times out online

Am I asking it to do too much, or have a I missed something?

I have a gateway that works because it refreshes fine with my smaller test model, both models have the same structure just one has more data than the other

Any ideas?
Moderator
Posts: 9,540
Registered: ‎03-10-2016

Re: Dataset and tranformations...cannot refresh on web service

[ Edited ]

@JamesRLUK,

How many rows and columns in each of the Excel file? What is the size of your PBIX file? What detailed error message do you get when refreshing data in Power BI Desktop and Power BI Service? Complex data model and multiple transform steps in Power BI Desktop may eat a large amount of memory during the refresh and cause the crash problem. In your scenario, it is recommended to import required columns and rows into Power BI.

In addition, please make sure that you are using latest version of Power BI Desktop and Power BI gateway.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 3
Registered: Friday

Re: Dataset and tranformations...cannot refresh on web service

Thanks for replying

The problem is I have hundreds of thousands of rows combined and 128 columns at base and by unpivotting etc I add one new column and 5 rows per initial entry...I’m asking a lot of power bi I know, currently I have 2 options I’m aware of;
1. transform in azure and load in that way (but delays my due date of the required report significantly)
2. Transform the data pre load but increasing the file size of the excels I do load into power bi

I am having to load my 10 files one at a time this week in desktop to see if it allows it and of not at which point it times out and has a ‘heart attack’ as I’ve been telling my end users
Moderator
Posts: 9,540
Registered: ‎03-10-2016

Re: Dataset and tranformations...cannot refresh on web service

@JamesRLUK,


Could you please export gateway log and share gateway log here?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 3
Registered: Friday

Re: Dataset and tranformations...cannot refresh on web service

We do not have admin access to the gateways in my organisation, but I have asked them if they can send something across to me.