Forum Discussion
Using Today's date for aging calculation in custom column
I was able to figure this out on my own. I did a bit more research on using M and also discovered the Add Conditional Column feature, which got me started on the if then else statement.
#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns2", "Aging", each if [Revised Implementation Date] = null then Duration.Days(today-[Original Estimated Implementation Date]) else Duration.Days(today-[Revised Implementation Date]))
Thanks for getting me started along this path m_dekorte
Cheers
Hi CRyley
Great you found a solution on your own!
With the error message I could identify the problem with the code above you were missing a set of parenthesis. That needed to be wrapped around Date.From and the end of the expression, like below.
Duration.Days( today - ( Date.From( [Revised Implementation Date] ) ?? [Original Estimated Implementation Date] ))
Hi CRyley,
First, so what where you expecting?
Second, yes you will need to transfer this logic into a custom column. The sample provided is just to illustrate how the code works as you didn't supply a sample of your own.
Here are the steps to incorporate this logic.
Go to your excisting query and open the advanced editor. From the sample provided, copy the first line of code and paste that above the first line of code in your query. That's this bit.
today = Date.From( DateTime.FixedLocalNow()),
Close the advanced editor and use the user interface to add a custom column. In the dialog, give that new column a proper name and copy this bit into the code section.
Duration.Days( today - (Date.From( [Revised Implementation Date] ) ?? [Original Estimated Implementation Date] ))
If your column names do NOT match, select that field reference in the code section, delete it and double click the correct field from the field list on the right hand side to replace it.
Done.
Again, know that the today variable will return the current date from the system upon query refresh.
Please mark this answer as solution when it helped you to resolve your question, thanks!
Thanks again m_dekorte.
Regarding your comment on my statement regarding the results not being quite what I expected, your suggested today query appeared to use my source data of 9 rows, but only returned 2 rows. I would have thought that if it returned 2 rows of the 9 it would have return more. That said, I'm not sure what the query was intended to return so likely my expectations where wrong.
So, I've added what I assume is your variable definition statement to the query, and no syntax errors were detected
(I initially made an error due to use of date.From instead of Date.From. The error messages helped me sort this out.)
Then, I created the new column and added your suggested code to the formula:
The resulting column returns one value (where there is a value in the [Revised Implementation Date] field) and Errors for the other rows (where the revised date is null):
This makes sense, and is getting me closer to what I need. However, I need to build in logic so that if there is a null value in the [Revised Implementation Date] field/column that the formula will then use the [Original Estimated Implementation Date] value. I can't figure out how to incorporate logic into the formula field comparable to a typical Excel If/Then statement such as: If([Revised Implementation Date]=0,today-[Original Implementation Date],today-[Revised Implementation Date]).
How do If/Then statements work in Power Query Editor and where to I use them?
Thanks again
Hi CRyley,
I think you are very, very close...
There is one step you didn't repeat back in your actions. Let's start there...
When you click off to the side in the whitespace beside any of the errors does the say:
Expression.Error: The field 'Original Estimated Implementation Date' of the record wasn't found?
If it does, press the gear wheel on the Add Custom Column step to open the dialog
delect that field reference [Original Estimated Implementation Date], look through the field list on the right hand side to select the correct column name, double click it to enter it in the code and press done.
Please mark this answer as solution when it helped you to resolve your question, thanks!
Thanks again.
When I click in the whitespace where I'm getting an error, I get:
"Expression.Error: We cannot convert the value #date(2019, 12, 31) to type Duration.
Details:
Value=12/31/2019
Type=[Type]Any suggestions would be welcome.
Thanks.
I was able to figure this out on my own. I did a bit more research on using M and also discovered the Add Conditional Column feature, which got me started on the if then else statement.
#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns2", "Aging", each if [Revised Implementation Date] = null then Duration.Days(today-[Original Estimated Implementation Date]) else Duration.Days(today-[Revised Implementation Date]))
Thanks for getting me started along this path m_dekorte
Cheers