Creating connection in model - 4+ hours -Financial Spreadsheet

[hangell]

Hi,

I have an issue where a financial spreadsheet that is used for multiple reports was recently changed slightly.

Nothing major, a new column and 2 column headings changed slightly. I edited the Data Source settings to point PowerBI at the new version, got the anticipated errors and started to fix them.

This spreadsheet contains multiple sheets, but PowerBI only uses 1 sheet and creates 3 tables from it, each table with a different format and purpose.

As I was nearing the end of fixing the "errors" due to the modifications to the spreadsheet I exited the Query using "Save and Load" and the Load screen appeared and got to the "Creating connection in model..." stage but that's where it stayed for 4 hours until I quit.

Today I tried a new approach as I thought the Load time was unsual and maybe my query mods were the issue, I went into the spreadsheet and removed the new additions, so deleted the new column and reverted the 2 columns to their original title.

So now the only difference in the spreadsheet to the one PowerBI already has loaded is the financial values have changed as it's been a few months since the last update. I attempted to load the "updated" spreadsheet and I've got the same problem with "Creating connection in model...", we're now at 3 hours. Now As far as I can see the spreadsheet is now identical in layout, there are a few more rows of data (approx 300 vs previous 200) but nothing else. 

Is there anyway I can access a report to see what is casuing the Load to take so long, my computer specs are Core i5-U @GHz and 16GB RAM. CPU Usage is 30-70% and RAM usage is 70-85%. This is only a 300 row 80 column numerical spreadsheet, surely this isn't a hardware issue?

Any advice here would be appreciated.

Thanks

[V-lianl-msft]

Based on your description, in PBI, you create another table by transform data.If so, this operation and other transformations( merge/appen queries, etc.) will consume memory and cause the performance issue as you.

It is also possible that the query cannot be applied due to the cache and mismatched fields.

You can try to save the previous report as a template file, and then reconnect to the new data source to see if the problem still exists.

[jdbuchanan71]

If you make a connection to the sheet in a new .pbix file does it give you the same problem?  I agree the behaviour does not make much sense.

[jdbuchanan71]

@hangell 

Try disabling the options under the data load tab for the current file, see if that helps.

[hangell]

Hi,

Thanks for the tip, sadly no luck still stuck in "Creating connection in model..." for 2+ hours. Anything else I can try, really don't understand why the spreadsheet is causing such issues.