Forum Discussion

OscarSuarez10's avatar
OscarSuarez10
Helper III
7 years ago
Solved

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

 

YEAROBJECTOUTPUT INDEX
1990A1
1991A2
1992A3
1990B1
1991B2
1992B3
1990C1
1991C2
1992C3

2 Replies

  • Anonymous's avatar
    Anonymous
    Not 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"