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

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi Jennifer786 ,

 

Quick question: have you tried using the Smart Narrative visual? This is exactly what it does using DAX measures.

 

Pete

Jennifer786's avatar
Jennifer786
Frequent Visitor
4 years ago

Thanks Pete! I'm kind of double jobbing here and want to run this query into excel too. The dataset is huge so I'm trying to resolve it on the query side

  • BA_Pete's avatar
    BA_Pete
    Super User
    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