Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |