How can I display the latest date of a device that has multiple dates associated with it ?

Question

I am extracting data from Intune. Our mobile devices. have multiple "LastSync" date/time records for a single serial number. I need to display in Power BI a serial number of the device and display the latest date/time it ran a sync to Intune (LastSync). It is currently displaying the same serial number multiple times because Intune has "stale" records of older syncs. I want to display 1 serial number to its latest date/time only with Power BI.

Answer 1

@Gonzalez Jr, Cruz A (Contractor)

Within Power BI, Edit the query > Advanced Editor. Now, assuming the query STEP that corresponds to the picture you uploaded is the #"Changed Type" step (otherwise adapt), below that step replace everything with:

    GroupedSerialN = Table.Group(#"Changed Type", {"Serial Number"},  
        {"SN_Table", each  
            Table.FirstN(  
                Table.Sort(_, {<!-- -->{"Last Sync",Order.Descending}}),  
                1  
            ),  
            type table  
        }  
    ),  
    CombinedTables = Table.Combine(GroupedSerialN[SN_Table])  
in  
    CombinedTables  

NB (EDITED) I assumed [Last Sync] isn't ordered/sorted by [Serial Number]. If it is let me know as the above Table.Sort would not be required and Table.FirstN would be replaced with Table.LastN

If you're not familiar with Power Query and struggle implementing this post your complete query code

Answer 2

@Gonzalez Jr, Cruz A (Contractor)

If your table is quite large (# records) prefer the following option:

    TableWithLastSync = Table.Group(  
        Table.SelectColumns(#"Changed Type", {"Serial Number","Last Sync"}),  
        {"Serial Number"}, {{"LastSync", each List.Max([Last Sync]), type nullable datetime}}  
    ),  
    MergedTables = Table.NestedJoin(  
        #"Changed Type", {"Serial Number","Last Sync"},  
        TableWithLastSync, {"Serial Number","LastSync"},  
        "TableLastSynch", JoinKind.Inner  
    ),  
    RemovedColumn = Table.RemoveColumns(MergedTables,{"TableLastSynch"})  
in  
    RemovedColumn  

Answer 3

@Gonzalez Jr, Cruz A (Contractor) Thanks for posting in our Q&A.

For this issue, it is more related to Power BI. It is suggested to try to aggregate the records according to the latest sync time in Visualization. We can refer to the following article:
https://learn.microsoft.com/en-us/power-bi/create-reports/service-aggregates#types-of-data

To get more effective help, please post in the Power BI community forum:
https://community.powerbi.com/

Thanks for understanding and have a nice day.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.