Forum Discussion
Splitting Column Assistance
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.
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.