DataFormat.Error: 'x' is an undeclared prefix. Line 2, position 1344

[remojsy]

Hi all

I am having an issue when combining multiple Excel files (stored in SharePoint online) in a Dataflow (or in PowerQuery).  Annoyingly, the preview values of my transformation steps are fine however when I refresh the complete dataset I get the error:

"DataFormat.Error: 'x' is an undeclared prefix. Line 2, position 1344."

I assume this will be an problem with one of the Excel files however I am unable to determine which file has the issue (adding a 'keep errors' step doesn't help).  Any guidance to either find or mitigate the issue will be very gratefully received.

Rem

[edhans]

If you don't have a lot of excel files, just remove one at a time and refresh each time to see which one stops causing the error.

If you have a lot, remove half, then refresh. then remove half again, refresh, etc. Sometimes it is trial and error to get the issue to pop up.

You could also try sorting the data in PQ but if it is a lot of records and the error is in the middle of your sort, it won't help much.