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. 

 

EmployeeIDTrade Engagement
CARLCompany
1.Subcontractor1.Subcontractor
ADAMCompany
  
JAMESCompany

 

Thanks in advance

  • FreemanZ's avatar
    FreemanZ
    3 years ago

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

5 Replies

  • 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: