Forum Discussion
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}, {'from': 1200001, 'until': 11999999, 'value': 80000}, {'from': 12000000, 'until': -1, 'value': 120000}]
each from, until, value need to be in a separated row. the next shot is the tabled showed in Power Query
5 Replies
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"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"}),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'}]
Anonymous So, dumb question, but is this coming from a JSON file? Because something like this works fine when importing via JSON connection.
{ "_id": "61f872", "studyvalue": [{"from": 200000, "until": 700000, "value": 40000}, {"from": 700001, "until": 1200000, "value": 60000}, {"from": 1200001, "until": 11999999, "value": 80000}, {"from": 12000000, "until": -1, "value": 120000}], "paymentpercentage": "7" }Otherwise I believe you will need to use Json.FromValue:
Json.FromValue - PowerQuery M | Microsoft Docs
