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
modwonka
Helper I
Helper I

M if text contains, if not empty

Hi,

 

I'm trying to improve one formula i did in M, to extract a date between brackets but i don't know how to advance. If anyone could help me, i would really appreciate it.

 

This is the formula:

 

let l=List.Transform( Text.Split([#"Tags (created on)"]," "), each Text.Remove(_,{"(",")",","})),
i=List.PositionOf(l, "BLUETEXT")
in l{i+1} & " " & l{i+2}

 

The formula is ok, but if in the column doesn't has "BLUETEXT" the formula extract the first text, no the text inside the bracket. 

 

How can i create a if there's not BLUETEXT leave empty? 

 

thanks! 

1 ACCEPTED SOLUTION

in was missing

let l=List.Transform( Text.Split([#"Tags (created on)"]," "), each Text.Remove(_,{"(",")",","})),
i=List.PositionOf(l, "BLUETEXT")
in if i>=0 then l{i+1} & " " & l{i+2} else null

View solution in original post

5 REPLIES 5
Vijay_A_Verma
Super User
Super User

I think should work for you in last line

if i>=0 then l{i+1} & " " & l{i+2} else null

 

Thanks for your answer Vijay_A_Verma!

 

I tried the solution with your code:

 

let l=List.Transform( Text.Split([#"Tags (created on)"]," "), each Text.Remove(_,{"(",")",","})),
i=List.PositionOf(l, "BLUETEXT"),
if i>=0 then l{i+1} & " " & l{i+2} else null

 

There's one error: "Token identifier expected". In the "if". Is this ok?

in was missing

let l=List.Transform( Text.Split([#"Tags (created on)"]," "), each Text.Remove(_,{"(",")",","})),
i=List.PositionOf(l, "BLUETEXT")
in if i>=0 then l{i+1} & " " & l{i+2} else null

Hi Vijay_A_Verma,

just one last thing, in case you can help again with this 😉 

As you know, if the formula does not find BLUETEXT it puts "null". If it finds "BLUETEXT", it extracts a date from inside the parenthesis.

The issue is that I would like to compare this column with a new one with the same formula. Date vs date. And get the smaller one and select it. But with the null that formula is broken.... how can I fix it?

Thank you so much! That worked 😉 

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