Forum Discussion
Conditional Column: IF Statement with column values embedded in result text
No problem, thought I'd ask 🙂
I think the column formula you want is something like this:
if [Opened vs 1st Time Recorded] < 0 then Text.Combine({"The timesheet indicates... ", Text.From(Date.From([TimeDate])), " prior to...", Text.From(Date.From([OpenDate])), " by ", Text.From([Opened vs 1st Time Recorded]), ". Confirm..."}) else nullPete
No problem, thought I'd ask 🙂
I think the column formula you want is something like this:
if [Opened vs 1st Time Recorded] < 0 then
Text.Combine({"The timesheet indicates... ", Text.From(Date.From([TimeDate])), " prior to...", Text.From(Date.From([OpenDate])), " by ", Text.From([Opened vs 1st Time Recorded]), ". Confirm..."})
else null
Pete
Omg thanks Pete that was perfect!
So you nested the Text.Combine within the IF statement which is exactly what I wanted! Did you have to use anything to nest the formulas or does Power query just recognise the formula and where it ends with the 'else' in the IF statement?
Power query IF statements are divided by the keywords 'if'...'then'...['else if']...'else'.
You can put anything in between them (within reason!) and PQ will distinguish the calculation/functional segment from the IF-clause transition.
Pete
great - thanks again Pete!
Hi Pete - related quesion, I want to display a value from another column within the nested text.combine, but Text.From([% Value]) displays the result as a number e.g. 1.5 instead of 150%.
Is there a function or formula for this?
EDIT: nevermind! Found it by using Number.ToText([% Value], "p")