parse JSON list of n values

[murillocosta]

Hi,

I have the following field which is in the following format:

- This field contains ONE or a LIST of patterns inside the brackets as per below:

[

 {"qty":"1","len":"1.32","wid":"1.08","hyt":"1.4"}

]

or

[

{"qty":"1","len":"1.32","wid":"1.08","hyt":"1.4"},

{"qty":"1","len":"1.32","wid":"1.08","hyt":"1.4"},

{"qty":"1","len":"1.32","wid":"1.08","hyt":"1.4"},

{"qty":"1","len":"1.32","wid":"1.08","hyt":"1.4"},

....

]

Is there any way I can loop into this field and get all values for qty, len, wid, and hyt keys?

I need to perform a calculation using all these fields.

Thanks

[ppm1 · Accepted solution]

You can just use the Json.Document function to extract the list of records, convert that to a table, and then expand the fields. See example below. Just create a new query, open advanced editor, and replace the code there with this.

let
    Source = "[

{""qty"":""1"",""len"":""1.32"",""wid"":""1.08"",""hyt"":""1.4""},

{""qty"":""1"",""len"":""1.32"",""wid"":""1.08"",""hyt"":""1.4""},

{""qty"":""1"",""len"":""1.32"",""wid"":""1.08"",""hyt"":""1.4""},

{""qty"":""1"",""len"":""1.32"",""wid"":""1.08"",""hyt"":""1.4""}]",
    Custom1 = Json.Document(Source),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"qty", "len", "wid", "hyt"}, {"qty", "len", "wid", "hyt"})
in
    #"Expanded Column1"

Pat