Forum Discussion

jmillsjmills's avatar
jmillsjmills
Icon for Helper III rankHelper III
4 years ago
Solved

Parsing 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...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    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's avatar
jmillsjmills
Icon for Helper III rankHelper III
4 years ago

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's avatar
PhilipTreacy
Icon for Super User rankSuper User
4 years ago

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's avatar
    jmillsjmills
    Icon for Helper III rankHelper III
    4 years ago

    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!