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!
I have a column in a Power BI query with names. In case of absence of a person, the system adds (On Leave) to the name of the employee. So, if John Doe is absent for 2 months, the report shows John Doe (On Leave).
Also, in some cases, the system provides an output where the name of the person is added in a different writing. Particularly, in Asian countries, the system adds the name of the person in the original writing.
In both case, I'd like to have everything between brackets (including the brackets) removed. Is there a solution to this?
Thanks so much in advance for your support!
Solved! Go to Solution.
Hi @Nipius ,
I hop[e you have access to edit queries.
if so these are the steps:
1- Data looks like this
2- right click and hit on split column , you will get output like this. Hit ok
3. remove the column which was created after splitting.
4. Output looks like this
Let me know if this works.
Thanks,
Tejaswi
There is an even simpler solution.
You can create a new function called fun_ReplaceTextBetweenDelimiters, and in it add this code 👇
let fun_ReplaceTextBetweenDelimiters = (Text as text, StartDelimiter as text, EndDelimiter as text, optional ReplaceDelimiters as nullable logical, optional NewText as nullable text, optional TrimResult as nullable logical, optional FixDoubleSpaces as nullable logical) as text => let // Add Default Parameters Default_ReplaceDelimiters = if ReplaceDelimiters is null then true else ReplaceDelimiters, Default_NewText = if NewText is null then "" else NewText, Default_TrimResult = if TrimResult is null then true else TrimResult, Default_FixDoubleSpaces = if FixDoubleSpaces is null then true else FixDoubleSpaces, //Do work TextBetweenDelimiters = Text.BetweenDelimiters(Text, StartDelimiter, EndDelimiter), TextToReplace = if Default_ReplaceDelimiters then Text.Combine({StartDelimiter,TextBetweenDelimiters,EndDelimiter}) else TextBetweenDelimiters, ReplacedText = Text.Replace(Text, TextToReplace, Default_NewText), //Clean Result TrimmedText = if Default_TrimResult then Text.Trim(ReplacedText) else ReplacedText, FixedSpaces = if Default_FixDoubleSpaces then Text.Replace(TrimmedText, " ", " ") else TrimmedText in FixedSpaces in fun_ReplaceTextBetweenDelimiters
Then, we can test it like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtAw1FTSAbGUYnWilVzd3BU0jEAiQBZYJCIyCqhGRwEsCOQoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TestData = _t, TargetData = _t]), ChangeType = Table.TransformColumnTypes(Source,{{"TestData", type text}, {"TargetData", type text}}), RunFunction = Table.AddColumn(ChangeType, "NewText", each fun_ReplaceTextBetweenDelimiters([TestData], "(", ")", true), type text), TestResult = Table.AddColumn(RunFunction, "Test", each [TargetData]=[NewText], type logical) in TestResult
Input:
TestData | TargetData |
ABC (1) | ABC |
EFG (2) | EFG |
XYZ (1, 2) | XYZ |
Output:
TestData | TargetData | NewText | Test |
ABC (1) | ABC | ABC | TRUE |
EFG (2) | EFG | EFG | TRUE |
XYZ (1, 2) | XYZ | XYZ | TRUE |
Hi @Nipius ,
A little confuse with your requirments.
Do you want the output to look like this? or do you want to remove (on leave) in your report?
@Anonymous Thanks for the response!
So, originally the data looks like this "Name (On Leave)" or "Name (Name in different writing)". I want everything to look like "Name". So for all the records where there is data between brackets in the records, I'd like to remove this part.
Hi @Nipius ,
I hop[e you have access to edit queries.
if so these are the steps:
1- Data looks like this
2- right click and hit on split column , you will get output like this. Hit ok
3. remove the column which was created after splitting.
4. Output looks like this
Let me know if this works.
Thanks,
Tejaswi
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |