Forum Discussion
OscarSuarez10
7 years agoHelper III
Repeated Index Per Year
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 |
Hi OscarSuarez10 ,
Or we can create an index column by DAX like this.
index = RANKX(VALUES(Query1[YEAR]),Query1[YEAR],,ASC,Dense)
2 Replies
- AnonymousNot applicable
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" - v-frfei-msftCommunity Support
Hi OscarSuarez10 ,
Or we can create an index column by DAX like this.
index = RANKX(VALUES(Query1[YEAR]),Query1[YEAR],,ASC,Dense)