Explode Table by DateTime

[Cam Grant]

Hello,

I have a partial solution from Marcel Beug on Mr. Excel Forum (2017 post),
to a problem similar to mine.

https://www.mrexcel.com/forum/power-bi/1025952-occupancy-problem.html

I need to measure Website traffic for each [PageNumber] visited. In an
excel file, I have a table ("Occupancy") with Columns:[NameID],
[TimeLoggedIn],[TimeLoggedOut],[Page Number]. At any given hour, I would
like to know how many people are on each [PageNumber]. The following code
(credit to Marcel Beug), produces correct output when run by the minute
(changing "24*Number.From" etc. to
"1440*Number.From",etc.). However, when run
by the hour, it produces counts that are not within any hour blocks of time.

Please see attached.

Thank you in advance to anyone who can solve!

[COLOR=#222222][FONT=Verdana]let
    Source = Patron_Occupancy,

    #"Added Custom" = Table.AddColumn(Source,
"Time", each
{Number.RoundUp(24*Number.From([TimeLoggedIn]),0)..Number.RoundDown(24*Number.From([TimeLoggedOut]),0)}),

    #"Expanded Time" =
Table.ExpandListColumn(#"Added Custom", "Time"),

    #"Transformed To DateTime" = Table.TransformColumns(#"Expanded
Time", {{"Time", each DateTime.From(_/24), type datetime}}),

    #"Added Date" = Table.AddColumn(#"Transformed
To DateTime", "Date", each DateTime.Date([Time] -
#duration(0,0,0,0)), type date),

    #"Extracted Time" = Table.TransformColumns(#"Added
Date",{{"Time", DateTime.Time}}),

    #"Grouped Rows" = Table.Group(#"Extracted
Time", {"PageNumber", "Time", "Date"},
{{"Count", each Table.RowCount(_), type number}}),

    #"Changed Type" =
Table.TransformColumnTypes(#"Grouped Rows",{{"PageNumber",
type text}}),

    #"Pivoted Column" = Table.Pivot(#"Changed
Type", List.Sort(List.Distinct(#"Changed Type"[PageNumber])),
"PageNumber", "Count", List.Sum),

    #"Added All WebSites" =
Table.AddColumn(#"Pivoted Column", "All WebSites", each
List.Sum(List.Skip(Record.FieldValues(_),2))),

    #"Reordered Columns" =
Table.ReorderColumns(#"Added All WebSites",{"Date",
"Time"})

[COLOR=#222222][FONT=Verdana]in
    #"Reordered Columns"
[/FONT][/COLOR][/FONT][/COLOR]

 

[p45cal]

9 months is too late I know, but…
I think only one little change is needed:

from:
#"Added Custom" = Table.AddColumn(Source, "Time", each {Number.Round[COLOR=#FF0000]Up[/COLOR](24*Number.From([TimeLoggedIn]),0)..Number.RoundDown(24*Number.From([TimeLoggedOut]),0)}),
to:
#"Added Custom" = Table.AddColumn(Source, "Time", each {Number.Round[COLOR=#FF0000]Down[/COLOR](24*Number.From([TimeLoggedIn]),0)..Number.RoundDown(24*Number.From([TimeLoggedOut]),0)}),

which when effected changes the chart from:

to: