Hi,
Somebody send me a file and I could not figure out if they were last name, first name, and middle name or they were actually two person names.
I thought their names are so weird. To make sense I thought that I should add at least 'And' or '&' between these 2 people. It looks it is going to be easy but I have not been able to achieve the exact result.
It has only 7% data and 93% blank. When I brought to Power Query, 70% showed me null and 23% showed me nothing. I could not figure out any solution for that 23%. I tried to replace the value of space with null but it does not like null.
I want to add 'And' or '&' before the last name from the right side. So that I know that these are 2 people. 🙂
How do I approach? I really need help. Thanks
Hi PB,
First split the column based on the last space delimiter, then merge it back together with the ampersand. See attached.
Mynda
Hello Mynda,
Thank you so much! The problem is where there are 93% null and empty. I can add '&' but then it shows & in other 93% of records
&
&
&blank
&blank
That is what is causing the problem. Any advice about this?
Thanks
PB
Please look at the screenshot. Thanks
Hi PB,
It would have saved us both time if your original example file illustrated the problem.
You can just use a custom column to ignore blanks or nulls (assuming there is data in other columns that you want to keep, otherwise I'd just remove the blank and null rows before splitting.
After the Split Column step you can add a Custom Column with this formula:
= if [Name.2] = null then null else [Name.1] &" & " &[Name.2]
Mynda
Hello Mynda,
Thank you! Yes, my mistake I thought that since I wrote about nulls and empty and blanks so it was clear but maybe not. Now it worked. Thanks
BTW, I was able to do it another way also. Try it with Column From Examples. There are some tricks when using Column From Examples but if you know what is the result, your brain goes that way and changes the steps. This was tricky but I was able to do it. Please try it and let me know if you were able to do it with Column From Examples. I am curious to know since I want to know that I was able to trick Column From Examples because I was so desperate or it was just easy. It took me many trials.
Thanks,
PB
Hi PB,
Glad it worked now.
I had to give Column by Examples 5 examples before it realised the pattern, but you can also use that technique if you prefer. It makes no difference because the underlying process is still the same i.e. you can see in the formula it's splitting the text by delimiter and then joining it back together.
Mynda
