Get data from Power BI to Excel - unable to recognize number format

[Anonymous]

Hi!

First time here. Sorry for the bad English.

I'm having trouble when obtaing data from Power BI in order to create a pivot table in Excel. I do can access the dataset and all the fields are shown. The problem is: I can't put the field [Price] in the pivot table "Values" box (picture 1).

Apparently Excel is recognizing [Price] as text due to some number format conversion failure. The thing is, Power Query had already recognized this field type as number and it's working fine in Power BI.

Can you help me figuring out a way to keep the number format when getting data from Power BI to Excel?

PS: We use decimal comma instead of point (portuguese) and changing it isn't an option.

PS 2 (update): Even integer number or dates can't be recognized as so. Looks like all the fields come down as "text" when I get data from Power BI. If I could at least process the data with Power Pivot maybe the problem could be solved...

Thank you very much in advance!

Picture 1:

[v-angzheng-msft · Accepted solution]

Hi,  @Anonymous 

Try to create a measure (SUM('Table'[Price]))and place the measure into the Pivot Table Value field.

When your Power BI dataset connects to an external OLAP model (which is how Excel connects to Power BI), the PivotTable requires measures to be defined in the external model, since all calculations are performed on the server. 

If you take a close look at the Pivot Table Fields list, you will see that there are now 2 types of field groups.  The first type (shown as 1 below) are the measures stored in your tables in Power BI.  These “measure tables” are indicated by the Sigma symbol.  The second type (shown as 2 below) are the actual tables (and columns) from the data model.  You select the Values for your Pivot Table from the measures tables (1) and the Rows/Columns/Filters/Slicers from the actual tables (2).

refer:
Can't drag anything to the PivotTable Values area (no measures)

Power BI Analyze in Excel – What You Need to Know.

Measures in Power Pivot

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anmol93]

Hi,
I had this similar issue and I did creating measures so that I can pull that field into values. The numeric columns are't able to populate in the values field list. 
When I create measures, it does pull it into the values field list but it shows numbers same for everything.How can i fix it?

[Anonymous]

@v-angzheng-msft, thank you very much for your explanation!

[PhilipTreacy]

Hi @Anonymous 

Can you get the data directly into Excel with Power Query?  Why are you using PBI first then transferring to Excel?

Regards

Phil

[Anonymous]

Hi @PhilipTreacy , we do get the data directly into Excel with Power Query locally a couple of times a day, for different analisys and reports, which is very time consuming ant troublesome. It's already schedulled to run once a day in a server and being used in Power BI and now what we need is to make it easily avaiable and updated.