Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bertuccio
New Member

Excel.CurrentWorkbook() crashes

Hi All, 

 

Something really weird happened to me, and by extention, my colleagues. I set up recently (like a month ago) a new but very simple query that basicaly takes 2 existing tables, reshapes both onto the same format and order, and appends it. 

 

Both tables are actual tables, with values and formulas, with a name etc. you got it. 

 

So, naturally, I use the Get Date - From a table function to upload both tables. At the time of the setting up, no issue, it was working fine, took seconds to refresh. Yesterday, went in the document to update everything, and the query is locking my Excel entirely, for more than 15 min, so I decided to stop it, then go to Query editor, but it's the same. It took more than 30 to just see the table in the editor, and 30 min again for each move I did. 

 

In that case, I deleted the queries one by one, then step by step, to discover that it was the initial Excel.CurrentWorkbook() that was causing all the troubles. For some reason, I tried on other documents and sometimes it's working fine, sometimes it's not. 

 

Anyone heard about something like this? 

5 REPLIES 5
v-yingjl
Community Support
Community Support

Hi @Bertuccio ,

Actually there is a little difference between Excel.Workbook() and Excel.CurrentWorkbook(), you can refer this blog which introduces it in details:

What is with Excel Tables and the Data Model 

 

One big caveat is that Excel.Workbook() doesn't read from the current data set, it reads from the most recently saved copy of the file. If we use Power BI Desktop to connect to a local file, the query should be used Excel.Workbook() by default.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you, it's very informative. I've actually tried to do it this way, and get data from the document itself by doing Get Data-from Excel. Unfortunately for me, it appears using Office 365 makes this impossible as when I tried to load the Data I have the error message that this document is already in use. I am yet to find a way to bypass this

Hi @Bertuccio ,

 

For heavily-used Excel files, I would recommend looking into using Power Automate Flows to detect when the modified date of the file changes on SharePoint, then copy it to your own 'safe' location elsewhere on SharePoint.

This should ensure you always have the most recently-edited version of the file in a no-lock environment.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




BA_Pete
Super User
Super User

Hi @Bertuccio ,

 

Where are your Excel files stored? Are they local (C: drive), in network folders, or SharePoint?

Are your Excel files shared with other users or o you have them open on your desktop while you're trying to load your PQ queries?

 

I believe any platform that doesn't essentially support 'checking out/in' of documents can cause PQ problems as it creates a 'file being used by another person' conflict.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete, 

 

Thanks for your answer. It's indeed on a sharepoint, and the Excel document itself grabs a lot of info from other sharepoints sources, even Sales Force. I did test to copy and paste this excel document on the desktop to try it out, and first thing I did was to remove the other querries, for no effect. 

 

Update on this though, after 2 days not working at all, today it's ... working fine. I have no idea what happened as I did not upgrade my Office (it's Office 365, so it might be without me knowing) or change any option. The solution was apparently to leave it be for a day or two. 

 

I'll leave this here for a bit if someone encountered this issue before, and maybe found a way.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors