Splitting Column Assistance

[EricC]

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!

[Anonymous]

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!

[EricC]

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.

[Anonymous · Accepted solution]

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.