cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ovetteabejuela Established Member
Established Member

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: PowerQuery | Extract Numbers from A string (eg ABCD1234)

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))))

 

1.PNG

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

8 REPLIES 8
Community Support Team
Community Support Team

Re: PowerQuery | Extract Numbers from A string (eg ABCD1234)

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))))

 

1.PNG

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

ovetteabejuela Established Member
Established Member

Re: PowerQuery | Extract Numbers from A string (eg ABCD1234)

@v-shex-msft thank you very much for the solution, I really could have not thought of that. I'll read more about it "Transform".

Nahuatl_C137 Regular Visitor
Regular Visitor

Re: PowerQuery | Extract Numbers from A string (eg ABCD1234)

Thank you, this worked. Do you know how I can make this into a function? The code below has an error message: Expression.Error: The name '_' wasn't recognized. Make sure it's spelled correctly.

 

image.png

bajimmy1983 Member
Member

Re: PowerQuery | Extract Numbers from A string (eg ABCD1234)

Hi @v-shex-msft, great solution! Smiley Happy

 

Taking this post as an opportunity, could you please try to help with two extra details maybe complementing your solution? 

 

1- Is there any way to separate number from text (vise-versa), but keep both in different columns?

 

2- A different approach as follow attached. Is it possible or this is ask too much? Smiley Happy

 

Example.PNGAbove is the problem and desired outcome as bellow

Thanks a lot in advance and cheers, 

Jimmy

Jaderson Almeida
Business Coordinator
ovetteabejuela Established Member
Established Member

Re: PowerQuery | Extract Numbers from A string (eg ABCD1234)

I'm interested to see somebody solving this. This is a tough one.

bwasham Frequent Visitor
Frequent Visitor

Re: PowerQuery | Extract Numbers from A string (eg ABCD1234)

Using Transpose and then Split column Space Right most delimiter  in M Is manually intensive but will do what you want.  I also trimmed the extra spaces

 

Heres my code

 

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column3", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column3.1", "Column3.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column3.1", type text}, {"Column3.2", type text}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Column4", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column4.1", "Column4.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column4.1", type text}, {"Column4.2", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type3",{{"Column1.1", Text.Trim, type text}, {"Column1.2", Text.Trim, type text}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Trimmed Text",{{"Column2", type text}, {"Column5", type text}}),
#"Trimmed Text1" = Table.TransformColumns(#"Changed Type4",{{"Column2", Text.Trim, type text}, {"Column3.1", Text.Trim, type text}, {"Column3.2", Text.Trim, type text}, {"Column4.1", Text.Trim, type text}, {"Column4.2", Text.Trim, type text}, {"Column5", Text.Trim, type text}}),
#"Transposed Table1" = Table.Transpose(#"Trimmed Text1")
in
#"Transposed Table1"

bajimmy1983 Member
Member

Re: PowerQuery | Extract Numbers from A string (eg ABCD1234)

Thnaks a lot @bwashamSmiley Happy

Jaderson Almeida
Business Coordinator
jazz Frequent Visitor
Frequent Visitor

Re: PowerQuery | Extract Numbers from A string (eg ABCD1234)

Hello,

 

a simple one step answer to this question:

 

OneStep = Table.AddColumn(Source, "MyNumberColumn", each Text.Select([MyStringColumn], {"0".."9"}))

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 112 members 1,448 guests
Please welcome our newest community members: