Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How do we fill the working hours between those two dates with Power Query?

Hello guys, Im trying to calculate the working hours between two dates using the formula Decimal.From but I can't get decimal number at the start/end of the shift. 

 

 

 

 

If the start and end shift have Whole number its well calculated but if the shift has a decimal number it shows an error.

Any suggestions will be highly appreciated.

Thank you guys 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    As @ edhans mentioned, if just create a list like {a..b}, the default increment is 1 and only use List.combine() to add decimal numbers.

    In this case, if you want to create your expected column, you may create a conditional column using if statement and combine it with List.Combine() and List.Numbers(), like this:

        #"Added Custom2" = 
        Table.AddColumn(
            #"Added Custom1", "DATE ETAT", each 
                if [End_] - [Start_] < 1 then 
                    List.Combine({{[Start_]},{[End_]}}) 
                else if 
                    [Start_] = Number.RoundDown([Start_]) and [End_] = Number.RoundDown([End_]) then 
                    List.Numbers([Start_],[End_],1) 
                else if 
                    [Start_] = Number.RoundDown([Start_]) and [End_] > Number.RoundDown([End_]) then 
                    List.Combine({List.Numbers([Start_],Number.RoundDown([End_]) + 1,1),{[End_]}}) 
                else if 
                    [Start_] < Number.RoundUp([Start_]) and [End_] > Number.RoundDown([End_]) then 
                    List.Combine({{[Start_]},{Number.RoundUp([Start_])..Number.RoundDown([End_])} ,{[End_]}}) 
                else null
        )

    This is just an example, in your acutal table, you may need more conditions.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    You need to use the hour, from Time.Hour(). For example:

    {Time.Hour(DateTime.Time([Start Date]))..Time.Hour(DateTime.Time([End Date]))}

    This will generate a list that works. Lists must be integers.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you edhans  for your suggestion. Im trying to get the hour with its minutes in the list. I changed the hour to number so I can loop it and get the decimal number.

       

       

      I want the list to include Decimal numbers.

       

      Thank you for your help

       

      • edhans's avatar
        edhans
        Community Champion
        5 years ago

        You cannot generate a list like that. At best what you could do is use logic to generate a list the integers from 1 to n-1, so 1..10, then append the last item with the decimal in it.

        let
    Source = {1..10},
    Custom1 = List.Combine({Source, {11.7}})
in
    Custom1

        It returns this list.

        But there is no way to say "Generate a list from 1 to 11.7, increment by 1, until you get to 11.7, then tack on 11.7" without using some sort of combine operation as I have done here.