Forum Discussion

tfmei140's avatar
tfmei140
Copper Contributor
Aug 13, 2024

Table from JSON object

I get the following JSON response from an API call

 

[
    [
        {
            "field": "ID",
            "value": 29
        },
        {
            "field": "Created at",
            "value": "06/08/2024 15:18"
        },
        {
            "field": "Created by",
            "value": "Amanda"
        },
        {
            "field": "Job Card Status",
            "value": "Final"
        },
        {
            "field": "Sales Amount",
            "value": "2500"
        }
    ],
    [
        {
            "field": "ID",
            "value": 28
        },
        {
            "field": "Created at",
            "value": "06/08/2024 15:16"
        },
        {
            "field": "Created by",
            "value": "Amanda"
        },
        {
            "field": "Job Card Status",
            "value": "Final"
        },
        {
            "field": "Sales Amount",
            "value": "15400"
        }
    ]
]

 

and need to create a table in Power Query to create the bar chart in Power BI. The bar chart shows 'Sales Amount' by week. The x-axis represents weeks and the y-axis aggregates 'Sales Amount' for a given week.

 

Using the API call I'm getting this 2 column table

and with my limited Power Query skills I can't transform this table to the target table with the columns ID, Start Date and Sales Amount which I then can use to create the bar chart. How do I approach this?

No RepliesBe the first to reply

Resources