Retain last record for day (only) for each subscription_id

[michellepace]

Hello there. I have the below table - I need to retain the yellow rows only. So basically for each subscription_id, keep its last record for the day (because that will tell me if it's currently on or off).  I've been struggling with this for hours, I'd really appreciate your help. 

Input table: this entire table as shown below

Output table: Only the yellow rows

Logic: For each subscription_id, keep its last record for the day

[michellepace · Accepted solution]

hello @smozgur . Thank you very much for your reply. I googled around once I had posted this message and found teh below YouTube link. I used that approach so I could avoid writting code. Thank you very much nonetheless for the above. Posting the solution I used below (by following the youtube video):

let
Source = Subscriptions,
// Followed these steps: https://www.youtube.com/watch?v=QaodJFeX49k
#"Grouped Rows" = Table.Group(Source, {"subscription_id", "date"}, {{"Count", each Table.Max(_, "datetime") }}),
#"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"userId", "controlId", "datetime", "Activity", "subscription_setting", "isOn_flag"}, {"userId", "controlId", "datetime", "Activity", "subscription_setting", "isOn_flag"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"datetime", type datetime}, {"controlId", Int64.Type}, {"date", type date}, {"Activity", type text}, {"subscription_setting", type text}, {"isOn_flag", Int64.Type}, {"userId", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"subscription_id", Order.Ascending}, {"datetime", Order.Ascending}})
in
#"Sorted Rows"

[smozgur]

I can't make sure about the exact table structure, therefore I used sample binary data. You can adapt it to your own source data. Comments are written for each step.

Basically: Create a new date-only column from the datetime column, group by the id and this new column to get the associated daily data for each id, sort those nested tables by the datetime column to get the earliest one at the top, select the first row by using the Table.First() function, and finally extract the nested table for each id.

let
    // Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUNzTSNzIwMlYwtTI0sTIwUHD0BQongqSUYnXQFZlbGZrCFSUBsQGyInO4SaiKDAkpSkZTZGQAUWQBVRQAcxPEOiMgyxi7wxHWoSoyxu4mkCIj3G4yIKQoBU2RkSEOhwOtiwUA", BinaryEncoding.Base64), Compression.Deflate)), type table [subscription_id = Int64.Type, datetime = datetime, Activity = text, isOn_flag = Int64.Type]),
    
    // Create date only column from datetime column, so we can group the table 
    AddDate = Table.AddColumn(Source, "DateOnly", each Date.From(DateTime.From([datetime]))),
    // Group by date column, encapsulate associated data as nested table
    GroupByDate = Table.Group(AddDate, {"subscription_id", "DateOnly"}, {{"Table", each _}}),
    // Sort each nested table by date value - descending to find the earliest date
    FilterLatest = Table.AddColumn(GroupByDate, "Filter", each Table.First(Table.Sort([Table],{"datetime", Order.Descending}))),
    // Expand the nested table - except id column since we have it already
    ExpandFiltered = Table.ExpandRecordColumn(FilterLatest, "Filter", List.RemoveFirstN(Table.ColumnNames(Source), 1)),
    // Remove unnecessary columns
    RemoveColumns = Table.RemoveColumns(ExpandFiltered,{"DateOnly", "Table"})
in
    RemoveColumns

Edit: I didn't understand why you have the second 8/20 row yellow in the screenshot, but I assumed that it is a mistake.

[michellepace]

@smozgur  how do you make such a nice code table? when you select "insert/edit code sample" -- which langauge do you choose? 

[smozgur]

Thanks for posting your solution. Although I can't follow it due to the missing data source/structure, I learned that there is a Table.Max() function from your code. It made me feel uncomfortable creating my own by using Table.Sort and Table.First functions.

Use JavaScript to post M codes. It fits nicely.