cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

Removing middle name from a name field column with comma delimiter

I am a newbie to Power BI and trying to work out a solution for a scenario where I have got a column with name displayed in the format Lastname, first name, middle name. I need the first name concatenated with the last name with space in between. I tried a couple of options in string extraction but couldn't find anything that handles the scenario. Anyone got any suggestions.

 

Thanks

Sample Data -
Bray, John L
Ashmore, Gavin L

Required Output
- John Bray
- Gavin Ashmore

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

If you open the query under Home > Edit Queries you can add some additional steps.

1. Replace , with nothing

2. Split the column by delimiter (space)

3. Add a column that combines First Name & " " & Last Name

4. Delete the old name fields

NameFix.jpg

Here is the code from the Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKrNRR8MrPyFPwUYrViVZyLM7IzS9K1VFwTyzLBAvGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",",","",Replacer.ReplaceText,{"Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Name", each [Column1.1] & " " & [Column1.2])
in
    #"Added Custom"

 

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, use the Column by Examples feature.  It works very well on your data.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks @Ashish_Mathur . It worked in most of the cases and noted that for every name I need to provide an example, I might need to explore a bit more on how to use that feature. Thanks for the alternative suggestion.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jdbuchanan71
Super User
Super User

If you open the query under Home > Edit Queries you can add some additional steps.

1. Replace , with nothing

2. Split the column by delimiter (space)

3. Add a column that combines First Name & " " & Last Name

4. Delete the old name fields

NameFix.jpg

Here is the code from the Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKrNRR8MrPyFPwUYrViVZyLM7IzS9K1VFwTyzLBAvGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",",","",Replacer.ReplaceText,{"Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Name", each [Column1.1] & " " & [Column1.2])
in
    #"Added Custom"

 

Anonymous
Not applicable

Thanks, heaps this worked.

 

I really appreciate it.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors