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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.