Parsing a JSON field dynamically, based on another column

[lachlanP]

I have a data set where one column, "uuid", is a unique id of the row, and another column is a JSON packet. The JSON packet is structured as follows:

{

       "12345": {

              "key1": "value1",

              "key2": "value2",

              "importantKey": true

       },

       "678910": {

              "key1": "value1",

              "key2": "value2",

              "importantKey": false

       }

}

Where one of the first keys, ex. "12345" is the uuid of the row. Only one of the elements matches the row and I dont care about the rest. 

What I need to do is extract the value of importantKey into a new column. Is there a way to parse a JSON column using M where I reference a json element dynamically, based on the uuid column? For instance, I tried parsing the column to JSON using JSON.Document([json text column]), then from that I tried to expand the record dynamically with:

= Table.ExpandRecordColumn(#"Inserted Parsed JSON", "JSON", {[uuid]}, {"New Column"})

hoping that [uuid] would reference the uuid column. It didnt, I received this error:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

[SpartaBI · Accepted solution]

Hi @lachlanP,

Have a look at the following example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lGqjsmLyVOAgBiIaIySlQKyMFw2O7XSECQZo1SWmFOaCmTr4FBmhKTMCIeyzNyC/KKSxLwS79RKkPKSotJUJHW1OiguMzO3sDQ0GCCnpSXmFKO4DcSuVYrViYa6azQgKQvIWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [uuid = _t, JSON = _t]),
    #"Added Parsed JSON" = Table.AddColumn(Source, "Parsed JSON", each Json.Document([JSON])),
    #"Added Relevant JSON" = Table.AddColumn(#"Added Parsed JSON", "Relevant JSON", each Record.Field([Parsed JSON],[uuid])),
    #"Added importantKey" = Table.AddColumn(#"Added Relevant JSON", "importantKey", each Record.Field([Relevant JSON],"importantKey"), type logical)
in
    #"Added importantKey"

Showcase Report – Contoso By SpartaBI

      

[lachlanP]

Maybe a clearer way to ask the question is:
Is there a way to select a component of a JSON object in Power Query?

In Javascript, for example, I would parse the json object and then I could select the element I want with:

jsonObject[uuid]

Is there something similar in power query? Like:

Json.Document([jsonTextColumn])[uuid]

where "uuid" is another column in the table.