Power BI – Initials from a Name (First Letters from String of Text)

BY: David Rohlfs

 

Introduction

I recently ran across a problem where I needed to have the initials of someone’s name and all that I had was a column with their full name. Because of this I wanted to write a blog about how to get the first letter from a string of text.

This blog is going to be using several pre-covered blog topics. If you find that there is a process that you don’t know yet, I will include the links to EPM Strategy blogs below.

In this blog we are going to go over when you may want to use the process described and a walkthrough demonstrating the process.

When to use it

The first thing that you should know before starting this process is that there isn’t a very smooth and always working system for this process. So, if you do need to do this, I would only recommend that you do it with text strings that are no more than four words. This means that your data needs to be very standardized, and it could still have some errors if there is an outlier text string or typo. This process does work for text strings longer than four words, but it means that there is a lot more work on the front end to set everything up.

Walkthrough

In this walkthrough we will go through a method of getting initials from a full name in the Power Query. There are other ways to accomplish the same goal, but using Power Query is the cleanest and will work the most often.

One thing to note is that we will only be working with names that have a first and last name. If you have middle names that you want or a prefix/suffix, then you may need to add steps accordingly.

To start this method, you will need to locate your full name column and have it selected. We are going to do a split on this column by delimiter. The delimiter will be a space in this case. If you want to keep the original column, I suggest making a duplicate of the full name column because you will not keep the original column if you do a split column.

 

Now we are going to do two extract first characters from the split columns. We just want the first characters so your columns will look like this:

 

Now we can combine these columns back together. To do this we are going to click on the Add Column ribbon and do a custom column. In here we will be using the Text.Combine equation and not be adding any delimiter. Here is a look at what the equation will be:

 

Once you finish, you should get a new column that has the initials from the first and last name.

 

Conclusion

This is an uncommon problem that can be difficult to figure out if you don’t know what the steps are. But it is also a problem that is great to learn from because it uses a few steps that require knowledge in Power Query. If there are concepts in the walkthrough that you didn’t understand, we have covered them in previous EPM Strategy blogs, so I suggest reading through the links below.

 

Links Related to this Article:

Power BI – Extract Function : :: Welcome To EPM Strategy ::

Power BI – Split Column : :: Welcome To EPM Strategy ::

Power BI – Adding A Custom Column : :: Welcome To EPM Strategy ::

Power BI – Power Query M Code Basics : :: Welcome To EPM Strategy ::

https://epmstrategy.com/column-from-examples/

Comments