Keep the first (newest) records from a powershell dataset based on one column's value

Question

I have a dataset that looks like this:

Starting situation:

MsgNr	State	DateTime	Ms	TimeDiff	Typename	Type	Text2	Text1	AlarmTag
800292	2	2/16/2024 9:05:57 AM	692	6344	Error	1	=80+80.25.08-B208.6	Photo eye blocked	12231
800249	2	2/16/2024 9:05:57 AM	692	68	Error	1	=80+80.11.07-B118.5	Photo eye blocked	12175
800226	2	2/16/2024 9:05:57 AM	692	3882	Error	1	=80+80.03.08-B104.6	Photo eye blocked	12152
800249	1	2/16/2024 9:04:48 AM	773	0	Error	1	=80+80.11.07-B118.5	Photo eye blocked	12175
93004869	2	2/16/2024 8:52:29 AM	685	154	Error	1	IFZ-0030.14	Infeed must be emptied	21924
93004794	2	2/16/2024 8:52:29 AM	678	154	Error	1	BJ-0030.14.09	Speed difference	21908
805052	2	2/16/2024 8:52:15 AM	395	2	Maintenance	2	=80+80.11.07-M1	Manual mode	11586
805051	2	2/16/2024 8:52:15 AM	395	2	Maintenance	2	=80+80.11.06-M1	Manual mode	11585
805049	2	2/16/2024 8:52:15 AM	395	2	Maintenance	2	=80+80.11.04-M1	Manual mode	11583
805048	2	2/16/2024 8:52:15 AM	395	2	Maintenance	2	=80+80.11.03-M1	Manual mode	11582
805050	2	2/16/2024 8:52:15 AM	395	2	Maintenance	2	=80+80.11.05-M1	Manual mode	11584
805052	1	2/16/2024 8:52:13 AM	210	0	Maintenance	2	=80+80.11.07-M1	Manual mode	11586
805051	1	2/16/2024 8:52:13 AM	210	0	Maintenance	2	=80+80.11.06-M1	Manual mode	11585
805050	1	2/16/2024 8:52:13 AM	210	0	Maintenance	2	=80+80.11.05-M1	Manual mode	11584
805049	1	2/16/2024 8:52:13 AM	210	0	Maintenance	2	=80+80.11.04-M1	Manual mode	11583
805048	1	2/16/2024 8:52:13 AM	210	0	Maintenance	2	=80+80.11.03-M1	Manual mode	11582
93004869	1	2/16/2024 8:49:54 AM	845	0	Error	1	IFZ-0030.14	Infeed must be emptied	21924
93004794	1	2/16/2024 8:49:54 AM	845	0	Error	1	BJ-0030.14.09	Speed difference	21908
720669	2	2/16/2024 8:20:27 AM	470	40	Error	1	=72+72.04.71-WE1	General error weighing scale	8902
720669	1	2/16/2024 8:19:46 AM	748	0	Error	1	=72+72.04.71-WE1	General error weighing scale	8902

I want to keep the latest "State" of the "MsgNr" values. So my script needs to look at all the rows from newest to oldest ("DateTime") and keep only the ones where "MsgNr" is unique.

Result after Sort-Object -Property MsgNr -Unique:

MsgNr	State	DateTime	Ms	TimeDiff	Typename	Type	Text2	Text1	AlarmTag
720669	1	2/16/2024 8:19:46	748	0	Error	1	=72+72.04.71-WE1	General error weighing scale	8902
800226	2	2/16/2024 9:05:57	692	3882	Error	1	=80+80.03.08-B104.6	Photo eye blocked	12152
800249	2	2/16/2024 9:05:57	692	68	Error	1	=80+80.11.07-B118.5	Photo eye blocked	12175
800292	2	2/16/2024 9:05:57	692	6344	Error	1	=80+80.25.08-B208.6	Photo eye blocked	12231
805048	1	2/16/2024 8:52:13	210	0	Maintenance	2	=80+80.11.03-M1	Manual mode	11582
805049	2	2/16/2024 8:52:15	395	2	Maintenance	2	=80+80.11.04-M1	Manual mode	11583
805050	1	2/16/2024 8:52:13	210	0	Maintenance	2	=80+80.11.05-M1	Manual mode	11584
805051	1	2/16/2024 8:52:13	210	0	Maintenance	2	=80+80.11.06-M1	Manual mode	11585
805052	2	2/16/2024 8:52:15	395	2	Maintenance	2	=80+80.11.07-M1	Manual mode	11586
93004794	2	2/16/2024 8:52:29	678	154	Error	1	BJ-0030.14.09	Speed difference	21908
93004869	1	2/16/2024 8:49:54	845	0	Error	1	IFZ-0030.14	Infeed must be emptied	21924

