Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😉
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.