Forum Discussion
Splitting Column Assistance
Hey Guys,
So I am running into a hiccup, I know how to do this in excel but am struggling how to do it in powerBI.
So I have a column in this format: USERNAME17 or USERNAME7, so I can't set the delimiter to be at the first 1, I would prefer to set the delimiter to be the first occurance of ANY number. In excel I would use this formula: =LEFT(A2,LEN(A2)-LEN(C2)) or I would just do a simple find and replace. I am struggling with removing the numbers from the USERNAME piece though in powerBI as I've just started diving into it.
Any help is appreciated!
What about this?
Table.AddColumn(<Previous Step>, "Position to Split", each List.PositionOfAny([UsernameColumn], {"0","1","2","3","4","5","6","7","8","9"}, Occurrence.First), Int64.Type),This should return the 0-based offset of where the first # shows up in the name. Basically, it's looking in the UserNameColumn for any instance of those 10 numbers (formatted as text).
Then you can add the 2 columns using this [Position to Split] and Text.Start() and Text.End() to grab the right number of characters. You'll probably need Text.Length() as well.
3 Replies
If your column always starts with USERNAME, you can do this in Query Editor
Table preview
Here's the M code to generate these 2 columns:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg12DfJz9HU1VIrVQeKZo3CNjJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Orignal Column" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Orignal Column", type text}}), #"Add Username Prefix" = Table.AddColumn(#"Changed Type", "Username Prefix", each "USERNAME", type text), #"Add Number after USERNAME" = Table.AddColumn(#"Add Username Prefix", "Number after USERNAME", each Number.From(Text.AfterDelimiter([Orignal Column], "USERNAME", 0)), Int64.Type) in #"Add Number after USERNAME"Pay no attention to the first 2 steps, those were just needed to generate the 3 row table with sample data.
The 2 steps you need are #"Add Username Prefix" and #"Add Number after USERNAME".
Hope this helps!
Okay, that does help I see where you are going with it. This is a very dumb question but the "USERNAME" is not limited to username it would be jbsmith, knjohnson then have a number following it such as knjohnson17 or jbsmith22.
What about this?
Table.AddColumn(<Previous Step>, "Position to Split", each List.PositionOfAny([UsernameColumn], {"0","1","2","3","4","5","6","7","8","9"}, Occurrence.First), Int64.Type),This should return the 0-based offset of where the first # shows up in the name. Basically, it's looking in the UserNameColumn for any instance of those 10 numbers (formatted as text).
Then you can add the 2 columns using this [Position to Split] and Text.Start() and Text.End() to grab the right number of characters. You'll probably need Text.Length() as well.
