cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ovetteabejuela
Impactful Individual
Impactful Individual

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
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Hi @v-shex-msft, great solution! 🙂

 

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? 🙂

 

Above is the problem and desired outcome as bellowAbove is the problem and desired outcome as bellow

Thanks a lot in advance and cheers, 

Jimmy

Jaderson Almeida
Business Coordinator

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

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"

Thnaks a lot @bwashamSmiley Happy

Jaderson Almeida
Business Coordinator

Hello,

 

a simple one step answer to this question:

 

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

 

Anonymous
Not applicable

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

@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".

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors