Occupancy Problem

[TrueDat]

Complicated problem!
Thanks to Mr. Bill Jelen for authoring the extraordinary book; “PowerPivot for the Data Analyst”. However, I remain stumped on a difficult problem; Any assistance is greatly appreciated!!!
The problem would be similar to restaurant occupancy. I need to calculate how many people are in the restaurant at each 15 minute interval, 24 hours a day, 7 days a week and 365 a year. From a report, I am able to see each individuals (stamped with customer #) date and time in, along with the date and time out for every customer. As an example, customer A arrives on 03/29/2017, 11:44pm and leaves on 03/30/2017 at 12:31am. He spent 47 minutes and was present at 4 different time segments; 11:45pm, midnight, 12:15am and 12:30am. If customer B was also present from 03/30/2017, 12:01am to 03/30/2017, 12:29am, then I need to show 1 customer at 12:00am, 2 customers at 12:15am and back to 1 customer at 12:30am. I need to analyze by the table number they sat at. I would like to show the results on an excel sheet with 15 minutes time segments as rows and table numbers as columns. E.g. I should be able to know how many people were seated at table #4 at 15:45, and separately at table #7 at the same time; and all subsequent 15 minute time segments throughout the day.
To compound the problem, anything up to 3:59am is counted as occurring on the previous date, then the cash register is changed and begins a new date at 4:00am.
I am using Excel 2010 with PowerPivot add-on, 32 bit installation.
Thank you in advance!

 

[MarcelBeug]

It looks like the only difficult part is to "explode" your table to records per quarter. The other requirements are pretty straightforward.

This would be my suggestion: just multiply the date/times in/out by 96 (# quarters per day), round them up/down and create lists for the numbers between the outcomes.
After expanding the resulting list column, the numbers can be calculated back to date/times.

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Time", each {Number.RoundUp(96*Number.From([In]),0)..Number.RoundDown(96*Number.From([Out]),0)}),
    #"Expanded Time" = Table.ExpandListColumn(#"Added Custom", "Time"),
    #"Transformed To DateTime" = Table.TransformColumns(#"Expanded Time", {{"Time", each DateTime.From(_/96), type datetime}}),
    #"Added Date" = Table.AddColumn(#"Transformed To DateTime", "Date", each DateTime.Date([Time] - #duration(0,4,0,0)), type date),
    #"Extracted Time" = Table.TransformColumns(#"Added Date",{{"Time", DateTime.Time}}),
    #"Grouped Rows" = Table.Group(#"Extracted Time", {"Table", "Time", "Date"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Table", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Sort(List.Distinct(#"Changed Type"[Table])), "Table", "Count", List.Sum),
    #"Added All Tables" = Table.AddColumn(#"Pivoted Column", "All Tables", each List.Sum(List.Skip(Record.FieldValues(_),2))),
    #"Reordered Columns" = Table.ReorderColumns(#"Added All Tables",{"Date", "Time"})
in
    #"Reordered Columns"

 

[TrueDat]

Thank you for your solution Marcel!
This is exactly the result I'm looking for, however, I cannot replicate the solution. I am assuming you are pasting the code into the (Power) Query Advanced editor. When I do this I get the following error: "Expression.Error A cyclic reference was encountered during evaluation."
Thanks again for your help!

 

[MarcelBeug]

I have another query: "Table1" imports Excel Table1 in Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer #", type text}, {"In", type datetime}, {"Out", type datetime}, {"Table", Int64.Type}})
in
    #"Changed Type"

The query in my previous post I called "Occupancy". Possibly you called it "Table1"? This would explain the cyclic reference, as a query can't be its own source.

 

[TrueDat]

Hi Marcel, Thanks again for your timely response. I must be pasting the code incorrectly somehow. When the Code you provided is pasted into the Advanced Editor (Power Query 2010), it does not produce the table that you were able to produce. The following link shows screen shots of my results:
https://drive.google.com/file/d/0B5Q1YcGtVOv5VDB3M2xHR3dxU3NvRUJfMzItbEhKdmhhb2ZJ/view?usp=drivesdk
Essentially, it will just produce the Code again where the table should be. I've tried naming the original table "Table1" and "Occupancy". My apologies for not being better versed with Power Query and M, but have been trying to get your results for a few days.
Thank you for any assistance!

 

[TrueDat]

GOT IT!!! Thank you for your outside the box solution! I see that M is incredibly powerful and worth learning. I missed loading the 1st results before the 2nd query. Good now. For the time intervals where no customers are present, I would need to produce a zero. Do you think INDEX MATCH would be the best solution to complete a full 24 hour table; at 15 minute intervals, that would include zeros where no customers existed?
THANK YOU!!!