Forum Discussion
jmillsjmills
Helper III
Helper IIIParsing JSON String within Custom Function
Hi, I'm trying to parse a JSON string that currently sits in a column (shown in the screenshot). I've tried either with the record just as a text string ([Column1Copy]), or also with the JSON alr...
Hi jmillsjmills
Your ManipulationQuery is expecting the JSON as a Record, but it's receiving text. So try wrapping the JSON text in Json.Document() before passing it to the function
ManipulationQuery(Json.Document([JsonText]))Regards
Phil
PhilipTreacy
Super User
Super UserHi jmillsjmills
Try this function
(JSON as table) =>
let
#"Expanded regions" = Table.ExpandListColumn(JSON, "regions"),
#"Expanded regions1" = Table.ExpandRecordColumn(#"Expanded regions", "regions", {"boundingbox", "lines"}, {"regions.boundingbox", "regions.lines"}),
#"Expanded regions.lines" = Table.ExpandListColumn(#"Expanded regions1", "regions.lines"),
#"Expanded regions.lines1" = Table.ExpandRecordColumn(#"Expanded regions.lines", "regions.lines", {"boundingbox", "words"}, {"regions.lines.boundingbox", "regions.lines.words"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded regions.lines1",{{"language", type text}, {"textangle", Int64.Type}, {"orientation", type text}, {"regions.boundingbox", Int64.Type}, {"regions.lines.boundingbox", Int64.Type}, {"regions.lines.words", type any}}),
#"Expanded regions.lines.words" = Table.ExpandListColumn(#"Changed Type", "regions.lines.words"),
#"Expanded regions.lines.words1" = Table.ExpandRecordColumn(#"Expanded regions.lines.words", "regions.lines.words", {"boundingbox", "text"}, {"boundingbox", "text"})
in
#"Expanded regions.lines.words1"
In my example file you can see that I'm calling this function to create a Custom Column which contains the table output from the function. Expanding that table gives you the parsed JSON.
Regards
Phil