Forum Discussion

ShaneL79's avatar
ShaneL79
Helper I
3 years ago
Solved

Distributing Hours over Months using START/END dates/time.

I just want to point out that I am not comfortable with Power Query, so if at all possible I would prefer answers that involve measures only. If that isn't possible, please let me know.   My issue,...
Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    I think it's because you are adding this as a custom column. Not as a function like I told you.

     

    From New Source select Blank Query. Name this query on the left box (like changing the name of the source table). Then open Advanced Editor (it's a button next to the Refresh Preview)and paste this:

    let getParameters = (StartDate, EndDate) =>
    
    let

        NullStart = StartDate = "" or StartDate = null,
        Start = if NullStart then null else Date.From(StartDate),
        NullEnd = EndDate = "" or EndDate = null,
        End = if NullEnd then null else Date.From(EndDate),

        CountDays = if NullEnd or NullStart then 1 else Duration.Days(End-Start) + 1,

        DateList = if NullStart then {null} else List.Dates(Start, CountDays, #duration(1, 0, 0, 0)),

        #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
        #"Add Custom Start DateTime" = Table.AddColumn(#"Converted to Table", "Start datetime", each if NullStart then null else if [Date] = Start then StartDate else DateTime.From([Date])),
        #"Add Custom End DateTime" = Table.AddColumn(#"Add Custom Start DateTime", "End datetime", each if NullEnd then null else if [Date] = End then EndDate else [Date] & #time(23,59,59)),
        #"Change Types" = Table.TransformColumnTypes(#"Add Custom End DateTime",{{"End datetime", type datetime}, {"Start datetime", type datetime}}),
        #"Duration" = Table.AddColumn(#"Change Types", "Duration", each Duration.TotalMinutes([End datetime]-[Start datetime])/60, type number),
        #"Remove Date" = Table.RemoveColumns(#"Duration",{"Date"})
        
    in
        #"Remove Date"
        
in
    getParameters

    After doing that you should see something like this:

     

    Then go to your table and from ribbon select new column > invoke custom function:

    Select column name, query name that you have provided in previous steps and the start and end columns (example on screenshot).

    And you should know the rest. 🙂

bolfri's avatar
bolfri
Solution Sage
3 years ago

No problem. Everyone starts from somewhere. I can understand that.

You said the problem is with records that has null in the Start Date, right? Your sample data didn't provide such scenario. What do you expect to be in the new columns and the durations if the StartDate is null?

ShaneL79's avatar
ShaneL79
Helper I
3 years ago

You're right, I didn't say that start time can be null and I should have. It is true though. Some of the events do not require work, so therefore there is no start/end time for those events (the majority of events are like this in fact). 

 

If the start date is null, then duration is 0. The start/end times can be null as well, or anything that may fit better. I am using the dates from other columns for my reports, I just needed the duration to be broken out by day - if that makes sense.

 

Here is an snippet of the data from the table. Ignore the "min alarm received date" column - that's for another measure.

 

 

 

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    Ok, so... 

    splitting_datetime_to_datetime_with_nullend_and_nullstart ^.^

    If StartDate is null, all the columns: Start Datetime, End Datetime and Duration will be null

    let getParameters = (StartDate, EndDate) =>
    
    let

        NullStart = StartDate = "" or StartDate = null,
        Start = if NullStart then null else Date.From(StartDate),
        NullEnd = EndDate = "" or EndDate = null,
        End = if NullEnd then null else Date.From(EndDate),

        CountDays = if NullEnd or NullStart then 1 else Duration.Days(End-Start) + 1,

        DateList = if NullStart then {null} else List.Dates(Start, CountDays, #duration(1, 0, 0, 0)),

        #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
        #"Add Custom Start DateTime" = Table.AddColumn(#"Converted to Table", "Start datetime", each if NullStart then null else if [Date] = Start then StartDate else DateTime.From([Date])),
        #"Add Custom End DateTime" = Table.AddColumn(#"Add Custom Start DateTime", "End datetime", each if NullEnd then null else if [Date] = End then EndDate else [Date] & #time(23,59,59)),
        #"Change Types" = Table.TransformColumnTypes(#"Add Custom End DateTime",{{"End datetime", type datetime}, {"Start datetime", type datetime}}),
        #"Duration" = Table.AddColumn(#"Change Types", "Duration", each Number.RoundUp(Duration.TotalMinutes([End datetime]-[Start datetime])/60), type number),
        #"Remove Date" = Table.RemoveColumns(#"Duration",{"Date"})
        
    in
        #"Remove Date"
        
in
    getParameters

     

    • ShaneL79's avatar
      ShaneL79
      Helper I
      3 years ago

      Okay, this seemed to work. However, I had to remove the following:

      • In the first line I removed "let getParameters"
      • In the last two lines I removed "in getParameters"

      Without removing that it did not work. Are those removals going to be a problem?

       

      Now the durations appear correctly, however they are whole numbers. 

       

      Example: 25 minutes is showing as 1 hour.

       

      I would prefer it showed with three decimal places so it would be 0.417. Any idea how to switch this to 3 decimals?

       

      Thanks again for all of your help. I really appreciate this.

      • bolfri's avatar
        bolfri
        Solution Sage
        3 years ago

        I think it's because you are adding this as a custom column. Not as a function like I told you.

         

        From New Source select Blank Query. Name this query on the left box (like changing the name of the source table). Then open Advanced Editor (it's a button next to the Refresh Preview)and paste this:

        let getParameters = (StartDate, EndDate) =>
    
    let

        NullStart = StartDate = "" or StartDate = null,
        Start = if NullStart then null else Date.From(StartDate),
        NullEnd = EndDate = "" or EndDate = null,
        End = if NullEnd then null else Date.From(EndDate),

        CountDays = if NullEnd or NullStart then 1 else Duration.Days(End-Start) + 1,

        DateList = if NullStart then {null} else List.Dates(Start, CountDays, #duration(1, 0, 0, 0)),

        #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
        #"Add Custom Start DateTime" = Table.AddColumn(#"Converted to Table", "Start datetime", each if NullStart then null else if [Date] = Start then StartDate else DateTime.From([Date])),
        #"Add Custom End DateTime" = Table.AddColumn(#"Add Custom Start DateTime", "End datetime", each if NullEnd then null else if [Date] = End then EndDate else [Date] & #time(23,59,59)),
        #"Change Types" = Table.TransformColumnTypes(#"Add Custom End DateTime",{{"End datetime", type datetime}, {"Start datetime", type datetime}}),
        #"Duration" = Table.AddColumn(#"Change Types", "Duration", each Duration.TotalMinutes([End datetime]-[Start datetime])/60, type number),
        #"Remove Date" = Table.RemoveColumns(#"Duration",{"Date"})
        
    in
        #"Remove Date"
        
in
    getParameters

        After doing that you should see something like this:

         

        Then go to your table and from ribbon select new column > invoke custom function:

        Select column name, query name that you have provided in previous steps and the start and end columns (example on screenshot).

        And you should know the rest. 🙂