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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.