Results 1 to 1 of 1

Thread: Explode Table by DateTime

  1. #1
    Neophyte Cam Grant's Avatar
    Join Date
    Oct 2019
    Posts
    1
    Articles
    0
    Excel Version
    Office 365 Pro

    Explode Table by DateTime



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!


    Code:
    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"})
    
    in
        #"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
  •