Is it possible to connect to an on-premises Sharepoint list?

[andybriggs]

Has anyone successfully connected Power BI Service/Fabric to an on-premises Sharepoint list? We just get an exception error when trying to create the connection yet the Data Gateway works fine with other data sources like SQL Server and Oracle. From the gateway server you can reach the Sharepoint list via Edge (using the same credentials). Sometimes it is possible to connect from PBI Desktop loaded onto the Gateway server but it can be very slow. We can connect to the same Sharepoint lists no problem using Qlik Sense from the data centre using the same credentials. No reported firewall port issues either.

Tried all sorts of people in Microsoft but they have been unable to assist. At one point MS Support stated they didn't think it was possible.

[v-xiaocliu-msft]

Hi @andybriggs ,

I found a similar post.

Solved: Anyone successfully connected to an on-premises Sh... - Microsoft Fabric Community

As a workaround, please try to create a scheduled flow.

1\ Create a scheduled flow 

2\ Use Get items action to get all items from your on-premises sharepoint list.

3\ Create the items in the new datasouse such as sharepoint online list.

Best Regards,

Wearsky

[andybriggs]

Thanks. The similar post was mine when I was seeking an answer the first time. So from what you said the conclusion from yourself and MS Support is that Fabric really doesn't support a connection to an on-premises Sharepoint list. The MS account team, and FabCon24 ask an experts said it "should work".

I tried the Power Automate method you suggested and that doesn't work either.

The dynamic operation request to API 'sharepointonline' operation 'GetTable' failed with status code 'BadGateway'. Error response: { "error": { "code": 502, "source": "uk-001.azure-apim.net", "clientRequestId": "75ca2071-4c27-4d03-a551-502219e11dcc", "message": "BadGateway", "innerError": { "status": 502, "message": "Received error payload from gateway service with ID 246039: <ccon>An error occurred while sending the request.</ccon>.\r\nclientRequestId: 75ca2071-4c27-4d03-a551-502219e11dcc", "source": "https://sharepointonline-uks.azconn-uks-002.p.azurewebsites.net/$metadata.json/datasets/https%253A%2...", "errors": [] } } }

[djurecicK2]

Hi @andybriggs ,

 This is definitely possible and documented here: https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-local-drive#on-premises...

Are you trying to connect using Power BI desktop or to refresh a published report on the Power BI Service. You need a gateway to refresh a report published to the PBI service when data is on prem.

[andybriggs]

Hi, like I mentioned in the original post. This is from Power BI Service via our Gateway cluster or even just trying to create a connection in the Fabric admin portal. It works ok from Power BI Desktop but then that has a different list of connectors.