Get data from API that has separate column and value arrays

[EvPeCO]

I am building a Power BI report with an API data source. I’m connecting to the API endpoint fine but the json that is being returned has two arrays per object, one for the column metadata and the other array providing the values.  Similar to this post: https://community.powerbi.com/t5/Desktop/Load-JSON-array-of-values-with-field-names-in-another-eleme...

Here’s an example of the json that is being returned. I’ve simplified it greatly, but you’ll see the columnMetadata array and the rowData array. I need the FieldName value in the columnMetadata array and the corresponding value from the rowData array.

[

    {

        "jobId":"a",

        "columnMetadata":[

            {

                "name":"JOB ID",

                "FieldName":"job_id",

                "unitText":null,

                "columnIndex":0,

                "dataType":"string",

                "schemaType":"Static"

            },

            {

                "name":"TASK ID",

                "FieldName":"TASK_id",

                "unitText":null,

                "columnIndex":1,

                "dataType":"string",

                "schemaType":"Static"

            }

        ],

        "rowData":[

            [

                "a",

                "t1"

            ],

            [

                "a",

                "t2"

            ]

        ]

    },

    {

        "jobId":"b",

        "columnMetadata":[

            {

                "name":"JOB ID",

                "FieldName":"job_id",

                "unitText":null,

                "columnIndex":0,

                "dataType":"string",

                "schemaType":"Static"

            },

            {

                "name":"TASK ID",

                "FieldName":"TASK_id",

                "unitText":null,

                "columnIndex":1,

                "dataType":"string",

                "schemaType":"Static"

            },

            {

                "name":"TASK NAME",

                "FieldName":"TASK_Name",

                "unitText":null,

                "columnIndex":2,

                "dataType":"string",

                "schemaType":"Static"

            }

        ],

        "rowData":[

            [

                "b",

                "t1",

                "name1"

            ],

            [

                "b",

                "t2",

                "name2"

            ],

            [

                "b",

                "t3",

                "name3"

            ]

        ]

    }

]

In the json example above, you can see a few things:

• The column names are in the columnMetadata array
• The data values are in the rowData array and relate to the columnMetadata based on the ordinal position it’s in.
• The number of objects (columns) for each job can change – in this simple example, the object JobID=a has 2 columns and the object JobID=b has 3 columns.

I’m looking for recommendations as to the best way to proceed. My gut tells me that I’ll have to go down the custom connector path with logic that handles each JobID object, parses the columnMetadata along with the rowData arrays to align the column name and values together. This will need to be done for each JobID object and then at the end put all of them together for a final output to Power BI.

Another thought is that maybe this could be done within the M code. Or maybe even a python script within Power BI to handle this transformation.

1. What is the best to consume this data and prepare it for Power BI – M code, custom connector, or something else?
2. Will there be issues with the raged nature of this data that can’t be coded to address?

[ImkeF · Accepted solution]

Hi JSON results can be navigated fairly reliably. After parsing the JSON and transformed it into a table, just add a column with this formula:

Table.FromRows( 
    _[Column1][rowData], 
    List.Transform(
        _[Column1][columnMetadata], 
        (metadata) => metadata[FieldName])
        )

For the dynamic column names, you can use this expression:

 
List.Union(List.Transform(#"Added Custom"[Custom], Table.ColumnNames) ) 

See file attached:

[EvPeCO]

Thanks @ImkeF for the help. I was able to get this working against the API data source.