Hi Mynda,
how do i create a new column in a Power Query appended table and add a formula that only picks out the surname from the full name in an existing column? The formula I would like to use is: =IF($C2="-","-",LEFT($C2,SEARCH(" ",$C2)-1)).
Thanks,
Hans
Hi Hans,
You don't really need formulas, forget about old excel habits and think in Power Query. You have many ways to do that, without even writing a single formula:
One option is to split the Name column afterthe space delimiter, only on the leftmost occurence of the delimiter, you can delete the second column that will be created.
Another option is to go to Add Column tab, Text section, Extract: from this dropdown select Text Before Delimiter, and type a space as a delimiter. This is the best choice, in my opinion. You will notice that PQ will add this formula after this operation: =Text.BeforeDelimiter([Name], " ", 0), but as I mentioned, all you have to keep in mind are the commands from ribbon.
And of course, you can write a formula the hard way:
=try Text.Start([Name],Text.PositionOf(Text.From([Name])," ")) otherwise [Name]
thanks Catalin - most appreciated!
Cheers,
Hans
