Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
leolapa
Frequent Visitor

Extract initials from names column

OK, I have the following text column as part of a 19-column, several-thousand-rows table in PQ:

oBenef.png

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

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

7 REPLIES 7
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi mahoneypat, i just tried this on my own table, but it doesn't work 😞 

I wrote:

"

Initials = Text.Combine(List.Transform(Text.Split('Sales Related Documents'[Employee Responsible], " "), each Text.Start(_,1)), "")"

It sure did work like a charm...

Thanks a bunch!

tackytechtom
Super User
Super User

Hi @leolapa ,

 

How about this:

tomfox_0-1646941222343.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

No apologies needed...

mahoneypat's solution worked out just fine.

Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors