Transform a column of names to display an initial and a period using Power Query

[Divarche]

I'm trying to figure out how to transform a name column (with either a name or an initial) to only display the initial and a period. As an example 'John' would become 'J.' and the initial 'A' would become 'A.'. Can this be done with Excel Power Query? Any ideas would be appreciated.

 

[JEC]

What about John Cena?

 

[JEC]

= Table.TransformColumns(tbl,{{"test", each Text.Start(_,1)&"."}})

 

[smozgur]

One way of doing this. (Ignore the Source step as I used sample data. You would use your own source. Just make sure changing "Name" column name with yours).

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1WCM7NLMlQitWJVnLOKMosLskvyEgtUnDMS0ktKs7Pg0joKfhmJmek5uSkKnjlZ+SBxWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
  TransformColumns = Table.TransformColumns(Source, {"Name",
                        each
                            let
                                Split = Text.Split(_, " "),
                                FirstName = Text.Trim(Split{0}),
                                Initial = if Text.EndsWith(FirstName, ".") then FirstName else Text.Start(FirstName, 1) & ".",
                                FullName = Initial & " " & Text.Combine(List.RemoveFirstN(Split, 1), " ")
                            in
                                FullName
                        })
 in
    TransformColumns

The above code replaces the existing column. If you want to add a new column instead, you can use the following code instead (still same approach):

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1WCM7NLMlQitWJVnLOKMosLskvyEgtUnDMS0ktKs7Pg0joKfhmJmek5uSkKnjlZ+SBxWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
  Split = Table.AddColumn(Source, "Custom", each Text.Split([Name], " ")),
  TransformColumns = Table.TransformColumns(Split, {"Custom",
                        each
                            let
                                FirstName = Text.Trim(_{0}),
                                Initial = if Text.EndsWith(FirstName, ".") then FirstName else Text.Start(FirstName, 1) & "."
                            in
                                Initial & " " & Text.Combine(List.RemoveFirstN(_,1), " ")
                        }
                    )
 in
  TransformColumns

Result:

 

[Divarche]

Thanks for your reply Smozgur. I wasn't expecting to have to enter lots of lines of code. Isn't it possible to use the Transform data options within Power Query editor for this? Where did you get the Json binary code from?

 

[smozgur]

Thanks for your reply Smozgur. I wasn't expecting to have to enter lots of lines of code. Isn't it possible to use the Transform data options within Power Query editor for this?

Power Query also generates lots of lines of code in the background when using the user interface. However, not all functions are available through the user interface. In such cases, we often need to write the necessary M code in the Advanced Editor, as demonstrated above, to maintain clarity and understanding in our code and also be able to use more powerful functions and methods. Simply replace the Source step with your own data source, and then you can examine the code to understand its operation.

However, you can achieve a similar result by adding or removing columns using the interface. Please note, in this case, you won't have access to the Table.TransformColumns() function, which performs all the steps below at once, as shown in the initial code I provided above. If you're not interested in diving into the behind-the-scenes details that would enable you to work with more powerful functions that M language provides and if you prefer to work within the interface's limitations, here's how you can do that.

1. Right click on the Name column and select "Split Column -> By Delimiter".
   
   
2. Select Space as the delimiter and Split at Left-most delimiter - so you get the first name in the first column and the rest of the name in the second column.
   
   
   
   
   
3. Add a new custom column, name it as Initial and enter the following formula:
   The formula simply looks at the last character of the first name, if it is a dot then it means it is Initial already, otherwise it takes the first character of the name and concatenate with a dot.
   
   
   
   
   
4. Add another custom column to create the transformed Name column by using the Initial and Name.2 columns:
   
   
   
   
   
5. Select the first three columns that you used temporarily to get to the result, right click on the columns row and select Remove Columns:
   
   
   
   
   
6. You get the result in more steps and more complicated code generated in the background:
   
   

The following is the generated code. As you can see, you still need to enter some functions in the Add Column steps, like Text.EndWith() and Text.Start().
Note: I used the Table1 as the source table in this sample. It requires the column to be named as Name.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Name.1", "Name.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Initial", each if Text.EndsWith([Name.1], ".") then [Name.1] else Text.Start([Name.1], 1) & "."),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Initial] & " " & [Name.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Name.1", "Name.2", "Initial"})
in
    #"Removed Columns"

Where did you get the Json binary code from?

When you open the PQ interface, you will see the Enter Data button in the Home tab of the Ribbon (the last button at right edge). There you can enter your data manually, and it is compressed as binary text in the generated code.

 

[Divarche]

Thank you for your solutions to this problem. So it appears the M code can be used to transform and replace the existing column, but it’s not possible to replace the existing column with the user interface, the interface can only be used to create a new custom column?

 

[smozgur]

Thank you for your solutions to this problem. So it appears the M code can be used to transform and replace the existing column, but it’s not possible to replace the existing column with the user interface, the interface can only be used to create a new custom column?

If you followed the steps that I wrote and illustrated in a very detailed form in my previous post, you can see that it is entirely using the UI and it is also "replacing" the column. However, what it does is creating a new column and deleting the old one instead of doing the change on the actual column.

In the initial solution I provided by using M code directly, we can use functions and methods that don't exist on the user interface options, so I didn't have to add/remove column steps but simply used the Table.TransformColumns() function.

At the end, both does the same thing. In my opinion, learning the M code version makes you more powerful in Power Query in a short time.