Forum Discussion

Omega's avatar
Omega
Impactful Individual
5 years ago
Solved

Split decimal number from text

Hi, 

 

I have column that have size and I'd like to split the number from the size unit. I can do that easily in Power query using split digit to non-digit. The challenge is with sizes that has decimals like 1.5ML or 2.5KG. 

 

How can I get the size number as 1.5 in a column and ML in another column in Power Query? 

 

Please help.

Need Help
Show and Tell
  • AlB's avatar
    AlB
    5 years ago

    Hi Omega 

    Place the following M code in a blank query to see the steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTwdleK1QEy9EyhLGNfHwitZ2EBYsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Size", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Size", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9", "."}, c)), {"Size.1", "Size.2"})
in
    #"Split Column by Character Transition"

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Omega 

    Place the following M code in a blank query to see the steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTwdleK1QEy9EyhLGNfHwitZ2EBYsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Size", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Size", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9", "."}, c)), {"Size.1", "Size.2"})
in
    #"Split Column by Character Transition"

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Omega's avatar
      Omega
      Impactful Individual
      5 years ago

      Thanks a lot! Worked as charm 🙂