I am very new to power query and for the most part am able to stumble around and achieve what I need to, but in this case the option of adding column after column and then removing all of the unwanted ones just feels too ugly.
I am trying to replace several different items in one column where there are several thousand rows. Basically combining similar position titles in to a generic title:
Eg, all titles that begin with Supply Assistant to Supply Assistant, all titles that begin with NCC Rep to NCC Rep.
In excel this is simple using wildcards and a nested IF statement, but there are no wildcards in PQ. Ideally I'd like to do it without adding a new column, but the closest I can get is by adding a new column. It works for the first replace, but when I added the second one, although the first one still works, every other result is "Function".
= Table.AddColumn(#"Replaced Value1", "Title", each if Text.StartsWith([POS_TITLE], "Supply Assistant") then "Supply Assistant" else (each if Text.StartsWith([POS_TITLE], "NCC Rep") then "NCC Rep" else [POS_TITLE]))
How do I either get this to work when adding a new column above, or, better, replace multiple values based on the start of the text in the existing column?
If I can get the second one working I'll be able to modify for the remaining titles.
Thanks in advance for any help, but please, keep it simple - all of the searching I've done on this has left me baffled!