Forum Discussion

dhodgson's avatar
dhodgson
Frequent Visitor
3 years ago
Solved

Replace Column Values

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 ...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

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

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi dhodgson 

not very clear. do you mean something like:

Column =
IF(
    [EmployeeID] IN {"1.Subcontractor", BLANK()},
    [Trade Engagement],
   "Company"
)
dhodgson's avatar
dhodgson
Frequent Visitor
3 years ago

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's avatar
    FreemanZ
    Super User
    3 years ago

    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:

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

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

    • dhodgson's avatar
      dhodgson
      Frequent Visitor
      3 years ago

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