get Json array and generate rows and columns

[Anonymous]

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 

[Vijay_A_Verma]

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]

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]

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'}]

[Greg_Deckler]

@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

[Anonymous]

I'm getting the data directly from a MongoDB Database collection, via Python script