Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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? 🙂
Thanks a lot in advance and cheers,
Jimmy
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"
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).
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.
@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".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |