Changing multiple text values to a single text value

[Fahid]

Hello everyone. I have 389 different text values in a column. I want to keep 4 values same and change rest 385 text values to “Others”.

Using Replace or Conditional Column in query editor is not efficient in this case. I have tried to solve this with DAX Substitute condition but doesn’t work properly because of too many text substitutions.

Is there a way to keep 4 values same and redirect 385 text values to “Others”?

Thanks a lot for your help.

BR,

Fahid

[Greg_Deckler · Accepted solution]

I would look at creating a Group in DAX. You could create a group for each of your 4 values and then include an "Other" group. I think this would be the quickest and most efficient way of doing what you are looking to do. In your fields area, click the ellipses (...) and choose "New group". 

[AlB · Accepted solution]

Hi @Fahid

You can add a custom column in the query editor with the following code:

= if List.Contains({Val1,Val2, Val3, Val4},[YourCurrentColumn]) 
     then [YourCurrentColumn] 
     else "Other"

where Val1-4 are the values you want to keep

Then, if necessary, you can remove the original column and rename the new one

[v-cherch-msft]

Hi @Fahid

You may refer to below calculated column.List the 4 values in {...} with IN function.

Column =
IF ( Table4[Text] IN { "a", "c", "d", "g" }, "Single", "Others" )

Regards,

Cherie

[Fahid]

Thanks to everyone for your reply.

@AlB your below solution works perfect and marked it as solution.

= if List.Contains({"Val1","Val2", "Val3", "Val4"},[YourCurrentColumn])
     then [YourCurrentColumn]
     else "Others"

@v-cherch-msft, unfortunately, I couldn’t run your solution.

@Greg_Deckler your solution works fine but it can’t replace Blanks to “Others”. I am marking it also as a solution.

BR,

Fahid

[AlB · Accepted solution]

@Fahid

It doesn't turn all rows to others. Like I said, Val1,...Val4 are just examples and you have to substitute them for the values you want to keep (you haven't said what those values are). You cannot use "Val1"... "Val4" 

[Fahid]

Hey @AlB,

Sorry for late reply and again sorry for making silly mistake. I apologize. You are correct, and code works fine in this case. I marked it also as a solution. Thanks again for helping me out.

BR,

Fahid

[AlB · Accepted solution]

Hi @Fahid

You can add a custom column in the query editor with the following code:

= if List.Contains({Val1,Val2, Val3, Val4},[YourCurrentColumn]) 
     then [YourCurrentColumn] 
     else "Other"

where Val1-4 are the values you want to keep

Then, if necessary, you can remove the original column and rename the new one

[Greg_Deckler · Accepted solution]

I would look at creating a Group in DAX. You could create a group for each of your 4 values and then include an "Other" group. I think this would be the quickest and most efficient way of doing what you are looking to do. In your fields area, click the ellipses (...) and choose "New group".