Forum Discussion
PowerBI DataSet from Excel (Live Connection) data model
Hi powerdell ,
If I understand correctly, you would like to connect to multiple excel files store in Teams or Sharepoint site and want to create a calculated column which extract data from different tables. At the same time, you want to refresh the report every hour. Am I right? When you import files from SharePoint Online, it ensures the work you’re doing in Excel stays in sync with the Power BI service. It is no required to make additional schedule refresh for it. You can review the following official documentation to get more details...
Refresh a dataset created from an Excel workbook on OneDrive or SharePoint Online
And about how to connect to the excel file in sharepoint and create calculated column, you can refer the following links.
Connect the excel file in Sharepoint:
LOADING EXCEL FILES FROM SHAREPOINT
Create calculated column:
Tutorial: Create calculated columns in Power BI Desktop
Combine Columns:
Power BI combine columns from two tables
If the above one can't help you get the desired result, please provide some sample data in your excel files(exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And it is better if you can share asimplified pbix file. You can refer the following link to upload the file to the community.
How to upload PBI in Community
Best Regards
I just wanted to update for anybody reading this, it is not possible.
Yes that is right, it is not possible to create a spreadsheet hosted in O365, use it as a live data source in PowerBI AND create a calculated column or do any data modelling (apart from create a Measure).
The only way it seems is to create a PowerBI gateway, host the document on... yea exactly, completely impractical.
If anybody from Microsoft or the PowerBI team is reading please do consider implementing this feature, to me it seems fairly basic and not that technically challenging to accomplish. The fact it is possible if I hosted the file on a fileserver and used an Application Gateway and refresh schedule is a pretty clear indication this should be implemented for SharePoint/O365 hosted files.
Hi powerdell ,
Thanks for your sharing. For the feture which you referred, you can submit a new idea on the ideas site. It is a place for customers provide feedback about Microsoft Office products . What's more, if feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.
Best Regards