Forum Discussion
Solved
Remove first characters that are numbers
Hello 🙂
I have a column in my table with these values:
| Type |
| NE23 |
| HJ98 |
| 47KL19 |
| DF90 |
| 32NE23 |
I want to create a way, in Power Query, that always removes the first characters of the text if they are numbers. The expected result would be something like this:
| Type |
| NE23 |
| HJ98 |
| KL19 |
| DF90 |
| NE23 |
How could I do this?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nM1MlaK1YlW8vCytAAzTMy9fQwtwUwXN0sDMMPYCKIuFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t]), #"Split Column by Character Transition" = Table.SplitColumn(Source, "Type", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Type.1", "Type.2"}), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition",each [Type.1],each if Text.Start([Type.1],1)>"9" then [Type.1] else null ,Replacer.ReplaceValue,{"Type.1"}), #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Type.1", "Type.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Type") in #"Merged Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
1 Reply
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nM1MlaK1YlW8vCytAAzTMy9fQwtwUwXN0sDMMPYCKIuFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t]), #"Split Column by Character Transition" = Table.SplitColumn(Source, "Type", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Type.1", "Type.2"}), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition",each [Type.1],each if Text.Start([Type.1],1)>"9" then [Type.1] else null ,Replacer.ReplaceValue,{"Type.1"}), #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Type.1", "Type.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Type") in #"Merged Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.