Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Fields pulling through to Excel incorrectly



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.


If anybody can help..


Thanks in Advance,






Yes, only explicit measures will pull through to Excel.


See here for more detail on implicit vs explicit measures:

View solution in original post

Helper I
Helper I

On a second look, it appears that the only items I can use as values are measures in the dataset.

Yes, only explicit measures will pull through to Excel.


See here for more detail on implicit vs explicit measures:

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.

It doesn't seem like it.


  • Excel PivotTables don't support drag-and-drop aggregation of numeric fields. Your dataset in Power BI must have pre-defined measures. Read about creating measures.

From these documentation sources:


Helpful resources

September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Kudoed Authors