new to power bi -

[Anonymous]

Hi guys,

I'm new to Power BI and struggling to convert data in the Query editor, which seeems to be stored as text (numbers are stored to the left of the cell) to decimal numeric format. The error message that pops up is "we can't automatically convert the column to decimal number type". I'd appreciate if you can propose any reasonably straightforward panacea to my issues.

Thanks a lot,

Filip

[v-angzheng-msft · Accepted solution]

HI, @Anonymous 

Perhaps you have encountered an error like the one below.

Powerbi cannot directly correct the data. Maybe your data value contains invisible placeholders such as spaces.

The solution is to modify the type in Power Query. Power Query has certain error correction capabilities, but If there are obviously different types of values in your column, errors may occur, such as numbers and letters at the same time, then you need to modify the data.

Click: Transform data>>Transform>>Data Type

Hope this help

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.

[PaulDBrown]

@Anonymous 

You may well have values in the field/column which cannot be converted. Try changing the sort order of the column to see if values appear which cannot be transformed

[ERD]

Hi @Anonymous ,

It would increase your chances to get a response if you can provide some examples of data.

How to Get Your Question Answered Quickly  

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

[Anonymous]

Hi @ERD ,

Hereby I provide a snapshot of the two problematic columns (capital return and annualised capital return). Sales commission also seems to be stored as text but I do not intend to utilise it. Let me know whether I should provide the Excel file itself.

[ERD]

@Anonymous ,

Please, provide .pbix / Excel or just paste the values in table here. There might be some hidden symbols that may affect yoor case. 

[Anonymous]

Problem is that once I decide to replace all "nulls" data with nothing(empty string) in the 2 columns I target swtich back to "any" format in the query editor after I have previously converted it to decimal numeric.

[Anonymous]

Sadly, I cannot find an attachment of files button and I cannot paste the numbers here as they exceed the character limit.