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

[Jennifer786]

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.AddColumn(#"Added Custom1", "Custom", each if

[Opened vs 1st Time Recorded] < 0 then

"The timesheet indicates that work commenced on this matter"&[TimeDate]&"prior to the file opening"&[OpenDate]&"by "&[Opened vs 1st Time Recorded]&". Confirm and flag if needed." else null)

Reference	TimeDate	OpenDate	Opened vs 1st Time Recorded	Custom
1234	07/03/2017 00:00:00	14/03/2019 00:00	-52	The timesheet indicates that work commenced on this matter [TimeDate] prior to the file opening [OpenDate] by [Opened vs 1st Time Recorded]. Confirm and flag if need.

[BA_Pete]

Hi @Jennifer786 ,

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

Pete

[Jennifer786]

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 · Accepted solution]

@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]

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]

@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]

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

[Jennifer786]

great - thanks again Pete!