Data set from Excel Sharepoint Online and Database

[aguffin]

Hi,

I have two data sources. One is an excel document on sharepoint online (office 365). The other is the SQL server database. I need both datasources to be in the same dataset because I need to connect/join them to do comparisons.

I'm very familiar with creating datasets using the database on the desktop and publishing that to the power bi services. I also have figured out how to make a dataset on the power bi web that connects to sharepoint online (office 365). I haven't figured out how to do both together in the same dataset. 

When I try to connect to sharepoint online using the power bi desktop, I get a message "Details: "Access to the resource is forbidden." Ironically, I have no problem accessing the right excel file using the power bi web service. I need them both together, though. Whatever option I choose will need to be able to have either a scheduled refresh or auto refresh.

[v-diye-msft · Accepted solution]

Hi @aguffin 

Based on my test , I am able to join the data source in SharePoint with the data source in the database in desktop, and then publish it to service with schedule refresh:

1.  Get data > SharepointOnlinelist > add Excel data

2. Get data > SQL server databse> Import mode >add data

3. Join them as your expected

4. Save and Publish to service

5. Manage the gateway, add Database info under the data source setting.

6. Make sure you have ticked the below option:

7. Set the schedule refresh.

For the error: "Access to the resource is forbidden."

1. You can refer to the similar solved post for help:

https://community.powerbi.com/t5/Desktop/SharePoint-List-Access-to-resource-is-forbidden/td-p/51475

2. Also another solution for your reference:

When receiving the message, “Access to the resource is forbidden” with ODATA connections to Project Server / Project Online from PowerBI or Excel, check for an active delegation session.

Stop your delegation session in the Project Web App (PWA) to resolve the Access to the resource is forbidden error.

Launch the Project Web App, on the warning bar, click here to manage your delegation.  Click the Stop Delegate Session button.

[GilbertQ]

Hi there

This can certainly work.

Just make sure when connecting to the SharePoint Online that you go into the Excel file, click on on Info and copy the URL there.

Here are more details on how to do this: https://docs.microsoft.com/en-us/power-bi/service-excel-workbook-files