Dataset and tranformations...cannot refresh on web service

[JamesRLUK]

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?

[Anonymous]

@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

[JamesRLUK]

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

[Anonymous]

@JamesRLUK,

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

Regards,
Lydia

[JamesRLUK]

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.