Values based on number of reoccurence in another column

[haichenhuang]

Hi there. I need to create another column "Value" from data in collumn "Year":

What it needs to do is count the number of times the Year value is duplicated, and list down which number of occurence it is. The data in "Values" in the screenshot above is manual entry and I need a way of powerBI doing this automatically. Thanks.

[Anonymous · Accepted solution]

Hi @haichenhuang ,

Besides that the method as suggested by @Ashish_Mathur , you can achieve it by DAX. If there is one date field (as shown below) or index field that uniquely identify each record in your table , create the following calculated column to get the value. You can find the details in the attachment.

Value = 
CALCULATE (
    COUNT ( 'Table'[Date] ),
    FILTER (
        'Table',
        'Table'[Year] = EARLIER ( 'Table'[Year] )
            && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
    )
)

Otherwise, you need to add an index column in Power Query Editor first. If so, the method from @Ashish_Mathur  is better....

 #"Added Index" = Table.AddIndexColumn(#"Change Type", "Index", 1, 1, Int64.Type)

Best Regards

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Partition = Table.Group(Source, {"Year"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Index"})
in
    #"Expanded Partition"

Hope this helps.