Cam Grant
New member
- Joined
- Oct 15, 2019
- Messages
- 1
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Office 365 Pro
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!
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!
Code:
[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]
