Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
Solved! Go to Solution.
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
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"
Hi,
In the Query Editor, use the Column by Examples feature. It works very well on your data.
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.
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
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"
Thanks, heaps this worked.
I really appreciate it.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.