Convert text format 11,11 in decimal number

[maxduff59]

Hi everybody,

I am a newbie on PowerBi, and one of the first step I am trying to do is the data cleaning in the Power Query Editor. I have columns with number in a text format such as '58.67'. I cannot convert it to decimal directly (I have an error) so I decided to change the point by a coma such as '58,67'. After that when I convert it to decimal number format, I am having 5867 instead of 58,67.

Does somebody know how I could correct my mistake ?

Thanks in advance for your time

[Syndicate_Admin]

I have the same inconvenience, text format values such as: "4,22" when converted to decimal number type converts it to "422".

[v-jianboli-msft]

Hi @maxduff59 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

Hi @maxduff59 ,

It seems to work fine in my side:

Output:

Here is the M code:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrXQMzNXitWJVjI31jMzBLMM9YwgDCM9cwswwxgirhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}})

in

#"Changed Type"

Beasides, you can try :

Then keep the column you need

Output:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrXQMTNXitWJVjI31jEzBLMMdYwgDCMdcwul2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Column1]/100),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"})
in
    #"Removed Other Columns"

Please provide me with more details about your data and your error or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[danextian]

Hi @maxduff59 

For this: I have columns with number in a text format such as '58.67'. I cannot convert it to decimal directly (I have an error) 

What is the error?