Forum Discussion
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.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. |
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
7 Replies
Hi Jennifer786 ,
Quick question: have you tried using the Smart Narrative visual? This is exactly what it does using DAX measures.
Pete
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
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
