Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
EvPeCO
Frequent Visitor

Get data from API that has separate column and value arrays

 

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?

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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:

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
ImkeF
Super User
Super User

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:

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

EvPeCO
Frequent Visitor

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors