Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Hi @OscarSuarez10 ,
Or we can create an index column by DAX like this.
index = RANKX(VALUES(Query1[YEAR]),Query1[YEAR],,ASC,Dense)
Hi @OscarSuarez10 ,
Or we can create an index column by DAX like this.
index = RANKX(VALUES(Query1[YEAR]),Query1[YEAR],,ASC,Dense)
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.