Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi there,
I currently have a date column called "closing date", and I want to create a new custom column in Power Query that will either give "Expired" or "Active" depending if the "closing date" has surpassed current date or not.
So for example,
if closing date is less than todays date (example: DateTime.Date(DateTime.LocalNow())) then new column value = Expired
else the column value = Active
How would I be able to do this in Power Query?
Solved! Go to Solution.
Hi @Anonymous,
this is just an example to give you a taste of the possible solution. For your code you need to adopt the Added Custom step. In your case if would look something like:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [closing date] < Date.From(DateTime.FixedLocalNow()) then "Expired" else "Active", type text)
If you are using a standard Add Column button in the main menu, this is the code that you need to paste into the dialog box:
if [closing date] < Date.From(DateTime.FixedLocalNow()) then "Expired" else "Active"
Cheers,
John
Hi @Anonymous,
Something like this?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MlKK1YlWMkJiGyOxTZDYpiC2sbFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Date] < Date.From(DateTime.FixedLocalNow()) then "Expired" else "Active", type text)
in
#"Added Custom"
Cheers,
John
Hi @jbwtp
I'm a bit confused with this one, I've added it in, but it looks like it's just generated it's own date column. I want it to use my date column which is "closing date" and check if that's less than todays date.
Hi @Anonymous,
this is just an example to give you a taste of the possible solution. For your code you need to adopt the Added Custom step. In your case if would look something like:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [closing date] < Date.From(DateTime.FixedLocalNow()) then "Expired" else "Active", type text)
If you are using a standard Add Column button in the main menu, this is the code that you need to paste into the dialog box:
if [closing date] < Date.From(DateTime.FixedLocalNow()) then "Expired" else "Active"
Cheers,
John
Check out the July 2025 Power BI update to learn about new features.