Mark first occurrence of a value in a column

[sromondas]

Hi,

I have a table that is as follows. The table is first sorted by "Class" (asc) and then by Date (asc)

Class	Date
A	1 Jan 2023
A	2 Jan 2023
A	3 Jan 2023
B	1 Jan 2023
B	1 Feb 2023
B	3 Feb 2023
C	4 May 2023
C	5 May 2023
C	20 May 2023
C	21 May 2023

What I want to do is, for the first row of every "Class", I want to mark it as a "Yes" in a new column (NewColumn) as shown below:

Class	Date	NewColumn
A	1 Jan 2023	Yes
A	2 Jan 2023
A	3 Jan 2023
B	1 Jan 2023	Yes
B	1 Feb 2023
B	3 Feb 2023
C	4 May 2023	Yes
C	5 May 2023
C	20 May 2023
C	21 May 2023

I'd like to perform this during the transformation steps using PowerQuery. Appreciate any and all the help on this. Thanks!

[BIswajit_Das]

Hello @sromondas You can also use a calculated column to acheive what you need
I.e

NewColumn =

VAR _class =

    CALCULATE(

        MIN('XOX2'[Date]),

        ALLEXCEPT('XOX2', 'XOX2'[Class])

    )

RETURN

    IF('XOX2'[Date] = _class, "YES", BLANK())

[Dhairya]

Hey @sromondas 
Please paste this below-mentioned code in your Advanced Query Editor, you will get the expected output

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJU8ErMUzAyMDJWitWBCBlhChmjCjlhaoQIuaUmoQoZowo5A4VMFHwTK1GFTDGFjAywiBkiicUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Class = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Class", type text}, {"Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Class", Order.Ascending}, {"Date", Order.Ascending}}),
shiftedList = {null} & List.RemoveLastN(#"Sorted Rows"[Class],1),
custom1 = Table.ToColumns(Source) & {shiftedList},
custom2 = Table.FromColumns(custom1,Table.ColumnNames(Source) & {"Next Row"}),
#"Added Custom" = Table.AddColumn(custom2, "First Occurence of Class?", each if [Class] = [Next Row] then "No" else "Yes"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Next Row"})
in 
#"Removed Columns"

If this helps you please mark my solution as accepted so that others can find this quickly when they encounter similar issue. Thank you!