Power Pivot always converts a row in Text format and ruins all Pivot Tables and Carts

Question

I hava an Excel book with data of sales from 6 years; over 13,000 pieces of data. Instead of making an huge worksheet with lots of repeated data, I decided to make a few tables and connect them in PowerPivot.

The problem is that everytime I do a search, make an update or filter anything, the column for amount in the PowerPivot Data Model switches from "currency" to "text" and aIl pivot tables and charts get lost. ¿Is this normal? Is there anything I can do to lock the data type in the "amount" column?

Juan

Answer 1

Hi @uanUno UnoCe

"If a column mixes numbers and text, Excel assigns the text data type." For more, please refer to https://support.microsoft.com/en-us/office/set-the-data-type-of-a-column-in-power-pivot-e62c582e-824f-439a-8437-680534100e11#:~:text=Understanding%20the%20Current%20Data%20Type

The link explains the cause of data type errors and how you can correct them:

• You can re-import the data. To do this, open the existing connection to the data source and re-import the column. Depending on the data source type, you might be able to apply a filter during import to remove problem values. Filtering during import requires that you import using the Power Pivot add-in.
• You can create a DAX formula in a calculated column to create a new value of the desired data type. For example, CURRENCY function can be used to convert text to a number. If conversion succeeds the number will be converted to currency, otherwise an error is returned.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---