Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

get Json array and generate rows and columns

Got this json array in a column but need to separate its positions by multiple columns and rows [{'from': 200000, 'until': 700000, 'value': 40000}, {'from': 700001, 'until': 1200000, 'value': 60000}...
Need Help
Tips and Tricks
Tutorial Requests
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkwpV9JRiq6OKTUwMDJPK8rPhbCsFIwMQEBHAcIvzSvJzIFJmaNIlSXmlKbCpExAMrU6ClgMBOsyxGqgoRFuE81wmwjRhsNIQ0swwGqmBSEzcXhc1xCrcRA9tbHAkDRVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [_id = _t, studyvalue = _t, paymentpercentage = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"'","""",Replacer.ReplaceText,{"studyvalue"}),
    #"Added Custom" = Table.ReplaceValue(#"Replaced Value",each [studyvalue],each Json.Document([studyvalue]),Replacer.ReplaceValue,{"studyvalue"}),
    #"Expanded studyvalue" = Table.ExpandListColumn(#"Added Custom", "studyvalue"),
    #"Expanded studyvalue1" = Table.ExpandRecordColumn(#"Expanded studyvalue", "studyvalue", {"from", "until", "value"}, {"from", "until", "value"})
in
    #"Expanded studyvalue1"

 

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    This looks good.

     

    One small simplification would be to use the GUI to click Transform > Text Column > Parse > JSON.

     

    This generates the code

     

    #"Parsed JSON" = Table.TransformColumns(#"Replaced Value",{{"studyvalue", Json.Document}}),

     

    instead of

     

    #"Added Custom" = Table.ReplaceValue(#"Replaced Value",each [studyvalue],each Json.Document([studyvalue]),Replacer.ReplaceValue,{"studyvalue"}),

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Greetings. Tried this one, but I got an error message about additional characters found at the end of the JSON entrance. 

      Details: Value = ' Position = 2

       

      here's another example of a field with the same form: 

       

      [{'applicationid': '', 'state': 0, 'changedate': {'seconds': 1652714199, 'nanos': 530721737}, 'userrol': 'system', 'userid': 'system'}]