Transform Json record into Table

[Anonymous]

Hi

i ave this Json dataset and I want to transform it into a table with columns indicated in the json: Prodcut_ID and PRICE

the values are int rows records:

{
    "columns": [
        {
            "name": "PRODUCT_ID",
            "type": "text"
        },
        {
            "name": "PRICE",
            "type": "float"
        }
    ],
    "rows": [
        [
            "A26",
            0.30447
        ],
        [
            "A158",
            1.0970999
        ],
        [
            "A18898",
            0.205
        ],
        [
            "A25177",
            19.4
        ],
        [
            "A265",
            130.95
        ]
    ]
}

My target output:

    PRODUCT_ID  |    PRICE    
---------------+---------------
A26             |0.30447        
A158            |1.0970999      
A18898          |0.205          
A25177          |19.4           
    

Thanks in advance

[Anonymous]

Thanks @ImkeF for your help, it works 

[ImkeF · Accepted solution]

Hello @Anonymous ,
first you parse the JSON and then extract the values as follows:

let
    Query1 = "{#(cr)#(lf)    ""columns"": [#(cr)#(lf)        {#(cr)#(lf)            ""name"": ""PRODUCT_ID"",#(cr)#(lf)            ""type"": ""text""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""name"": ""PRICE"",#(cr)#(lf)            ""type"": ""float""#(cr)#(lf)        }#(cr)#(lf)    ],#(cr)#(lf)    ""rows"": [#(cr)#(lf)        [#(cr)#(lf)            ""A26"",#(cr)#(lf)            0.30447#(cr)#(lf)        ],#(cr)#(lf)        [#(cr)#(lf)            ""A158"",#(cr)#(lf)            1.0970999#(cr)#(lf)        ],#(cr)#(lf)        [#(cr)#(lf)            ""A18898"",#(cr)#(lf)            0.205#(cr)#(lf)        ],#(cr)#(lf)        [#(cr)#(lf)            ""A25177"",#(cr)#(lf)            19.4#(cr)#(lf)        ],#(cr)#(lf)        [#(cr)#(lf)            ""A265"",#(cr)#(lf)            130.95#(cr)#(lf)        ]#(cr)#(lf)    ]#(cr)#(lf)}",
    #"Parsed JSON" = Json.Document(Query1),
    Custom1 = Table.FromRows(#"Parsed JSON"[rows], List.Transform(#"Parsed JSON"[columns], each _[name]))
in
    Custom1

If you copy this code into the advanced editor you will see that the first step contains your JSON (with some line break code) and then the relevant steps follow.