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!
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"
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
Hi 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
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!