Forum Discussion
Extract initials from names column
OK, I have the following text column as part of a 19-column, several-thousand-rows table in PQ:
I need to extract the initials of each name from the above column, so this added column would be something like this:
BENEFICIÁRIOS Initials |
| ITDM |
| EDS |
| VCEDSV |
| OOBL |
| JVDL |
| PHDPS |
| MVDSS |
| CLB |
| LMDCS |
| MGDS |
| and so on... |
How can I accomplish that?
Any of the extract options won't help me with that and I've scanned every text M code in the book, as well as converting each name to a list of names to see if some list M code could come to rescue, but to no avail.
I suppose a combination of M codes should do trick, I just don't know how as I'm fairly new with this...
Thanks in advance for any help!
Leonardo
Please try this expression in your custom column instead. It will work for any # of names. Replace [Names] with your actual column name.
= Text.Combine(List.Transform(Text.Split([Names], " "), each Text.Start(_,1)), "")
Pat
7 Replies
Please try this expression in your custom column instead. It will work for any # of names. Replace [Names] with your actual column name.
= Text.Combine(List.Transform(Text.Split([Names], " "), each Text.Start(_,1)), "")
Pat
Hi leolapa ,
How about this:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnPVXDLr1CK1QGyE5OzKxVCUpMzFIDCYKGA/PLUIgUnTwXPYgX3otTEEnSFLvklCk45+elKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]), #"Added Custom1" = Table.AddColumn(Source, "Custom", each [Names]), #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Names.1", "Names.2", "Names.3", "Names.4"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Names.1", type text}, {"Names.2", type text}, {"Names.3", type text}, {"Names.4", type text}}), #"Extracted First Characters" = Table.TransformColumns(#"Changed Type1", {{"Names.1", each Text.Start(_, 1), type text}, {"Names.2", each Text.Start(_, 1), type text}, {"Names.3", each Text.Start(_, 1), type text}, {"Names.4", each Text.Start(_, 1), type text}}), #"Replaced Value" = Table.ReplaceValue(#"Extracted First Characters",null,"",Replacer.ReplaceValue,{"Names.1", "Names.2", "Names.3", "Names.4"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each [Names.1] & [Names.2] & [Names.3] & [Names.4]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Names.1", "Names.2", "Names.3", "Names.4"}) in #"Removed Columns"Let me know if this helps or if you have any questions 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Thanks for the quick reply Tom.
Your approach sure works, the only thing is that since it doesn't dynamically deals with the amount of words for each row I'm afraid it doesn't help me 100%.
Your suggestion for instance takes care of names up to 4 words. My table contains upwards of 80k names and some have up to 8 words.
Of course I could tweak your formula to make it cover names up to 8 words, but if in the future a new name comes up with more than that then its initials would be incomplete.
I don't even know whether it's possible to deal with that kind of request dynamically. I searched for some approach in Excel so it could maybe enlighten me on PQ, but all proposed solutions are like yours: non-dynamic.
Leonardo
Hi leolapa ,
You are absolutely right. My solution was not scalable. I apologise 🙂 Does mahoneypat's solution work for you?
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
