How do you generate and arrange the latest records in an Excel table containing GUID numbers

I sought a personalized approach for managing students across various classes, intending to generate unique IDs based on the class distinctions.

To ensure this, when the 'Class' column indicates 'JSS1,' 'JSS2,' or 'JSS3,' the 'GUID' column (representing unique identifiers) is to initiate from 5. Conversely, if the 'Class' column contains 'SS1,' 'SS2,' or 'SS3,' the 'GUID' column should commence with 2.ALL containing 8digits Guid numbers

Regarding the transformation table, for a student like WHITE, CYNTHIA, whose record appears twice, the goal is to maintain a consistent GUID across different years and classes while retaining only the latest record. How can this be achieved?

BEFORE TRANSFORMATION:

StudentID	NAME	YEAR	CLASS
1	FELICIA	2020/2021	JSS1
2	CYNTHIA	2020/2021	JSS1
3	WHITE	2020/2021	SS1
3	WHITE	2021/2022	SS2
2	CYNTHIA	2021/2022	JSS2
4	BOMBU	2020/2021	SS2

AFTER TRANSFORMATION:

StudentID	GUID	NAME	YEAR	CLASS
1	50234678	FELICIA	2020/2021	JSS1
2	51234567	CYNTHIA	2021/2022	JSS2
3	20912341	WHITE	2021/2022	SS2
4	20112342	BOMBU	2020/2021	SS2

Thank you.

Solution

You can sort on Year descending, then buffer the table, then remove duplicates on Student Id. This will give you the latest record of each Student.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StudentID", Int64.Type}, {"NAME", type text}, {"YEAR", type text}, {"CLASS", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "GUID", each (if Text.StartsWith([CLASS], "JS") then "5" else "2") & Text.PadStart(Text.From([StudentID]), 7, "0")),
    #"Sorted Rows1" = Table.Buffer(Table.Sort(#"Added Custom",{{"YEAR", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows1", {"StudentID"}),
    #"Sorted Rows2" = Table.Sort(#"Removed Duplicates",{{"StudentID", Order.Ascending}})
in
    #"Sorted Rows2"

If you want a random 7 digit unique id for each student then create a new query with:

let
    Source = List.Transform({1..99999}, each 
      Text.Combine( 
        List.Transform( {1..7},
          each Text.From( Int64.From( Number.RandomBetween( 0, 9 )))
        )
      )
    ),
    #"Removed Duplicates" = List.Distinct(Source),
    #"Converted to Table" = Table.FromList(#"Removed Duplicates", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

// update the 99999 above to twice the number of total students expected.

Then merge this into your Student table on the Student ID column and the Index column. Then after the merge, pre-fix "5" or "2" etc....