Forum Discussion

EvPeCO's avatar
EvPeCO
Frequent Visitor
6 years ago
Solved

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 ...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    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:

     

ImkeF's avatar
ImkeF
Community Champion
6 years ago

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's avatar
EvPeCO
Frequent Visitor
6 years ago

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