Forum Discussion
Encoding
I created a nifty little big data extraction from multiple endpoints to ADLS gen2. I have full control of the encoding of the blobs in the data lake endpoint.
Currently I'm using iso-8859-1 (UTF-8) since I have both danish and eastern european characters in my extractions.
All files are in json format, since the schema can differ in many of the sources.
Unfortunately, Power BI does not recognice the encoding on the json files in ADSL. This leads to danish and eastern europe characters being displayed as ? in the text lines, when imported into Power BI.
When the files are opened in Notepad++ encoding is clearly UTF-8 (as expected), but how do I get Power BI to display this encoding correctly.
Alternately, how should I encode the files to make Power BI play nice?
Any help would be greatly appreciated!
Data extracted from Dýnamics 365 CRM directly to the same ADLS behaves exactly the same. It is also UTF-8 in notepad++, but displays wierd characters in stead of the correct danish and eastern european characters.
- Instead of transforming the column to JSON, you have to use Add Custom Column and type the right expression according to this syntax:
Json.Document(jsonText as any, optional encoding as nullable number) as any
Here's the documentation of the function:
https://docs.microsoft.com/en-us/powerquery-m/json-document
Best
D
5 Replies
Hey Anonymous ,
if you are using the function Json.Document( ... , ...)
Try TextEncoding.Utf8 as 2nd parameter.
Maybe you have to use the "Advanced Editor", to see what's really going on inside your query.
Hopefully, this provides some ideas to tackle your challenge.
Regards,
Tom
Stay safe, stay healthy, and happy holidays
Hey Tom,
Thank you for our speedy reply!
Where exactly ios that going to go?
let
Source = AzureStorage.DataLake("https://MyDL.dfs.core.windows.net/Endpoint/clients"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Parsed JSON" = Table.TransformColumns(#"Removed Other Columns",{},Json.Document),
#"Expanded Content" = Table.ExpandListColumn(#"Parsed JSON", "Content"),
#"Expanded Content1" = Table.ExpandRecordColumn(#"Expanded Content", "Content", {"id", "name", "is_active", "address", "statement_key", "created_at", "updated_at", "currency", "self"}, {"id", "name", "is_active", "address", "statement_key", "created_at", "updated_at", "currency", "self"})
in
#"Expanded Content1"- Instead of transforming the column to JSON, you have to use Add Custom Column and type the right expression according to this syntax:
Json.Document(jsonText as any, optional encoding as nullable number) as any
Here's the documentation of the function:
https://docs.microsoft.com/en-us/powerquery-m/json-document
Best
D
