Integration between One Drive (Forms for Excel) and Power BI Desktop

[Anonymous]

I am building an Excel Survey that will be sent out to multiple different respondents (via one link). I intent to integrate the Excel sheet questionnaire (from within One Drive) with the Power BI Desktop application.

My question is:

1) Do I need to enable OAuth2 credentials to connect to my Power BI with the Excel Form link that is generated ? If yes, I am currently unable to see OAuth2 authentication as a selection while creating the connection. Has this been replaced with some other function ?

2) How can I ensure that when respondents update their questionnaire on the Excel Form OR if new respondents key in new Excel sheets updates, that Power BI could update itself to reflect new responses on its data model and visualisations ? Is there a useful tutorial that can be useful to me on this ?

[v-yuezhe-msft]

@Anonymous,

Firstly, do you use OneDrive for business or OneDrive for personal? If you use the former, you can follow the guide in this article to connect to the Excel file and organizational account  is required. If you use the latter, you can follow the steps in this KB to connect to Excel, and please use basic authentication.

Secondly, after you create report in Power BI Desktop, you can upload the PBIX file to Power BI Service and set schedule refresh for your dataset. This way, once there are any data changes in the Excel file, Power BI report will get updated automatically based on refresh schedule.

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft

Many thanks for your valuable response. It gave me a good understanding. 

The second link KB that you had provided me, resulted in an "Access Denied" error: 

"You do not have sufficient privileges for this resource or its parent to perform this action. Click your browser's Back button to continue."

Could you please check this one and maybe provide me with a link that could work please ?

Secondly, the question I have now is, if the Power BI Service is going to be used throughout in capturing the Excel data and modelling it (before visualising) the result, what advantages can I get by using Power BI Desktop after loading the data into Power BI Service online ?

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please check this thread instead: https://community.powerbi.com/t5/Integrations-with-Files-and/PowerBI-support-for-OneDrive-Personal-as-data-source/td-p/129125.

It is not possible to model data(create relatipnship, create calculated columns/measures, change data types and so on) in Power BI Service, you are only able to model data in Power BI Desktop.



Regards,
Lydia