Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello. I have a Dataset with two string columns. Only one column has a string for each row. In this string is some text and somewhere in this text is a 7 digit number, either split with a widespace or not split at all. How can I parse the string to find the digits?
Solved! Go to Solution.
Quick thought - this will leave you with errors if the field doesn't contain a 7 digit number. Use try and otherwise to leave the field blank if not found:
try List.Select(List.Transform(Text.Split([Column1], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) = 7){0}
otherwise ""
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
pls try this
Hi,
You could try something like this in a custom column in Power Query, just change Column1 to your Column Name:
List.Select(List.Transform(Text.Split([Column1], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) = 7){0}
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Quick thought - this will leave you with errors if the field doesn't contain a 7 digit number. Use try and otherwise to leave the field blank if not found:
try List.Select(List.Transform(Text.Split([Column1], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) = 7){0}
otherwise ""
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Thanks, now there was actually a condition I didn't think of, what if the series of digits can only be 7 digits long and there are bits that are longer than 7 and the code shouldn't catch?
The current formula will only catch the 7 digits if you wanted to modify to capture all digits on the first occurance of digits you could change the = 7 to >0:
try List.Select(List.Transform(Text.Split([Column1], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) >0){0}
otherwise ""
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |