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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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