cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Welsh_Will
Helper II
Helper II

Remove characters from both beginning and end of string

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

1 ACCEPTED SOLUTION

hi, @Welsh_Will 

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:

5.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Welsh_Will
Helper II
Helper II

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!

hi, @Welsh_Will 

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:

5.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Gordonlilj
Solution Sage
Solution Sage

Hi,

 

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

 

Text.BetweenDelimiters([Column],".","_")
Anonymous
Not applicable

hi @Welsh_Will ,

 

1. see this is my data.

 

Capture.JPG

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

Capture1.JPG

3. after that it'll showing like this.

Capture2.JPG

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.

 

Capture3.JPG

 

5.    like wise write 2 or 3  Names

 

Capture5.JPG

 

5. then click Ok after that see your result

 

Capture6.JPG

 

regards,

Naveen

Anonymous
Not applicable

hi @Welsh_Will ,

 

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

  

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

 

@Welsh_Will ,

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors