Forum Discussion

lachlanP's avatar
lachlanP
Helper II
4 years ago
Solved

Parsing a JSON field dynamically, based on another column

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?

 

 

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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


          

2 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    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's avatar
    lachlanP
    Helper II
    4 years ago

    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.