Forum Discussion
Conditional column to match a range of substring
I have Column A from a dataset, I need to make the result into Column B, basically every result must have calling code in front.
While I can write a really long ugly code something like below, is there anyway I could write it like Text.StartsWith([Column A], "01"&{"0".."9"}) so that I dont have to repeat for each combination. If possible I would like to stick to this type of solution, but I welcome other type of solution too.
= if (Text.StartsWith([Column A], "010") or Text.StartsWith([Column A], "011") or
Text.StartsWith([Column A], "012") or
Text.StartsWith([Column A], "013") or
Text.StartsWith([Column A], "014") or
Text.StartsWith([Column A], "015") or
Text.StartsWith([Column A], "016") or
Text.StartsWith([Column A], "017") or
Text.StartsWith([Column A], "018") or
Text.StartsWith([Column A], "019")) and
Text.Length([Column A])>=10 and Text.Length([Column A])<=11
then "+6"&[Column A]
Hi smko
How about
= if Text.StartsWith([Column A], "01") and List.Contains({"0".."9"}, Text.Middle([Column A],2)) and Text.Length([Column A])>=10 and Text.Length([Column A])<=11 then "+6"&[Column A] else nullYou'll have to update the else clause
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
2 Replies
Hi smko
How about
= if Text.StartsWith([Column A], "01") and List.Contains({"0".."9"}, Text.Middle([Column A],2)) and Text.Length([Column A])>=10 and Text.Length([Column A])<=11 then "+6"&[Column A] else nullYou'll have to update the else clause
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Excellent!! Thank you for saving my ugly code. There is one small mistake though, text.middle should be
Text.Middle([Column A],2,1)otherwise it grab entire string after 2nd position
