Forum Discussion
Changing multiple text values to a single text value
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
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".
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
6 Replies
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".
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
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
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
