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

[Bjohnson07]

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: https://community.powerbi.com/t5/Desktop/Calculating-hourly-occupancy-of-a-medical-clinic-based-on-a... 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): 

let
mindate=List.Min(msuplus24h[LISTEDSTARTDATE]),
maxdate=List.Max(msuplus24h[LISTEDSTARTDATE]),
counts=Duration.Days(maxdate-mindate),
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)
in
#"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!

[Bjohnson07]

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?

[jgeddes]

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.

[jgeddes · Accepted solution]

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

let
    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})))
in
    #"Added Custom3"

with the firstShift List defined as 

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

second shift list

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

third shift list

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

In this example I started with...

and ended with...