My desired result would be:

MsgNr	State	DateTime	Ms	TimeDiff	Typename	Type	Text2	Text1	AlarmTag
800292	2	2/16/2024 9:05:57	692	6344	Error	1	=80+80.25.08-B208.6	Photo eye blocked	12231
800249	2	2/16/2024 9:05:57	692	68	Error	1	=80+80.11.07-B118.5	Photo eye blocked	12175
800226	2	2/16/2024 9:05:57	692	3882	Error	1	=80+80.03.08-B104.6	Photo eye blocked	12152
93004869	2	2/16/2024 8:52:29	685	154	Error	1	IFZ-0030.14	Infeed must be emptied	21924
93004794	2	2/16/2024 8:52:29	678	154	Error	1	BJ-0030.14.09	Speed difference	21908
805052	2	2/16/2024 8:52:15	395	2	Maintenance	2	=80+80.11.07-M1	Manual mode	11586
805051	2	2/16/2024 8:52:15	395	2	Maintenance	2	=80+80.11.06-M1	Manual mode	11585
805049	2	2/16/2024 8:52:15	395	2	Maintenance	2	=80+80.11.04-M1	Manual mode	11583
805048	2	2/16/2024 8:52:15	395	2	Maintenance	2	=80+80.11.03-M1	Manual mode	11582
805050	2	2/16/2024 8:52:15	395	2	Maintenance	2	=80+80.11.05-M1	Manual mode	11584
720669	2	2/16/2024 8:20:27	470	40	Error	1	=72+72.04.71-WE1	General error weighing scale	8902

The powershell script looks like this:

$datasetAlg.Tables[0] |
    select -first 20|
    select MsgNr,State,DateTime,Ms,TimeDiff,Typename,Type,Text2,Text1,AlarmTag |
    Sort-Object -Property DateTime |
    Sort-Object -Property MsgNr -Unique |
    Export-CSV "D:\WinCC_Active_Messages_result.csv"

I need to have only the rows that contain the latest "State" (and other row contents) of all the "MsgNr" values. So to get the latest values I'm sorting them from newest to oldest. Than I want to keep all the rows where the first encounter of a "MsgNr" value is unique. That way I get the latest "State" (and other row contents) of all the "MsgNr" values.

The MsgNr values must stay sorted by DateTime and the result should have the first record of every unique MsgNr listed in that order. (So the latest records first)

Sort-Object -Property MsgNr -Unique does not seem to respect the order I sorted on with Sort-Object -Property DateTime |

Answer 1

Doing this Sort-Object -Property DateTime | Sort-Object -Property MsgNr will first sort the collection using DateTime property, then, re-sort the whole result against the property MsgNr. In the end, it would be equivalent to just doing Sort-Object -Property MsgNr.

If you want to sort something by multiple properties, you have to do it in the same Sort-Object cmdlet, separating the properties using a comma ,. For descending order, you can use -Descending :

$datasetAlg.Tables[0] |
    select -first 20|
    select MsgNr,State,DateTime,Ms,TimeDiff,Typename,Type,Text2,Text1,AlarmTag |
    Sort-Object -Property DateTime,MsgNr -Unique -Descending |
    Export-CSV "D:\WinCC_Active_Messages_result.csv"

More informations can be found in documentation

Answer 2

Try this.

$datasetAlg.Tables[0] |
    Select-Object -Unique -Property MsgNr | 
    Foreach-Object {
        $datasetAlg.Tables[0]  | 
            Where-Object -Property MsgNr -eq $_.MsgNr |
            Sort-Object -Property DateTime -Descending | 
            Select-Object -First 1 
    }

Answer 3

I Managed to get it working like this:

$Exportdataset = $datasetAlg.Tables[0] | 
  Group-Object -Property MsgNr |
      ForEach-Object {
        $LatestMsgNrStates = $_.Group |
          Sort-Object -Property DateTime, Ms -Descending |
            Select-Object -First 1
              [pscustomobject]@{
                MsgNr = $_.Name
                State = $LatestMsgNrStates.State
                DateTime = $LatestMsgNrStates.DateTime.AddHours(1)
                Ms = $LatestMsgNrStates.Ms
                TimeDiff = $LatestMsgNrStates.TimeDiff
                Typename = $LatestMsgNrStates.Typename
                Type = $LatestMsgNrStates.Type
                Text2 = $LatestMsgNrStates.Text2
                Text1 = $LatestMsgNrStates.Text1
                AlarmTag = $LatestMsgNrStates.AlarmTag
              }
      } | Where-Object State -lt 2 |
        Export-CSV -Delimiter ';' -NoTypeInformation "D:\WinCC_Active_Scada_Messages.csv"