Forum Discussion
help with power query
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
- temp ={Number.From([Start_date])..Number.From([End_date])}
- A list is generated expanded it and changed to date
- if ( [Temp] = [StartDate]) then [StartTime] else [Temp]
- 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
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.
2 Replies
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
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.
