Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have an Excel file that loads data from Azure SQL DB (through Power Query and Power Pivot). The problem is that when I configre a resfresh with Power BI Service all other users (except my self) is no longer able to refresh the file in Excel Online.
I have done the following:
1. Created a Power BI Group/Workspace
2. Uploaded the Excel fil to the groups OneDrive (or Sharepoint folder?)
3. Given all members admin permissions in the Power BI workspace and explicit Full permission on the Excel file in the Sharepoint folder.
At this point the manuel refresh in Excel Online works!
4. Now I configure automated refresh i Power BI and enters the basic credentials to Azure SQL DB on the data source.
5. The automated refresh works but now the manuel refresh in Excel Online stops working for everyone except my self.
This is the error and i comes for every table.
My theory is that Power BI somehow hijacks the connection or at least the credentials used by the connection.
Hi @jeppesal,
I already involved someone more familiar with this topic for a further look at this issue. Your patience is greatly appreciated.
Thank you for your understanding and support.
Best Regards,
Qiuyun Yu
Hi Qiuyun_Yu,
Thank you for taking action, I will wait for any news.
Best Regards,
Jeppe
Hi @jeppesal,
I have gotten confirmation from our Product Group that this is working as designed. In case when User1 defines the credentials in Power BI and User2 does the refresh, we use User1’s refresh token to do the refresh. In case of refresh from SharePoint (OneDrive), it isn't aware of User1’s refresh token and calls Power BI with User2’s identity.
If you have any further questions on this, please let me know.
Best Regards,
Qiuyun Yu
Hi Qiuyun,
So what should I do make this work or create a similar setup that works?
I just whant a file that is automaticaly refreshed via Power BI and can be manually refreshed by the users.
Best Regards
Jeppe
Hi @jeppesal,
I got the feedback below:
"There is no easy way to do that for refreshing within Excel Online and within Power BI. Essentially once they make the connection in Power BI that takes precedence. At that point the only way to properly refresh it manually would be from within Power BI since it’d require the end user to “Take Over” control on the dataset to process the manual refresh.
For that to work, they would need to store the files within a group OneDrive location and connect within that group in Power BI to the file. The users would then be able to log into Power BI, go to the dataset, and select “Take Over” when they wanted to refresh the file manually. "
Best Regards,
Qiuyun Yu