Replace Column Values

[dhodgson]

Hi, 

I need to be able to replace all the values in the 'Trade Engagement' column to look like the below when 'EmployeeID' is NOT "1.Subcontractor" or blank. 

EmployeeID	Trade Engagement
CARL	Company
1.Subcontractor	1.Subcontractor
ADAM	Company
JAMES	Company

Thanks in advance

[FreemanZ]

hi @dhodgson 

not very clear. do you mean something like:

Column =

IF(

    [EmployeeID] IN {"1.Subcontractor", BLANK()},

    [Trade Engagement],

   "Company"

)

[dhodgson]

That forumla isn't wokring. All I did was duplicate the EmployeeID column and renamed to 'trade engagement'. I then need to replace all values to 'Company' if they are not 1.subcontractor or a blank field. I tried using the below, however it replaces all text with "Company"; 

= Table.ReplaceValue(#"Renamed Columns1", each [Trade Engagement], each if [employeeid] <> "1.SUBCONTRACTOR" then "Company" else [Trade Engagement], Replacer.ReplaceText,{"Trade Engagement"})

[FreemanZ · Accepted solution]

btw, the first reply is for DAX, it works like:

[dhodgson]

Apoloigies, was definitley doing this one wrong! This helps. Appreciate it. 

[FreemanZ]

hi @dhodgson 

try to add a custom column like:

= Table.AddColumn(#"Changed Type", "Custom", each if [EmployeeID] ="1.Subcontractor" or [EmployeeID]=" "
then [EmployeeID]
else "Company")

it worked like: