Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Extract Text in Power Query

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...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
BA_Pete
Super User
4 years ago

 

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