Parsing JSON String within Custom Function

[jmillsjmills]

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 already parsed ([Column1])..

My custom function in M code is as follows, but I always get errors. Hopefully you can see what I'm trying to do. I should be able to invoke this custom function with [Column1] as the JSON input/variable. This JSON string should get sent into the function, which then parses it/manipulates it accordingly.  

I don't care whether I parse the JSON string before the function (i.e. using [Column1]) or within the function (i.e. using the unparsed data in [Column1 - Copy]), I just want it to work!! I need to be able to send the JSON string into my custom function by invoking it via the column. Does that make sense? I've also included below an example of a JSON string, in case it's useful.

Thanks!!

let
#"Table" = (symbol) as table =>
let
#"Parsed JSON" = Record.ToTable(Json.Document(symbol)),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"language", "textAngle", "orientation", "regions"}, {"language", "textAngle", "orientation", "regions"}),
regions = #"Expanded Column2"{0}[regions],
#"Converted to Table" = Table.FromList(regions, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
in
#"Table"

JSON:

{"language":"en","textangle":0.0,"orientation":"up","regions":[{"boundingbox":"6,4,848,1007","lines":[{"boundingbox":"350,4,160,19","words":[{"boundingbox":"350,4,89,19","text":"ground"},{"boundingbox":"449,4,61,19","text":"floor"}]},{"boundingbox":"289,32,281,15","words":[{"boundingbox":"289,32,52,15","text":"approx."},{"boundingbox":"352,32,30,12","text":"51.0"},{"boundingbox":"386,35,21,12","text":"sq."},{"boundingbox":"413,33,47,11","text":"metres"},{"boundingbox":"465,32,44,15","text":"(548.9"},{"boundingbox":"514,35,20,12","text":"sq."},{"boundingbox":"539,32,31,15","text":"feet)"}]},{"boundingbox":"574,227,80,14","words":[{"boundingbox":"574,227,80,14","text":"bedroom"}]},{"boundingbox":"176,296,66,18","words":[{"boundingbox":"176,296,66,18","text":"lounge"}]},{"boundingbox":"520,465,69,18","words":[{"boundingbox":"520,465,69,18","text":"hallway"}]},{"boundingbox":"443,645,86,15","words":[{"boundingbox":"443,645,86,15","text":"bathroom"}]},{"boundingbox":"149,656,67,15","words":[{"boundingbox":"149,656,67,15","text":"kitchen"}]},{"boundingbox":"748,764,78,15","words":[{"boundingbox":"748,764,78,15","text":"entrance"}]},{"boundingbox":"782,786,32,18","words":[{"boundingbox":"782,786,32,18","text":"bby"}]},{"boundingbox":"188,943,483,20","words":[{"boundingbox":"188,943,46,16","text":"total"},{"boundingbox":"242,946,46,13","text":"area:"},{"boundingbox":"296,946,68,17","text":"approx."},{"boundingbox":"378,943,40,16","text":"51.0"},{"boundingbox":"425,946,27,17","text":"sq."},{"boundingbox":"460,944,62,15","text":"metres"},{"boundingbox":"530,943,58,20","text":"(548.9"},{"boundingbox":"595,946,26,17","text":"sq."},{"boundingbox":"629,943,42,20","text":"feet)"}]},{"boundingbox":"6,975,848,17","words":[{"boundingbox":"6,975,32,13","text":"this"},{"boundingbox":"44,975,31,17","text":"plan"},{"boundingbox":"82,975,12,13","text":"is"},{"boundingbox":"99,975,21,13","text":"for"},{"boundingbox":"125,975,85,13","text":"information"},{"boundingbox":"217,978,69,14","text":"purposes"},{"boundingbox":"292,975,31,17","text":"only"},{"boundingbox":"329,975,27,13","text":"and"},{"boundingbox":"363,975,11,13","text":"is"},{"boundingbox":"381,976,23,12","text":"not"},{"boundingbox":"409,975,76,13","text":"warranted"},{"boundingbox":"491,978,17,10","text":"as"},{"boundingbox":"514,978,18,10","text":"an"},{"boundingbox":"538,975,62,13","text":"identical"},{"boundingbox":"607,975,46,17","text":"image"},{"boundingbox":"659,976,14,12","text":"to"},{"boundingbox":"678,975,23,13","text":"the"},{"boundingbox":"707,975,63,17","text":"property"},{"boundingbox":"775,975,79,13","text":"concerned"}]},{"boundingbox":"320,995,221,16","words":[{"boundingbox":"320,995,32,13","text":"plan"},{"boundingbox":"359,995,69,16","text":"produced"},{"boundingbox":"435,995,40,16","text":"using"},{"boundingbox":"482,995,59,16","text":"planup_"}]}]}]}

[PhilipTreacy · Accepted solution]

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

[jmillsjmills]

This is perfect Philip! Thanks so much. Exactly what I needed

[jmillsjmills]

Thanks very much but when I run this function and invoke it with the ([Column1]) that contains the already-parsed data, I'm getting an error this error:

Similarly when invoke the function using the the [Column1Copy] (containing the raw JSON as a text string), I get this error:

[PhilipTreacy]

Hi @jmillsjmills 

You don't really need a custom function for this.  It actually complicates things more than needed because you have to parse out the JSON record, then return columns that have to be added back to the original table from which you called the function.

You'd be better off just doing the expansion/parsing in the source table:

Before

After

Regards

Phil

[jmillsjmills]

Hi again Philip,

Thanks so much for your help with this - I really appreciate it! Unfortunately I do have to do it separately within a function as the manipulation requires some expanding onto new rows, and grouping etc, within that particular JSON. I need to be able to manipulate the JSON data from each individual row in isolation. If all the rows of JSON data are expanded in the same aggregate table, some of the filtering (for example) will then go wrong.

As per your previous message, I'm providing the file along with a json file you can reference. You'll see the file you sent me now contains four more items:

• Table (contains the JSON data in rows as a text string)
• generic (JSON file attached, to how the manipulation works directly on a normal JSON file)
• Manipulation - the query set directly , but that I would like to make into a custom function that can be invoked on the column called "JsonText" in Table
• ManipulationQuery - my attempt at turning the above query into a function, such that there is a variable that can be invoked (and that should be invokable with [JsonText] in Table

Link to Power BI File 

Link to JSON for Manipulation Query 

Sorry if that isn't too clear, but hopefully it's a lot easier to have a set file to work with! 

 

[PhilipTreacy]

Hi @jmillsjmills 

Download example PBIX file

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