question

GonzalezJrCruzAContractor-3237 avatar image
0 Votes"
GonzalezJrCruzAContractor-3237 asked LuDaiMSFT-0289 answered

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

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.

power-query-not-supportedmem-intune-enrollment
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@GonzalezJrCruzAContractor-3237. Could you upload a picture of the table (a few rows only) containing the extracted data (from Intune), as you see it in Power BI?

0 Votes 0 ·

78323-lsstsync-issue.png



Data from Intune

0 Votes 0 ·
lsstsync-issue.png (7.9 KiB)

78314-image.png




0 Votes 0 ·
image.png (16.9 KiB)
Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 edited

@GonzalezJrCruzAContractor-3237

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

@GonzalezJrCruzAContractor-3237

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LuDaiMSFT-0289 avatar image
0 Votes"
LuDaiMSFT-0289 answered

@GonzalezJrCruzAContractor-3237 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://docs.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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.