Remove characters from both beginning and end of string

[Anonymous]

Hi all,

So I have a username column in my data that has the database code fixed to the start, plus another code fixed to the end, like so:

BDI.DTHOMAS_2

BDI.CSMITH_2

BDI.TKANE_18

Is there a way to remove both the first four characters and those after the underscore at the same time?

Thanks,

Dan

[Anonymous]

Thanks all for your help, unfortunately I've since realised that the table I'm working on is actually protected from query edits, so I can't use any of your methods. It'll have to be messy until I get admin access!

But thank you anyway!

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

If you couldn't access into edit queries, you could use this formula to add a new calculate column instead of it:

Column = 
RIGHT (
    LEFT (
        'Table'[NAME],
        SEARCH ( "_", 'Table'[NAME], 1, LEN ( 'Table'[NAME] ) - 1 ) - 1
    ),
    LEN (
        LEFT (
            'Table'[NAME],
            SEARCH ( "_", 'Table'[NAME], 1, LEN ( 'Table'[NAME] ) - 1 ) - 1
        )
    ) - 4
)

and if you have the access to edit queries, you could use above methods in edit queries.

Result:

Best Regards,

Lin

[Gordonlilj]

Hi,

You could try using the Text.BetweenDelimiters function by creating a custom column using the code below

Text.BetweenDelimiters([Column],".","_")

[Anonymous]

hi @Anonymous ,

1. see this is my data.

2. click column and go Add column bar and then click on the Column From Examples Button.

3. after that it'll showing like this.

4. see, i don't want to First 3 latters and Last 2 latter.

   So, you can write manually from 4th latter to 3rd latter from right side.

5.    like wise write 2 or 3  Names

5. then click Ok after that see your result

regards,

Naveen

[Anonymous]

hi @Anonymous ,

PowerBI have two DAX functions are there 

 1. Left(Column Name)

 2. Right(Column Name)

Ex: you have data in single column then write Dax like,

   --> Left(ColumnName,4)&Right(ColumnName,2)

regards,

Naveen

[Anonymous]

Hi Naveen,

Thanks for your reply - I know those functions, but that would bring me BDI._2, whereas that's what I want to remove.

Also, I edited my post as there is sometimes more than one character after the underscore...

Thanks,

dna

[Ronald123]

@Anonymous ,

Try this is in Power Query, paste this code in a blank query to check.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnLx1HMJ8fD3dQyON1KK1YGIOAf7eoZ4IAmEeDv6ucYbWijFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [NAME = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "NAME", Splitter.SplitTextByPositions({0, 4}, false), {"NAME.1", "NAME.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"NAME.1", type text}, {"NAME.2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "NAME.2", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"NAME.2.1", "NAME.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"NAME.2.1", type text}, {"NAME.2.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"NAME.1", "NAME.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NAME.2.1", "NAME"}})
in
    #"Renamed Columns"

Greetz,

Ronald