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] ))
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
I was able to figure this out on my own. I did a bit more research on using M and also discovered the Add Condition 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] ))
m_dekorte, thanks for your continued assistance.
Chris