help with power query

[Anonymous]

Hello,

 I have a data set for bike rental  with start date and time and end date and time. now, i want to calculate the difference between the start date and and time and end date and time. for example if a bike starts at 06.00 am on 7/2/2016 and ends on 7/3/2016 at 4.30 am the no of hours between them must be calculated which is 22 hours  and per each hours i want to print a new row with all the data included and the updated hour. for example :

#row1 trip id 1 , start time and date 7/2/2016 06:00 am

#row 2 trip id 1, start time and date 7/2/2016 07:00 am

#row 3 trip id 1, start time and date 7/2/2016 08:00 am 

#row 3 trip id 1, start time and date 7/2/2016 09:00 am ..........so ontrip id 1, till 7/3/2016 04:00am 

i have tried using the 

1. temp ={Number.From([Start_date])..Number.From([End_date])}
2. A list is generated expanded it and changed to date 
3. if ( [Temp] = [StartDate]) then [StartTime] else [Temp]
4. if ([Temp] = [EndDate]) then [EndTime] else [Temp] & #time(23,59,59) but, it dint work. i am attaching a screen shot for reference. Thank you for the help in advance. sample data with date and time , excluding other multiple columns

[v-frfei-msft]

Hi @Anonymous ,

One sample for your reference. Please check the following steps as below.

1. Create a table in power query like this.

let
    Source = List.Dates(#date(2016,7,1), 3, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each {0..23}),
    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded {0}", "DateTime", each DateTime.From([Date])+#duration(0,[Custom],0,0))
in
    #"Added Custom1"

2. Close and apply, then we can create a calculated table based on it.

Table 2 = var a =ADDCOLUMNS(CROSSJOIN(Query1,'Table'),"st",'Table'[start time],"ed",'Table'[end time])
var b =FILTER(a,[DateTime]>=[st] && [DateTime]<=[ed])
return
b

[Anonymous · Accepted solution]

thank you for the kind reply.

https://www.dropbox.com/s/oiodhrh5255bt8a/sampledatafor2016.xlsx?dl=0

but my data is not just for one day, i am sorry i was not clear with the question, i am very new to power bi and power queries. i have added a sample link for the file, it goes for 2016, july to end of year. and when i try to do this way , i am getting an error. list.dates gives list only for a month, actually i have data from july, 2016  to ,2017,2018, 2019 years . since power bi is not allowing me to have 24 hours  rows for all these years in same table, i split them in multiple tables yearly.

could you check the sample data and suggest me some solution.