Problem with special character encoding (Possibly Latin1)

[kondado]

Hi there,

So, I got a database that possibly has a Latin1 encoding for the text fields. I say possibly because it's a DB that's not being supported that well anymore by the original designers and I have to deal with it within my company. The text shows incorrect characters like "niños" = "niños" which means "boys" or "children" in Spanish.

So I would want to convert the encoding by using something like this:

= Table.AddColumn(#"Added Custom1", "Encoded Column ", each Text.FromBinary(Text.ToBinary([article_name], TextEncoding.Ascii), TextEncoding.Utf8))

Now instead of ASCII, obviously it should be "Latin1". But Power BI doesn't seem to have support for that. What's the alternative then?

[Ahmedx · Accepted solution]

try writing like this:
Text.FromBinary(....... , 1252)
Text.FromBinary(....... ,TextEncoding.Utf8)

Text.FromBinary(Text.ToBinary([Column1],1252),TextEncoding.Utf8))
maybe you will need another unicode :

Text.FromBinary(....... , 1145)
Text.FromBinary(....... ,TextEncoding.Utf8)

Text.FromBinary(Text.ToBinary([Column1],1145),TextEncoding.Utf8))
or

Text.FromBinary(....... , 1256)
Text.FromBinary(....... ,TextEncoding.Utf8)

Text.FromBinary(Text.ToBinary([Column1],1256),TextEncoding.Utf8))

[kondado]

Thank you so much Ahmedx!! You're a genius!

1252 did the trick. The only weird thing was, it was the first one I tried and it didn't seem to work. Even rotated through the output encoding (utf-8, windows, and others). Finally, I tried the 1251 and utf-8 combination again and suddenly it worked. Don't know if there was something cached before or whatever.

[lbendlin]

Your example looks like UTF-8 mis-stored in ANSI.  You might be able to forcibly interpret whatever you get from that database as UTF-8.

There aren't many options otherwise: TextEncoding.Type - PowerQuery M | Microsoft Learn