How to handle CSV style data from a custom connector

I have a custom connector and one of its methods is to return the content of a CSV file. I want to be able to display that content in a DataTable and also have access to the individual column names etc from with the formula.

The issue is, the data could be any shape, so I cannot create a simple Swagger defining the properties, the best I can do is define the response as an array or string arrays or similar, or perhaps a dictionary of key/value pairs.

In any event, I cannot figure out how to return the data in a format that is digestible by the platform (say to display in a DataTable) because what the platform sees is simple an array of tables, one for each row.

I figure I need to transform the API response in a Table using some sort of ForAll loop but I dont know what the correct way to do that is:

How to correctly configure my API to send back arbitrary CSV file content
How to consume that in the platform and transform it so it can magically create a single Table using the column names found in the response

For arguments sake, lets say my API defines the response like below (array of rows where each row is an array of objects each with a Name and a Value property representing column name and value)

"schema": {
 "type": "array",
 "items": {
 "type": "array",
 "items": {
 "$ref": "#/definitions/CsvFileColumnDto"
 }
 }

 "CsvFileColumnDto": {
 "type": "object",
 "properties": {
 "name": {
 "type": "string"
 },
 "value": {
 "type": "string"
 }
 }

[ [
 {
 "name": "ColA", "value": "Test"
 },
 {
 "name": "ColB", "value": "Row"
 }
 ],
 [
 {
 "name": "ColA", "value": "Test"
 },
 {
 "name": "ColB", "value": "Row"
 }
 ]
]

How can I use a formula or some other feature to turn that data into a Table when the headers are 'ColA' and 'ColB' and the table data is the values of 'value'?

And also what is the best way to return that style data from an api? Returning the column name over and over again per row seems wasteful, it would ideally make more sense to return an object with an array of strings (lets say called Header) and then an array of string arrays (called Rows) but this would seem even further away from what PowerApps is happy with.

Categories:

Connectors

Connecting to Data