Datedif Date to Words Possible in Power Query

S

smd747

New member
Joined
Nov 22, 2016
Messages
13
Reaction score
0
Points
0
I use the datedif formula a lot to emphasize time frames. I can do it in excel no problem with

Code: 
=IF(DATEDIF(A4,B4,"y")=0,"",DATEDIF(A4,B4,"y")&" years ")&IF(DATEDIF(A4,B4,"ym")=0,"",
DATEDIF(A4,B4,"ym")&" months ")&DATEDIF(A4,B4,"md")&" days"

I can even doing it in the power query model using this formula:

Code: 
= IF ( INT ( YEARFRAC ( [From Date] - ( DAY ( [From Date] ) - 1 ) * 1.0 , [To Date] - DAY ( [From Date] )  ) ) = 0, "", INT ( YEARFRAC ( [From Date] - ( DAY ( [From Date] ) - 1 ) * 1.0 , [To Date] - DAY ( [From Date] )  ) ) & " years " )
        & IF ( INT ( DIVIDE ( ( YEARFRAC ( [From Date] - ( DAY ( [From Date] ) - 1 ) * 1.0 , [To Date] - DAY ( [From Date] ) ) - INT ( YEARFRAC ( [From Date] - ( DAY ( [From Date] ) - 1 ) * 1.0 , [To Date] - DAY ( [From Date] )  ) ) ), 1 / 12 ) ) = 0, "", 
                INT ( DIVIDE ( ( YEARFRAC ( [From Date] - ( DAY ( [From Date] ) - 1 ) * 1.0 , [To Date] - DAY ( [From Date] ) ) - INT ( YEARFRAC ( [From Date] - ( DAY ( [From Date] ) - 1 ) * 1.0 , [To Date] - DAY ( [From Date] )  ) ) ), 1 / 12 ) ) & " months " )
        & IF ( [To Date] - DAY ( [From Date] )  = EOMONTH ( [To Date] - DAY ( [From Date] ) , 0 ), "", DAY ( [To Date] - DAY ( [From Date] )  ) & " days" )



I am practicing and attempting to be able to do it using power query on my data cleanup and transformation with out having to either add formula to excel or run in power pivot

Is this possible in Power Pivot using the M language?

I have attached my training file. Looking for some direction in Power query if it is possible. Thanks
 
Still coming up short, does any know if this is possible to do in power query?
 
I haven't got time right now to look at this more thoroughly but would using
Date.DayOfYear
I'll look at it a bit more thoroughly later this morning but it's an idea.
 
Here is a bit more detail. This still may need a bit of work but

Date.DayOfYear([To Date])-Date.DayOfYear([From Date])

Will give you the number of days. It doesn't produce 0 if [To Date] and [From Date] are the same date and I'll look at more thoroughly later because it's something that if I haven't had to do in the past I might find myself needing to in the near future.
 
This code

let
Source = Excel.CurrentWorkbook(){[Name="Durations"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"From Date", type date}, {"To Date", type date}}),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type", {{"To Date", type date}}, "en-GB"),
#"Changed Type with Locale2" = Table.TransformColumnTypes(#"Changed Type with Locale1", {{"From Date", type date}}, "en-GB"),
#"Added Custom2" = Table.AddColumn(#"Changed Type with Locale2", "Custom.2", each [To Date]-[From Date]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom.2", type duration}})
in
#"Changed Type1"

Produced this
PQ.PNG

Ok, I've used Locale to help my UK brain with the dates and the result only produces the number of days but it's progress.
 
Thanks for the direction you're definitely onto something, which is closer than I am, but I will continue to work with it for the sheer satisfaction. Thanks much appreciate your help and direction at least now I know it's possible based on your example, your close
 
I have entered some random dates in Column A and B starting from Row # 4. Then I added Datedif formula you mentioned above in cell c4 onwards. I converted this to table and done further work in Power Query.

Here is detailed log

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Datedif", type text}}),
#"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type", "DateDifference", each Duration.Days([End Date] - [Start Date]), Int64.Type),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Date Subtraction",{{"DateDifference", type duration}}),
#"Inserted Total Days" = Table.AddColumn(#"Changed Type1", "DurationTotalDays", each Duration.TotalDays([DateDifference]), type number),
#"Inserted Total Years" = Table.AddColumn(#"Inserted Total Days", "DurationTotalYears", each Duration.TotalDays([DateDifference]) / 365, type number),
#"Added Custom" = Table.AddColumn(#"Inserted Total Years", "Custom", each Number.Mod([DurationTotalYears],1)),
#"Inserted Subtraction" = Table.AddColumn(#"Added Custom", "Inserted Subtraction", each [DurationTotalYears] - [Custom], type number),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction",{{"Inserted Subtraction", "Years"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom.1", each [DurationTotalDays]-([Years]*365)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type number}}),
#"Inserted Integer-Division" = Table.AddColumn(#"Changed Type2", "Inserted Integer-Division", each Number.IntegerDivide([Custom.1], 30), Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Integer-Division",{{"Inserted Integer-Division", "Months"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "Custom.2", each Date.AddYears([Start Date],[Years])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Date.AddMonths([Custom.2],[Months])),
#"Inserted Date Subtraction1" = Table.AddColumn(#"Added Custom3", "DateDifference.1", each Duration.Days([End Date] - [Custom.3]), Int64.Type),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Date Subtraction1",{{"DateDifference.1", "Days"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"DateDifference", "DurationTotalDays", "DurationTotalYears", "Custom", "Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Years", type text}, {"Months", type text}, {"Days", type text}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type3", "Custom", each [Years]&" Years "&[Months]&" Months "&[Days]&" Days"),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom4","0 Years ","",Replacer.ReplaceText,{"Custom"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","0 Months ","",Replacer.ReplaceText,{"Custom"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value1",{"Years", "Months", "Days"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Power Query"}})
in
#"Renamed Columns3"

Please mark as answered if this solves.

This is bit lengthy and may be shortened by other people as well.
 

Attachments

  • Datedif.xlsx
    31.2 KB · Views: 31
Solved Solution Found

Answered and solved with a brilliant and elegant solution.
Thank you for the education, and see the step by step working toward a solution. Brilliant, thank you
 
You must log in or register to reply here.
Back
Top