Repeated Index Per Year

[OscarSuarez10]

Hi, I want to make an Index for an object that is repeated in some periods like in the following table but I don´t know how

YEAR	OBJECT	OUTPUT INDEX
1990	A	1
1991	A	2
1992	A	3
1990	B	1
1991	B	2
1992	B	3
1990	C	1
1991	C	2
1992	C	3

[v-frfei-msft · Accepted solution]

Hi @OscarSuarez10 ,

Or we can create an index column by DAX like this.

index = RANKX(VALUES(Query1[YEAR]),Query1[YEAR],,ASC,Dense)

[Anonymous]

Take a look at the code below, but basically you Groupy Object and aggregate by All Rows. Then add an index column to that aggregated Table. 

Table.AddIndexColumn([All Data], "Object", 1,1)

Then remove the other columns and expand out

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrS0NFDSUXJUitUBcwyROUbIHJAyJ2RlTsjKnJCVOSMrc0ZWBuTEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [YEAR = _t, OBJECT = _t]),
    #"Grouped Rows" = Table.Group(Source, {"OBJECT"}, {{"All Data", each _, type table [YEAR=number, OBJECT=text, OUTPUT INDEX=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All Data], "Object", 1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"YEAR", "OBJECT", "Object"}, {"YEAR", "OBJECT.1", "Object"})
in
    #"Expanded Custom"