Results 1 to 2 of 2

Thread: Explode Table by DateTime

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Neophyte Cam Grant's Avatar
    Join Date
    Oct 2019
    Excel Version
    Office 365 Pro

    Explode Table by DateTime


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

    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!

        Source = Patron_Occupancy,
        #"Added Custom" = Table.AddColumn(Source,
    "Time", each
        #"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
        #"Reordered Columns" =
    Table.ReorderColumns(#"Added All WebSites",{"Date",
        #"Reordered Columns"

    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts