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,
I am trying to group some of the data with a new column identifier and i have a lot of identifier. I want to use something like this:-
new column = if([column1] like "B%", "Ball", if([column1] like "A%", "Apple","Others"))
i have a lot of data that its impossible to type all data in the if statement.
Please help!!
Regards,
Anand Prakash
Solved! Go to Solution.
As KGrice said, you can create a new column by using the SEARCH function like below. BTW, the search function is case insensitive.
Column = IF ( SEARCH ( "B*", Table1[column1],, 0 ) = 0, IF ( SEARCH ( "A*", Table1[column1],, 0 ) = 0, "Others", "Apple" ), "Ball" )
Best Regards,
Herbert
As KGrice said, you can create a new column by using the SEARCH function like below. BTW, the search function is case insensitive.
Column = IF ( SEARCH ( "B*", Table1[column1],, 0 ) = 0, IF ( SEARCH ( "A*", Table1[column1],, 0 ) = 0, "Others", "Apple" ), "Ball" )
Best Regards,
Herbert
If the number of characters proceeding your % is variable, this task is actually not so easy (imagine how you would like to deal with "air" and "chair" for example). With fixed number of characters, you could use Text.Start("YourText", NumberOfCharacters) and then perform a lookup to your lookup-table.
But if it's variable, you could use Text.StartsWith to check if the text starts with any given string from your lookup-table. You need to use a function in order to iterate through the lookup-table and return the lookup-value if true.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @anandprakashnm. Have you tried the SEARCH function in DAX? That allows you to use wildcards. You might also find this post from the community helpful.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |