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

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

 

  • 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. 🙂

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