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

2021-03-16T14:59:14.283+00:00

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.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,525 questions
Microsoft Intune Enrollment
Microsoft Intune Enrollment
Microsoft Intune: A Microsoft cloud-based management solution that offers mobile device management, mobile application management, and PC management capabilities.Enrollment: The process of requesting, receiving, and installing a certificate.
1,312 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Lz._ 8,991 Reputation points
    2021-03-16T16:09:26.23+00:00

    @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

    0 comments No comments

  2. Lz._ 8,991 Reputation points
    2021-03-16T17:11:53.237+00:00

    @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  
    
    0 comments No comments

  3. Lu Dai-MSFT 28,366 Reputation points
    2021-03-17T05:26:31.767+00:00

    @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.

    0 comments No comments