PowerBI DataSet from Excel (Live Connection) data model

[powerdell]

Hello,

I am fairly new to all this although have been a developer for many years.

My scenario is I have a SharePoint (Teams folder) Excel spreadsheet with 2 key tables. 

I managed to create a PowerBI DataSet that refreshes. I now understand this is using a Live Connection (SSAS) model which limits my ability to add Calculated columns, manage relationships etc.

How do I achieve this type of outcome such that I can present a richer data model to PowerBI.

I thought a DataFlow would be what I needed here. But it seems I cannot import my DataSet into a DataFlow which seems very odd, I must be missing something here?

I think I understand all this is by design and with a SSAS/Live Connection model you should do all modelling in the source system and but for Excel, how to I manage these relationships and use the power of DAX/PowerBI to create a proper model all linked to live Excel table data?

Use Case
1. Import 2 or more (Excel) tables from a single spreadsheet hosted in SharePoint/Teams
2. Created a calculated column or join/merge 2 columns from different tables
3. Have this Excel data refresh via the PowerBI Data Service on a regular basis (1hr is fine)
4. Use this joined data in a PowerBI Report

Any help much appreciated!

[powerdell]

Thank you for this, your summary is correct although only a single spreadsheet (with multiple tables).

However the proposed solution does not work (for me). I already have a PowerBI DataSet linked to my SharePoint hosted spreadsheet. All good here.

But when I open this in PBID I cannot add Calculated Columns (or do any modelling). 

My guess from looking at the tooltip on the table here is that in the background the Power BI platform is using a SQL Analysis Services database to interface/sync changes from my underlying SharePoint Excel spreadsheet/DataSet, hence why I am change the model. But surely I can use this data set in a dataflow? Nope, no option when creating a DataFlow to use a PowerBI DataSet as a source. I don't understand.

All options greyed out, how do I add a calculated column to my Excel based PowerBI DataSet?

So I still don't think there is any way to perform this use case in a simple way: Host a spreadsheet in SharePoint, sync it into a PowerBI DataSet and use it in a PowerBI report with a calculated column?

[Anonymous]

Hi @powerdell ,

Thanks for your reply. According to your description and screenshot, you are trying to connect to the dataset in Power BI Desktop. If so, you can't create any calculated column, calculated table or make any transformation in Power Query Editor. Only report level measure can be created... You can watch the following video for more details...

Report level measures with Power BI live connections

As you referred, the data source of dataset is SQL Analysis Services database. Which connection mode is using? Import or live connection? If it is import, you can refer the following link to download the pbix file if  you have the proper permission. You can open the pbix file in Power BI Desktop and modify it(create calculated column etc.) after get it.

Download a report from the Power BI service to Power BI Desktop

Lastly, for creating dataflow, you can review the following official documentation to know the supported data sources of dataflow. And it doesn't support the dataset... By the way, why you need to create a dataflow? What's the aim? Later we can check if there is any workaround can provide you.

Creating a dataflow

Best Regards

[powerdell]

Ok I do not want an Import because it needs to refresh when the data changes in the spreadsheet. So to simplify the use case again:

1. Have refreshing/live connection to a SharePoint hosted Excel file
2. Create calculated column 
3. Use in PowerBI Report

Seems incredible that this is not possible? 

I was considering Data Flows because I cannot see how to do this using PowerBI DataSet.

[Anonymous]

Hi @powerdell ,

It is impossible to create a dataflow base on the dataset, but you can refer the workaround in the following thread to do it.

Create Dataflow from Power BI Dataset

You can not build a dataflow on top of the dataset, you can only do it the other way round.

The easiest way I know to replicate the models is by coping the M script from PBI Desktop advanced editor into dataflows.

You can still reference the same tables in dataflows, you just need to make sure "Enable Load" is not on.

---

And as I referred in my previous post, if you are connecting to a dataset, the calculated column can't be created. What's the underlying data source of your dataset? It is a excel file which store in Sharepoint site or SQL Analysis Services? And what's the calculated column used for? Later we can check if there is any other method can achieve the same function...

Best Regards

[powerdell]

Thanks again...

Ok will try the hack you have provided on the DataFlow, but please forget the Dataset/Dataflow that is just me desperately trying anything to achieve the above stated use case:

• 1. Have refreshing/live connection to a (SharePoint hosted) Excel file
  2. Create calculated column (or join/merge data)
  3. Use model in PowerBI Report

It cannot be done (out of the box) it seems and that seems incredible right? How can such a basic use case not be supported?

I would be very interested to hear from the community how they would solve this use case? Do I really have to do all modelling and data work in Excel?

[Anonymous]

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:

How to combine two columns from two different table into a new table

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

[powerdell]

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.

[Anonymous]

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