Text column with number switches to fraction

[rishtinz]

I have a column with mix format. Some values are text and some are just numbers. When I convert the data type to text some of the numbers (not all) convert to fractions. 

e.g. a row with just 5.1 will become 5.099999999999999996 

The data comes from excel and I don't want to fix it in excel. I could convert the format right there but i'm afraid users might mess it up later on, so i'd like to fix it in Power Query.

[v-yanjiang-msft · Accepted solution]

Hi @rishtinz ,

Number.Round function works for number type columns, if the column type is any or text, the function will not recognize it.

Here's my solution.

1. Right click the column, then select Duplicate Column.

2. Change the data type of the copied column to decimal number.

3. Add a new step in advanced editor.

= Table.TransformColumns(#"Changed Type1",{"Column1 - Copy",each Number.Round(_,2)})

Result:

4. Add a custom column.

try [#"Column1 - Copy"] otherwise [Column1]

Result:

Then you can remove the first two columns.

Here's the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWMtUzsAQDMM8JTJrpmUCBUmwsAA==", 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 any}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column1 - Copy", type number}}),
    #"Round"=Table.TransformColumns(#"Changed Type1",{"Column1 - Copy",each Number.Round(_,2)}),
    #"Added Custom" = Table.AddColumn(Round, "Custom", each try [#"Column1 - Copy"] otherwise [Column1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "Column1 - Copy"})
in
    #"Removed Columns"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[ronrsnfld]

You could condense your 5 steps to one by just rewriting the Table.TransformColumn step to act directly on the existing Column 1

#"Round" = Table.TransformColumns(#"Changed Type",{"Column1", each try Number.Round(Number.From(_),1) otherwise _})

[Greg_Deckler]

@rishtinz Number.Round before converting?

[rishtinz]

I could do that but there are other rows where this column has textual values and Number.Round would give me error for those. Unless if I could detect to see if the value is number then do Number.Round.

[wdx223_Daniel]

=Table.TransformColumns(PreviousStepName,{"column name",each if _ is number then Number.Round(_,2) else _})