Xml processing fails, but succeeds after copy paste
I have an .xml file, obtained by using the export function of some software our customer uses, which he wanted to analyse with PowerBI. However, trying to load it with GetData results in the following error:
Details: "Xml processing failed. Either the input is invalid or it isn't supported. (Internal error: Name cannot begin with the '.' character, hexadecimal value 0x00. Line 1, position 2.)"
The .xml file doesn't have a period at this position, and no names seemed to start with a '.'. After some trial and error, I found that if I open the xml in notepad or sublime, copy it's entire contents with Ctrl-C, then paste it in a new xml file, save it, and upload that one, PowerBI loads it with no problem. This new .xml file also is significantly smaller than the original (110 vs 55 MB).
I can open the files and copy paste its content, but on the customer's computer this is difficult due to the file's size. Plus, the files are to be loaded into a blob storage so that PowerBI can combine their data. If there's one un-copy-pasted file in the blob storage, the refresh of the whole published dataset will silently fail from there on out, until the offending file is found and removed. This makes the copy-paste trick unreliable as a permanent fix.
Does anyone know what could be causing this problem, and what would be an easy way to fix it? If needed, here are two small sample files, one working, one not working.
Thank you. I can get your solution to work on my end too. I haven't been able to get it to work with Xml.Tables instead of Xml.Document yet (I get an error that value=1200 can't be converted), but that is probably solvable.
However, unless I'm mistaken, I don't think this solution will work for the desired link of this PowerBI project to an Azure Blob Storage which contains the xmls. In those cases, I don't define explicitly which xml files it needs to load, and in fact it's designed so I don't need to know or define this. This way the customer can just store new xmls and be sure their data gets added to the project. But for this fix, I do need to define explicitly the xml files and their locations. If I'm assuming incorrectly here, let me know.
For my part, I'll see what we can do about the original encoding of the xmls. I doubt the program that exports those files can be customized in this regard, but I can try.