Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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".
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
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"
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
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"
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
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
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".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |