Forum Discussion

Jennifer786's avatar
Jennifer786
Frequent Visitor
4 years ago
Solved

Conditional Column: IF Statement with column values embedded in result text

Hi all   I've scoured the web but can't find an answer for this. Is anyone able to point me in the right direction?   Im trying to create a custom column with the following logic:   Table.AddCo...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Jennifer786 ,

     

    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

Jennifer786's avatar
Jennifer786
Frequent Visitor
4 years ago

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?

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Jennifer786 ,

 

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

  • Jennifer786's avatar
    Jennifer786
    Frequent Visitor
    4 years ago

    great - thanks again Pete!

  • Jennifer786's avatar
    Jennifer786
    Frequent Visitor
    4 years ago

    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")