Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

11 REPLIES 11
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.

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

 

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"

 

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

Hi, jazz;  I was wondering how this might work with decimals!  I tried your code, and it works, but it strips out the decimal (as a string).

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.