Log In
Register
Home
New Member
April 18, 2023
Offline
Hello, I reviewed the article on grouped running totals using Power Query but have a use case that would expand on this. I have data (healthcare, so unfortunately I cannot share) in the format of:
Account# (int64) / Recorded Instance (datetime)/ Property (string)
There are over a thousand accounts, several times that in recorded instances, and there are four distinct properties.
I need to creating a running count of occurrences of
Each property, for each account, for the 24 hours preceding the row being evaluated.
I have tried to customize the function from the above article without success. Is this even possible?
(I did attach data with some rows of generic data in the same style)
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Brandon,
I think you can use a grouping on the columns you want, then just add an index column to the grouped table.
Because the Recorded column has hh:mm:ss, I think you should group only by hour, otherwise you will not get relevant results, as there are probably no entries in the same second. That's the reason I extracted the hour only, and added a date 1 day before the Recorded date.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Hour", each Time.Hour(Time.From([Recorded]))),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Account", Int64.Type}, {"Recorded", type datetime}, {"Event Type", type text}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each Date.AddDays(DateTime.Date([Recorded]),-1), type date),
#"Sorted Rows" = Table.Sort(#"Inserted Date",{{"Account", Order.Ascending}, {"Recorded", Order.Ascending}, {"Event Type", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Account", "Date", "Hour", "Event Type"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1,1) }}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Account", "Recorded", "Event Type", "Index"}, {"Account", "Recorded", "Event Type", "Index"})
in
#"Expanded Count"
1 Guest(s)
