cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Andy1927
Helper I
Helper I

Simple Conditional Column on Today's Date - Power Query

Hi,

I want to add a column using Power Query that shows anything before Todays date = "expired" else "live"

 

I have this code but im getting an error saying 'Expression.SyntaxError: Token Then expected' with the ^ just below the comma after LocalNow()), below. 

 

= Table.AddColumn(#"Added Conditional Column", "Historic", each if [Agreement End Date] <= DateTime.Date(DateTime.LocalNow()), then "Expired" else "Live")

 

I'm quite new to PowerBI and I've tried suggestions from related questions already posted but no joy - how do I fix this and / or is there a better way of doing this?

 

Many thanks,

 

Andy

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Andy1927 , there is addition comma , removed here 

if [Agreement End Date] <= DateTime.Date(DateTime.LocalNow()) then "Expired" else "Live"

 

or

 

= Table.AddColumn(#"Added Conditional Column", "Historic", each if [Agreement End Date] <= DateTime.Date(DateTime.LocalNow()) then "Expired" else "Live")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@Andy1927 , there is addition comma , removed here 

if [Agreement End Date] <= DateTime.Date(DateTime.LocalNow()) then "Expired" else "Live"

 

or

 

= Table.AddColumn(#"Added Conditional Column", "Historic", each if [Agreement End Date] <= DateTime.Date(DateTime.LocalNow()) then "Expired" else "Live")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Many thanks!

 

I have one additional request and I'm not sure if it requires a new separate ticket? I have previously used the data model to create 'date bandings' e.g. 0-6 mths, 7-12 mths, and so on. I would like to do the same in query editor using the Agreement End Date  - is this possible? If so, is there an example I could use?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors