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": "...
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


          

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