Importing XML into ExCel with PowerQuery, encoding not working.

[andersbegde]

Hi!

I have a pretty big XML file, in which the encoding is UTF-8.

There's a lot of occurences of special characters (æ,ø,å), which after import only shows up as as questionmark "boxes".

Usually, when importing with the same process, just with CSV->EXCEL, I get an option in the "Navigator" to select the encoding I want. However, with the XML files I do not get that option.

Is there any way to convert the encoding after I've imported?

Thanks

[#2]

@andersbegde 

Not sure, since I'm not the big **bleep** in XML (to put it mildly). But I remembered that someone converted the csv file into text editor and then imported it into Excel and then it worked. but all of this without guarantee. As I said, I know that I don't know anything.

Hope I was able to help you.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here

[#3]

@andersbegde 

Both Xml.Tables() and Xml.Document() have optional encoding parameter (second and third accordingly), you may add it in formula bar.

https://docs.microsoft.com/en-us/powerquery-m/xml-tables

https://docs.microsoft.com/en-us/powerquery-m/xml-document

[#4 · Accepted solution]

@andersbegde @Sergei Baklan @Nikolino 

I've tried @Nikolino 's solution and it works.

What I did step by step was:

1. Open the file in Notepad++

2. Hit the "Encoding" tab and select "Convert to UTF-8-BOM".

3. Save as separate file (doesn't matter I guess but I wanted to keep the other as backup).

4. Open excel and import it normally via Data->Import From File (XML).

Worked exactly as expected.

Thanks for the replies both @Nikolino and @Sergei Baklan 

[#5]

@andersbegde 

Great to know you solved this. However, from my point of view to add one parameter in formula bar is more direct and straightforward way.