Results 1 to 9 of 9

Thread: Datedif Date to Words Possible in Power Query

  1. #1

    Datedif Date to Words Possible in Power Query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Still coming up short, does any know if this is possible to do in power query?

  3. #3
    Acolyte Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    57
    Articles
    0
    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.

  4. #4
    Acolyte Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    57
    Articles
    0
    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.

  5. #5
    Acolyte Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    57
    Articles
    0
    My previous suggestion is limited in what you can do given that your dates span years. However, if you take a look at https://msdn.microsoft.com/en-us/library/mt253419.aspx you might find a better way of doing what you already are. I'll carry on looking at this

  6. #6
    Acolyte Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    57
    Articles
    0
    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
    Click image for larger version. 

Name:	PQ.PNG 
Views:	8 
Size:	30.8 KB 
ID:	6235

    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.

  7. #7
    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

  8. #8
    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.
    Attached Files Attached Files

  9. #9

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •