Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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, @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
Hi,
You could try using the Text.BetweenDelimiters function by creating a custom column using the code below
Text.BetweenDelimiters([Column],".","_")
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
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
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |