Forum Discussion

vat2do's avatar
vat2do
Icon for Helper III rankHelper III
4 years ago
Solved

How to create new column using existing columns with a condition

Hope you have a good day,

 

I need to create a new column based on existing column but to combine some cell values with text datatype into a different text value.  

fo example, i have a column name Category having 1000 cells showing different text values, i want to create new column but need to change the text of cells "ABC" and "DEF" into one text value like "XYZ".

 

Thanks in Advance

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I tried to create a sample pbix file like below, and please check whether it suits your dataset.

    I hope the below can provide some ideas on how to create a solution for your dataset.

    Please check the below picture and the attached pbix file.

     

     

    New column CC = 
SWITCH (
    TRUE (),
    CONTAINSSTRING ( Data[Category], "ABC" ), SUBSTITUTE ( Data[Category], "ABC", "XYZ" ),
    CONTAINSSTRING ( Data[Category], "DEF" ), SUBSTITUTE ( Data[Category], "DEF", "XYZ" ),
    Data[Category]
)

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I tried to create a sample pbix file like below, and please check whether it suits your dataset.

    I hope the below can provide some ideas on how to create a solution for your dataset.

    Please check the below picture and the attached pbix file.

     

     

    New column CC = 
SWITCH (
    TRUE (),
    CONTAINSSTRING ( Data[Category], "ABC" ), SUBSTITUTE ( Data[Category], "ABC", "XYZ" ),
    CONTAINSSTRING ( Data[Category], "DEF" ), SUBSTITUTE ( Data[Category], "DEF", "XYZ" ),
    Data[Category]
)