Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smko
Helper I
Helper I

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.

smko_1-1623751539177.png

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]

 

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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

SU18_powerbi_badge

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.

 

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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

SU18_powerbi_badge

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors