Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Loop Json records to generate table with dynamic columns

Hi

 

I am developping a custom connector that calls API REST with customized query.

 

In the query we can ask for multiple columns

 

My question is how to fetch the result of the API to define the structure of the table and extract dynamically the list of columns:

 

The API response is (with 2 returned columns)

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

 

with 3 returned columns:

{
    "columns": [
        {
            "name": "PRODUCT_ID",
            "type": "text"
        },
        {
            "name": "PRICE",
            "type": "float"
        }
,
        {
            "name": "Category",
            "type": "string"
        }
    ],
    "rows": [
        [
            "A26",
            0.30447,
            "category AA"
        ],
        [
            "A158",
            1.0970999,
            "category BB"
        ],
        [
            "A18898",
            0.205,
            "category CC"
        ]
    ]
}

 

Target table:

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

 

OR

 

    ID_ART     |    QTE*PMP    |   Category    
---------------+---------------+---------------
26046          |0.30447        |category AA
158            |1.0970999      |category BB
18898          |0.205          |category CC
Need Help
Tips and Tricks

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. I had to add "" to your JSON to paste it in the Source but you won't need to do that. Just use Json.Document on your source, and then use the expressions in the following steps.

     

     

     

    let
    Source = "{
    ""columns"": [
        {
            ""name"": ""PRODUCT_ID"",
            ""type"": ""text""
        },
        {
            ""name"": ""PRICE"",
            ""type"": ""float""
        }
,
        {
            ""name"": ""Category"",
            ""type"": ""string""
        }
    ],
    ""rows"": [
        [
            ""A26"",
            0.30447,
            ""category AA""
        ],
        [
            ""A158"",
            1.0970999,
            ""category BB""
        ],
        [
            ""A18898"",
            0.205,
            ""category CC""
        ]
    ]
}",
    Custom1 = Json.Document(Source),
    Custom2 = Table.FromColumns(Custom1[rows], List.Transform(Custom1[columns], each [name]))
in
    Custom2

     

    Pat