topic Re: Custom data connector schema in Developer

Custom data connector schema

Anonymous — Thu, 16 May 2019 10:11:51 GMT

Hey guys,
Do you know the best way (if there is one) to program a custom data connector to receive a bunch of json data and parse the right data types of columns? So when i access the data on Power BI it shows the correct data type.

Context: i get my data from a REST API with a custom made Web Service that gets a Query_ID and retreives that data.

I read Advanced Schema of msdocs and didn't find an answer.
Thanks.

Re: Custom data connector schema

tonmcg — Thu, 16 May 2019 13:54:36 GMT

Does the API also provide metadata or response headers that includes data type definitions for that resource? If so, you could just parse those definitions and map them to Power BI data types. I've done something similar with resources on the Socrata Open Data API. Here's how that API define data types for columns in the Building Permits resource, which it includes in the response headers (scroll down to the Fields section of the page).

If your API adheres to the OData specification, the resource should have a corresponding metadata resource that defines data types. The TripPin API example is a good one, because it hews closely to the full OData spec.

For example, a call to the People resource provides a link to the People metadata resource, which contains data types for the columns in the original People resource.

If the API does not provide data types in a metadata resource or within the response headers, you could also just use the Determine Data Types function from the Power Query Editor UI to define data types.

Can you provide an example that contains both the JSON data and if possible, the data type definitions supplied by the API? If this is a public API, can you share the root endpoint?

Re: Custom data connector schema

Anonymous — Thu, 16 May 2019 14:54:16 GMT

Hi, thanks for answering.
I don't think i understand well how metadata is represented in http headers.
The headers of the response are like this:

Unfortunately, the API is not Open Data, and my pq connector reaches out to a custom made WS to receive an Url, QueryID and Parameters. Here is the connector code and result in json:

I can see in the TripPin example that we have access to a metadata column.
In my case, i see the data types of each JProperty in another JArray of the response, that i chose not to retreive in the Web Service, because the actual data is on dataSource. I have thought about fetching that dataType in the WS, but i don't know how to insert it in the result. Is it a good approach?

About the Determine Data Types function from the Power Query Editor UI, it doesn't get all data types right. My goal would be for the connector to determine the data types given any QueryID.

This is the first project of my internship, so i'm not yet very familiar with how data requests work.
Thank you.

Re: Custom data connector schema

Anonymous — Thu, 16 May 2019 16:50:55 GMT

Hey again, turns out the custom function in PQ UI Determine Data Types works very well, and i will go with that, thank you for the suggestion.
Cheers 🙂