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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BE50
Frequent Visitor

Power Query: Load xml suddenly fails - due to file size?

I have helped clients with a number of Excel solutions where Power Query is a part. In a reporting solution I get data from a number of sources in xml-format. It has been working for more than a year and similar solutions are also working.

Today I got a support request as the reporting stoppef with an error message from PQ:

"Unexpected error: Operation is not valid due to the current state of the object." followed by a long list of error code.

I have tried to isolate the root and have found that file size is the only trigger. I can reproduce the error with this simple query (the actual query is much longer and only one of many other queries):

 

let
    Source = Xml.Tables(File.Contents("X:\xml\InvoiceLines.xml"))
in
    Source

 

The report loads a lot of similar files with financial information. It has been working with me and with the customer for a year. By changing content of the file I have isolated the error trigger to be filesize:

Size < 3.877.134 bytes: Data loads

Size > 3.877.829 bytes: Error

I repeat the same data lines so it is not the content - only the size.

I am complete in the dark here - what is going on???

EDIT 1: I have tested on PC #1 and PC #2 with similar results

PC #1 Win 10, 16 GB ram, 1,5 TB SSD, Excel® for Microsoft 365 MSO (Version 2206 Build 16.0.15330.20216) 64-bit

PC #2 Win 10, 16 GB ram, 400 GB SSD, Excel® til Microsoft 365 MSO (Version 2206 Build 16.0.15330.20144) 32-bit

EDIT 2: I can load the same data in csv format. The file in my test is >15.000 kb and loads as expected.

EDIT 3: Today everything is fine again on PC1 after an Excel update. PC2 (not updated) update still fails with same test files.

PC #1 version was     Version 2206 Build 16.0.15330.20216

PC #1 version is now Version 2206 Build 16.0.15330.20260

4 REPLIES 4
miguel
Community Admin
Community Admin

Hey!

 

Could you please share a sample file where I could repro this behavior? I've been attempting to repro the bug, but I'm unable to do so.

 

Is this happening in Power Query for Excel? in Power BI Desktop? or is this in the Power Query Online experience through Power BI Dataflows? if it's in any desktop application, what's the version of the desktop app that you're using and the Power Query version?

Hi,

The problem was in Power Query for Excel Office 365 E5 desktop installation, but it already disappeared after last update. Thank for your engagment.

BE50
Frequent Visitor

OBS: See edit of my original post.

Here is a link to a Dropbox folder with 2 files:

filesize-test.xlsx with a simple query. You must edit the source in the query to the correct path.

InvoiceLines.xml which is too large (5.316 kb)

I run PQ from Excel desktop.

sborow
Regular Visitor

Same here. I can't even start new query in empty spreadsheet with larger XMLs. It happened after last office update - on the other computer where it is not updated everything works well as before.

 

Any ideas, anyone?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.