Today's date in Power Query custom columns

Question

Hi Community,

Really struggling to get Power Query to accept Today's date in a custom column calc. Not sure if I have the wrong function so any guidance would be appreciated!

I want a custom column to count the days between Today's date and the date that data was uploaded.

I have tried with several formula:

eg

= Duration.Days(Today()-[Date Uploaded])

= Duration.Days(TodayDate-[Date Uploaded])

= [today]-[Date Uploaded]

= [Today]-[Date Uploaded]

As you can see I have run out of options, but in each case Power Query gives me a green tick to say No syntax errors have been detected, and yet above that I get a yellow banner saying the function was not recognised. see image attached as an example.

Answer 1

Hi,

Thanks you for querying in this forum.

We go through the post carefully. You want to count the days between Today's date and the date that data was uploaded. But Today function doesn't work in Power Query formula in Excel.

We searched a lot for this kind of similar requirement. As mentioned in these articles, DateTime.LocalNow function can get today's date. You may also have a try.

DateTime.LocalNow - PowerQuery M | Microsoft Docs

Power Query - get todays date?

Add a Custom Column with Today’s Date using Power Query M

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Hope you have a nice day and stay safe!

Best regards,

Tina

Answer 2

Have you tried to take the easy route?

Just position yourself in the Date uploaded column and add age, than if you want days, years, seconds or whatnot, add them with duration:

Sincerely,

Answer 3

This is a task to calculate the number of days between 2 dates. How would this work without comparing the difference between the current date and the uploaded date?

Furthermore I could not reproduce your steps, In your example, you managed to add a column (not sure if that is automatic or whether you duplicated it) . In either case you are just converting Uploaded Date to Age (days etc) without performing the date diff calc.

Thankyou for your contributions however.

David

Answer 4

Thankyou Tina, this solved the challenge!