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

[Anonymous]

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 

[edhans]

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]

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

[v-yingjl · Accepted solution]

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.

[edhans]

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.