Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to strip out numbers from a string where the numbers are in a chain of 5 or greater numbers.
ie 12345 would be return 12345 but 12 234 wouldnt return anything.
I've listed an example of the input and the desired output below
Any ideas?
Input | Results |
1223.com IO 7156765 | 7156765 |
IO 159356 Gas Networks europe | 159356 |
Loreal Organics 2020 Nar IO: 159718 | 159718 |
Pharmaton IO: 159722 Audio | 159722 |
BGE Shine a Light IO: 159725 | 159725 |
Volkswagen IO: 140938 Video | 140938 |
IBTS 2019 IO: 88675 | 88675 |
Solved! Go to Solution.
You can add a custom column with this formula to get your desired result.
Text.Combine(List.Select(List.Transform(Text.Split([Input], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) >=5), ", ")
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi, @StephenF , mahoneypat's solution is neat and effetive to cope with your issue.
If your dataset by chance contains such an entry "1223.com IO 7156765and55555", you may want to try a more generic solution as follows,
#"Added Custom" = Table.AddColumn(Source, "Custom",
each [
lst_delim = Text.Remove([Input], {"0".."9"}),
lst_num = List.Select(Text.SplitAny([Input], lst_delim), each _<>"" and Text.Length(_)>4),
result = Text.Combine(lst_num," ")
][result]
)
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! |
Yeah, Pats solution will cover 99.9% of cases, I just found one where it was an issue where there was both a 5 and a 7 digit number with only the 7 digit one being valid useful data.
I really just will have to have some error tolerance exception handling so it won't crash the query and the data will of course be stored as a text string only at all times.
You can add a custom column with this formula to get your desired result.
Text.Combine(List.Select(List.Transform(Text.Split([Input], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) >=5), ", ")
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |