Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
In Excel
Solved! Go to Solution.
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
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
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:
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:
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.