Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]
Solved! Go to Solution.
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 null
You'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. |
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 null
You'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