Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
9 years ago
Solved

PowerQuery | Extract Numbers from A string (eg ABCD1234)

Hi Community,   In this example, FirstName12345. How can I extract 12345 from it using M?   Assume that the number length varies (eg abc123, abc1234567, abc1234, etc...)     [Edit] Spelling  ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    HI ovetteabejuela,

     

    You can refer to below formula to if it suitable for your requirement.

     

    Logic: split text to character list, select the number part and merge them to text.

     

    Functions comment.

    Text.ToList: split text to character list.
    Values.Is: check value type.
    List.Transform: transform list from original list.
    List.RemoveNulls: remove replaced null value.
    Text.Combine: merge character list to text.

     

    Formula:

    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null))))

     

     

    Full query:

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxxx\Desktop\test.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet3_Sheet,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null))))
in
    #"Added Custom"

     

     

    Regards,

    Xiaoxin Sheng

alice2k's avatar
alice2k
New Member
3 years ago

Hi, Jazz; disregard, I found it in Omega's community comment, 

https://community.powerbi.com/t5/Desktop/Split-decimal-number-from-text/m-p/1585056#M643513

 

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"

 

alice2k's avatar
alice2k
New Member
3 years ago

correction... Super User's comment to Omega.