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.
Hi all!
I'm working with badge numbers, and some of our badges start with either one letter, two letters, or none at all.
Joe Shmo | ZZ1234125 |
Derrick Dieter | Z23412344 |
Paul Piper | 124244444 |
Aaron Iron | 124321441 |
Bowlsey Bollsly | 564234123 |
Is there any way that I can create something that recognizes the first few characters as letters and, if there aren't none, to leave the cell alone?
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcwxC8JADIbhvxIyd0kudbd00ang1uOGIgEPoyfXivTf26bf8C4PfDHitSjcHq+CDY4jcRDiFlMTsdda8/0JfdZF686OQcR5mL4GQ/44EQvLPqfzVMsbLlsOCkwi5NSVn826QlfMZls3b09y/GJKfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Badge = _t]),
#"Added Custom" = Table.AddColumn(Source, "Num Only", each Text.Select([Badge], {"0".."9"}))
in
#"Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous you can create a column like below in your data
Proud to be a Super User!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcwxC8JADIbhvxIyd0kudbd00ang1uOGIgEPoyfXivTf26bf8C4PfDHitSjcHq+CDY4jcRDiFlMTsdda8/0JfdZF686OQcR5mL4GQ/44EQvLPqfzVMsbLlsOCkwi5NSVn826QlfMZls3b09y/GJKfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Badge = _t]),
#"Added Custom" = Table.AddColumn(Source, "Num Only", each Text.Select([Badge], {"0".."9"}))
in
#"Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks! Worked like a charm and I was able to link the id with another table in order to pull their names into a bigger dataset.
@Anonymous You can also try below way
Proud to be a Super User!
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |