Forum Discussion
PowerBI DataSet from Excel (Live Connection) data model
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?
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.
Best Regards
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.
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 DatasetYou 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