Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
TeofiloTamele
Regular Visitor

Power BI Export to excel - Keep data type

Hi all, Please Need help.

When i export data from PowerBI Service using Export>>Analyse in Excel option creates a pivot table, the issue i am facing is that some columns which stores numbers goes as text and i am not able to use in the value field of the Pivot table..

The same fileds i am using in powerBI to sum or calculate average in PowerBI are not accepting in Excel pivot table.

In PowerBI

TeofiloTamele_0-1713970137825.png

In Excel

TeofiloTamele_1-1713970206071.png

 

1 ACCEPTED SOLUTION
TeofiloTamele
Regular Visitor

Hi all,

 

I found a work around to this,

 

I created a measure to output the quantities of that column and i am using it for analysis.

Thanks

View solution in original post

5 REPLIES 5
TeofiloTamele
Regular Visitor

Hi all,

 

I found a work around to this,

 

I created a measure to output the quantities of that column and i am using it for analysis.

Thanks

v-yohua-msft
Community Support
Community Support

Hi, @TeofiloTamele 

Use the Power BI Excel add-in: If you're not already using the Power BI Excel add-in, consider using the Power BI Excel add-in for a more integrated experience. This add-in allows for a more seamless integration between Power BI datasets and Excel, potentially alleviating data type conversion issues. For more information about this add-in, visit: 

Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

 

Also using the excel add in as explained in the link you shared but getting the same results. Thanks for trying to help. Let me try to bring more context to it.

The columns is a  decimal number as per below pic:


 Updating Media

Is used in visuals to provide total sales:

Using Excel ading or exporting from PowerBI gives same results

It only accept to be in rows not in values as expected:

 

 

TeofiloTamele_3-1714024197015.png

TeofiloTamele_2-1714024118864.png

TeofiloTamele_1-1714023920141.png

johnbasha33
Solution Sage
Solution Sage

@TeofiloTamele 

It seems like the issue you're encountering is related to the data type formatting when exporting data from Power BI Service to Excel. Here are a few steps you can take to resolve this issue:

1. **Check Data Types in Power BI:** First, ensure that the data types for the columns in your Power BI report are set correctly. If the columns storing numbers are formatted as text in Power BI, they might export as text to Excel.

2. **Format Columns in Power BI:** In Power BI Desktop, select the columns that are storing numbers but are formatted as text, and change their data type to "Decimal Number" or "Whole Number" as appropriate. You can do this by selecting the column, going to the Modeling tab, and choosing the desired data type from the "Data Type" dropdown menu.

3. **Refresh the Data Model:** After updating the data types in Power BI Desktop, refresh the data model to apply the changes. This ensures that the correct data types are used when exporting the data to Excel.

4. **Export to Excel:** Once the data types are corrected in Power BI Desktop, try exporting the data to Excel again using the "Export > Analyze in Excel" option. Check if the columns are now exported as numeric values instead of text.

5. **Format Cells in Excel:** If the columns still appear as text in Excel after exporting, you can manually format the cells in Excel to convert them to numeric values. Select the cells containing the numeric data, right-click, and choose "Format Cells." Then, choose the appropriate numeric format (e.g., Number, Currency) from the Number tab in the Format Cells dialog box.

By following these steps, you should be able to ensure that the numeric columns in your Power BI report are exported correctly to Excel and can be used in the value field of a PivotTable. If the issue persists, double-check the data types and formatting settings in both Power BI Desktop and Excel to identify any discrepancies.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

The columns in PowerBI are formated correctly as decimal numbers. The same columns i use to calculate total sales in PowerBI and as per my screen shot it shows the Sum symbol in PowerBI. Not fixed yet but thanks.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors