Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi Team,
I'm trying to split a column that contains text and number into two seperate columns, one containing the number and other contains the character.
Below is the sample data how it looks.. I need to extract numbers and text seperately..
Please ignore if the text character is in the middle of the string. (Ex. 170M01788)
source | Number | Text |
170M01830X | 170M01830 | X |
016 260423XXX | 016 260423 | XXX |
170M01788 | 170M01788 | |
170M02313X | 170M02313 | X |
002P113748 | 002P113748 | |
016 273150AM | 016 273150 | AM |
Number and Text columns is what im expecting.
Thanks..
Solved! Go to Solution.
@rajrajsha
You can do it two steps:
Choose Digit to Non Digit
Then choose Merge Columns under Trasnform tab after selecting 1st and 2nd columns
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ38DUwtDA2iFCK1YlWMjA0UzAyMzAxMo6IgIhAFJhbWCB4RsaGxlDlBkYBhobG5iYWCN3mxoamBo6+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [source = _t]),
#"Split Column by Character Transition" = Table.SplitColumn(Source, "source",
Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"source.1", "source.2", "source.3"}),
#"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"source.1", "source.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Source")
in
#"Merged Columns"
Result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@rajrajsha
You can do it two steps:
Choose Digit to Non Digit
Then choose Merge Columns under Trasnform tab after selecting 1st and 2nd columns
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ38DUwtDA2iFCK1YlWMjA0UzAyMzAxMo6IgIhAFJhbWCB4RsaGxlDlBkYBhobG5iYWCN3mxoamBo6+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [source = _t]),
#"Split Column by Character Transition" = Table.SplitColumn(Source, "source",
Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"source.1", "source.2", "source.3"}),
#"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"source.1", "source.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Source")
in
#"Merged Columns"
Result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ38DUwtDA2iFCK1YlWMjA0UzAyMzAxMo6IgIhAFJhbWCB4RsaGxlDlBkYBhobG5iYWCN3mxoamBo6+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [source = _t]),
#"Split Column by Character Transition" = Table.SplitColumn(Source, "source", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))),
#"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",List.RemoveLastN(Table.ColumnNames(#"Split Column by Character Transition"),1),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Number"),
#"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{List.Last(Table.ColumnNames(#"Merged Columns")), "Text"}})
in
#"Renamed Columns"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!