Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Need a table to list the hours between two date/time columns

Alright all you fabulous PowerBI gurus - I need help creating a table that tells me each hour between two date/time columns.


I was able to do this without issue when creating a table that was looking at just dates, but I would like the same thing but hours.  What worked for dates: 

Occupancy Days - SVC = SELECTCOLUMNS(GENERATE('WWTS - Service',DATESBETWEEN('010-010 : Calendar : IND'[Date],'WWTS - Service'[Project Start Date],'WWTS - Service'[Project End Date])),"Test Cell", 'WWTS - Service'[Cell],"Project Type",'WWTS - Service'[Project Type],"PPN",'WWTS - Service'[PPN],"SVC Occ Days",[Date],"Site",'WWTS - Service'[Site])
New table looks like this with that code: 

In the original dataset it's one line item: Cell, Project type, PPN, starttime, endtime, Site.  As you can see the hours are all 12AM.

What I have now that I need to figure out (and maybe I'm thinking about it completely wrong - if so I'm all ears!) is when somthing starts (STARTTIME column) and stops (ENDTIME column) I need to understand for each hour it's "running" which shift it falls on.  So if something starts at 2pm and stops at 5pm I need to see that there was 1 hour on 1st shift and 2 on 2nd shift etc. (1st shift 7am-3pm; second shift 3pm-11pm; thrid shift 11pm-7am)  So if I could create a table like above but with hours I would be able to make that happen.  I know this will be a big table but I don't know how else to look at it.
I tried the code from this post: and I was able to see a table with the times, but couldn't figure out how to also have it show the cell/project type/ppn etc. and it's only the date with hours.  Now I am new to advanced editor and don't have a coding background so I'm fumbling around (I have learned what I needed to in order to get powerbi to do what I needed), but here is the code I got to work after adding a column that is the start date/time but type changed to only date (LISTEDSTARTDATE): 
dates = List.Dates(mindate,counts+1,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each List.DateTimes(#datetime(Date.Year([Date]),Date.Month([Date]),Date.Day([Date]),0,0,0),24,#duration(0,1,0,0))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "datetime"}}),
#"Inserted Time" = Table.AddColumn(#"Renamed Columns1", "Time", each DateTime.Time([datetime]), type time),
#"Inserted Hour" = Table.AddColumn(#"Inserted Time", "Hour", each Time.Hour([Time]), Int64.Type)
#"Inserted Hour"
here is the output: 


How on earth do I get the other data in there as well?  Or is there an easier way to figure out how many hours are running on each shift?   


Any advice/guidance/help is greatly appreciated!

Frequent Visitor

So when I enter this and update with my dataset, I can see the data generate in the Power Query window, but I get an error when it tries to load the data - I get a "OLE DB or ODBC error: [Expression.Error] We cannot conver the null value to number.  I cannot figure out where that error is originating from.  There are no null values in the table (I put a step in to replace them).  now we are talking over 7M rows of data...not sure if that is the problem or not.  but as you can see in the screenshot I do get the same dataset you're showing but with my data (again, only in the power query screen)


Any clue what could be causing the error or where to start looking?

My first suggestion would be to add a 'Keep Errors' step to find out which rows are causing the issue. From that point you may get more insight as to which column is causing the error. A null date value in either the start or end column will cause that error to occur.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User
Super User

If all you need to know is how many hours are spent on each shift I would try something like...

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTLSN7TUNzIwMlEwNLAyMACLGBlARYxBIrE60UpJqCrNYSphIkaGMJXJKCYYAFUaoogYGVuZWirFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"start", type datetime}, {"end", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "HoursInBetween", each List.Transform(List.DateTimes([start], Duration.TotalHours([end]-[start]), #duration(0,1,0,0)), each DateTime.ToText(_, [Format="HH"]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "firstShiftHours", each List.Count(List.Intersect({[HoursInBetween], firstShift}))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "secondShiftHours", each List.Count(List.Intersect({[HoursInBetween], secondShift}))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "thirdShiftHours", each List.Count(List.Intersect({[HoursInBetween], thirdShift})))
    #"Added Custom3"

with the firstShift List defined as 

    Source = {"07", "08", "09", "10", "11", "12", "13", "14"}

second shift list

    Source = {"15", "16", "17", "18", "19", "20", "21", "22"}

third shift list

    Source = {"23", "00", "01", "02", "03", "04", "05", "06"}

In this example I started with...


and ended with...



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors