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!
Solved! Go to 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
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 😉