I'm experimenting with the Get Data from Power BI datasets functionality in excel. I can connect fine to my power bi dataset and I am then given the ability to create a pivot table. So far so good!
The issue I'm having is that some fields are not available to be selected as values, only as rows or columns. In some cases, the column is formatted as a number in the dataset but when I'm trying to use it in the pivot table I can only use it as a row or column. Not as a value so I can't take the average or sum of the column as an example. In fact, I can't even display it.
An example is a column Sales Value formatted in Power Bi as £,000 but i can only use it as row or a column. This makes me think it is pulling through to excel as text. I can't seem to find a way to change the column in Excel.
It also means I don't have the ability to pull the Sales id column into the values of the pivot table and perform a count to find the number of sales.
I've only just started playing with Excel so maybe there is a secret to this.
Thanks, that is v helpful. Is there a way in excel to create explicit measures? Or do I have to create the explicit measures in PBI before connecting to the dataset in Excel. It feels a bit messy creating a measure for something which doesn't need a measure in PBI.