Extract Text in Power Query

[Anonymous]

Hi all, I would like to extract number in text format to number format. I try to use extract text after delimeter "0" but when it comes to number 11 and 10..it will become blank..anyone can recommend any step I can do to solve this.

[BA_Pete · Accepted solution]

Hi @Anonymous ,

In Power Query, you can just change the data type to DECIMAL by clicking the data type button at the top of the column. It'll sort it out for you:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBUitUBUkYQyhhCmelBGeZ6JmCGIUSZoYFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    duplicateColumn = Table.DuplicateColumn(Source, "Column1", "Column2"),
    chgDecimalType = Table.TransformColumnTypes(duplicateColumn,{{"Column2", type number}})
in
    chgDecimalType

Pete

[Anonymous]

Hi @BA_Pete thenks for the solution. Yes I did convert to decimal. But I add another step to replace 6.1 to 6.10 since converting to decimal make it 6.10 to 6.1.

Thanks again for the solution.

[BA_Pete]

No worries.

The formatting of 6.1 > 6.10 would be done on the reporting side, not in Power Query, as it is a data FORMAT. Power Query only deals in data TYPES.

Pete

[amitchandak]

@Anonymous , In Dax a New column

= [Number] *1

or try in Power Query

Number.FromText([Number])

[Anonymous]

Thanks @amitchandak I didnt test the solution but it seems work!