Custom Connector - CSVs & Xlsx data sources in SharePoint.

[GrahamJH]

Good morning All,

I'm contemplating writing a connector to provide access to various CSVs and Xlsx data sources store in SharePoint.

The dashboard that will consume this will reside in PowerBi service.

Asks:

1. Is an on-premises gateway required for this work, or simply at the publication?
2. Has this been done before?
3. The data sources in SharePoint are updated every 24 hours.  Would this pose any challenges for the connector?

Many Thanks, Graham

[ibarrau]

Hi. Getting data from many csv or xlsx files in sharepoint is really used by almost all companies. If you sharepoint is online then you don't need a gateway, if you are talking about an onpremise one, then yes you need one.

In case this is online you can check content all over internet like: https://www.youtube.com/watch?v=XuLnSYjmsJo

That will help you connect your data.

If you are working onpremise you would use the "Folder" connector.

I hope that helps,

[GrahamJH]

Thanks!

Is the also direct support of oauth?

[ibarrau · Accepted solution]

Yes. The sharepoint connector will let you pick authentication and oauth is one of them. All those common data sources are perfectly configured and ready to use at Power Bi.

Regards

[GrahamJH]

Hello,

In hindsight I should have been clear that we need to use a Service Principal from an access / authentication perspective.

PowerBi Desktop supports the connection to the CSVs in SharePoint over the Web connector.  However, at this point, there’s no support for using Service Principal credentials.

Once the report is Uploaded to PowerBi Service, there is the option to use Service Principal credentials.  Unfortunately, when one tries to, this fails.  Done all the checks to make sure the details provided are valid.

I’ve an open support call with Microsoft for this.  It transpires that although it appears possible through the UI there is a limitation in the product, essentially its not possible.  Something MS are working on…so this has been super frustrating. 

Contemplating moving the data to Storage Blob, as it looks like this may well be supported using a Service Principal.

[ibarrau]

Check this issue that looks similar. They say that changing a policy setting they were able to do it.

https://community.fabric.microsoft.com/t5/Service/Adding-a-SharePoint-data-source-with-a-service-pri...

I hope that helps,