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

[BE50]

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

[miguel]

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?

[sborow]

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]

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]

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?