cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBIWhat
Helper I
Helper I

Fields pulling through to Excel incorrectly

Hi,

 

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,

 

O

 

 

1 ACCEPTED SOLUTION

Yes, only explicit measures will pull through to Excel.

 

See here for more detail on implicit vs explicit measures:
https://radacad.com/explicit-vs-implicit-dax-measures-in-power-bi

View solution in original post

4 REPLIES 4
PowerBIWhat
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:
https://radacad.com/explicit-vs-implicit-dax-measures-in-power-bi

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:

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-connect-power-bi-datasets-excel

https://support.microsoft.com/en-us/office/create-a-pivottable-from-power-bi-datasets-31444a04-9c38-...

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors