Forum Discussion
Encoding
- 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 It actually worked when I changed the line above from:
#"Added Custom" = Table.AddColumn(#"Content", "JSON", each Json.Document([#"Content"], TextEncoding.Utf8)),
to:
#"Added Custom" = Table.AddColumn(#"Content", "JSON", each Json.Document([#"Content"], TextEncoding.Windows)),
Thanks, darlove!
Super UserHey 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
DHi darlove,
Thank you for your reply. That has certainly broadened my understanding of Power BI.
I guess my code should end up looking something like this when implementing the adjustments you describe:
let
Source = AzureStorage.DataLake("https://MyDL.dfs.core.windows.net/Endpoint/clients"),
#"Content" = Table.SelectColumns(Source,{"Content"}),
#"Added Custom" = Table.AddColumn(#"Content", "JSON", each Json.Document([#"Content"], TextEncoding.Utf8)),
#"Expanded JSON" = Table.ExpandListColumn(#"Added Custom", "JSON"),
#"Expanded JSON1" = Table.ExpandRecordColumn(#"Expanded JSON", "JSON", {"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 JSON1"Unfortunately the result is the same. I still get the special characters looking like this:
Best
Axel