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 any step I can do to solve this.

 

 

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

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    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
        Icon for Super User rankSuper 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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , In Dax a New column

    = [Number] *1

     

    or try in Power Query

    Number.FromText([Number])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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