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,
I have a field column which contains a name for example "Joe Bloggs (JBloggs)"
And I have cobbled together the following to strip out the "(JBloggs)"
Result = Table.AddColumn( ChgTyp, "Names", each Text.ReplaceRange( [Names], Text.PositionOf( [Names], "("), Text.PositionOf( [Names], ")") - Text.PositionOf( [Names], "(") + 1, ""))
My problem is now extending this to work for circumstances where there are 2, 3 or more names in the same field with a similar format ideally from the same single Table.AddColumn statement.
eg. Joe Blog (JBlogg) | Sam Smith (SSmith) | Andrew Cox (Acox)
I did take a look at the List.Generate function but got bogged down in the syntax... and couldn't get it to work... the approach I was taking was exiting based on List.PositionOf but like I said I was struggling.
Any assistance gratefully received - and thanks in anticipation
Jabba
Solved! Go to Solution.
In the query below, second step inside out:
1. Names are split on "(" and ")"
2. Alternating rows are taken from the result, skipping 1 item, taking 1 item, starting with the first 1 item to take.
3. Resulting items are trimmed (leading/trailing spaces removed).
4. Result is combined with a space between each part.
let Source = #table(type table[Names = text], {{"Joe Blog (JBlogg) | Sam Smith (SSmith) | Andrew Cox (Acox)"}}), StrippedText = Table.AddColumn( Source, "StrippedNames", each Text.Combine( List.Transform( List.Alternate( Text.SplitAny( [Names], "()"), 1, 1, 1), Text.Trim), " "), type text) in StrippedText
Do you have any hard limits on how many names might be contained in a single entry? Could you use the 'split columns by delimiter' function in order to get your names into multiple columns? Then apply your removal method to each column, then finally either pivot or concatinate the names again?
Thanks Ross - yes I did try that route but I couldn't keep it dynamic - varying datasets would contain different limits - sometimes 2 sometimes 3 or indeed more - which answers your question about hard limits - the good news is that MarcelBeug seems to have nailed it - bu thanks for responding.
In the query below, second step inside out:
1. Names are split on "(" and ")"
2. Alternating rows are taken from the result, skipping 1 item, taking 1 item, starting with the first 1 item to take.
3. Resulting items are trimmed (leading/trailing spaces removed).
4. Result is combined with a space between each part.
let Source = #table(type table[Names = text], {{"Joe Blog (JBlogg) | Sam Smith (SSmith) | Andrew Cox (Acox)"}}), StrippedText = Table.AddColumn( Source, "StrippedNames", each Text.Combine( List.Transform( List.Alternate( Text.SplitAny( [Names], "()"), 1, 1, 1), Text.Trim), " "), type text) in StrippedText
Thanks MarcelBeug
Awesome use of M - and learnt loads from your code - many thanks - works a treat.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |