Number column imported (Excel) changed to type ="all", unable to change to any other "number" type

[GeorgeFP]

G,day - thanks for reading.

After importing a column from Excel containing numbers (€ in thousands, no decimals), PBI changed the type to "all." Refer to the image below showing the various errors and results for each of the attempted type changes I made. What can I do to show that the type is € "money?"

Thanks in advance.

[GeorgeFP]

Brilliant thank you, let me test that and Ill revert in +-5hours
Regards George

[v-mdharahman]

Hi @GeorgeFP,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are facing issue with automatic data type selection selection for your excel data columns. As @DataNinja777 already responded to your query, please go through his response and check if it solves your issue.

I would also take a moment to thank @DataNinja777, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @GeorgeFP,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[GeorgeFP]

Thank you. Yes, the solution works, and I accepted it.

Regards George

[DataNinja777 · Accepted solution]

Hi @GeorgeFP ,

The problem you're running into is caused by the fact that your Excel column contains values that are not consistently clean numbers—likely due to characters like the euro symbol (€), commas, or even spaces that make Power BI throw a fit when you try to change the column type to a numeric one. When Power BI detects this inconsistency, it assigns the data type as “Any” (or “All” in the Power Query UI), and any attempt to coerce it into a decimal or whole number results in errors.

To fix this, go into Power Query and apply a transformation that strips out any non-numeric characters before converting the column to a number. Here’s a way to do that using M code. This version replaces the euro symbol and any commas, then tries to convert to a number. If the conversion fails, it will return null to prevent errors:

= Table.TransformColumns(Source, {
    {"YourColumnName", each try Number.FromText(Text.Replace(Text.Replace(Text.From(_), "€", ""), ",", "")) otherwise null, type number}
})

If your column name is, say, OutboundNumber, replace YourColumnName with OutboundNumber. This will clean your values and allow you to safely change the column type to a numeric one, such as Fixed Decimal, Decimal Number, or Whole Number depending on your use case. After applying this transformation, you can then format the field as currency (Euro) in the Power BI report view by selecting the field, going to the Modeling tab, and choosing Currency with the € symbol.

Let me know if you'd like to preserve decimal places or if your numbers are actually in thousands and need to be adjusted down (e.g., multiply by 1,000).

Best regards,