In power bi - the first column is getting repeated value then count +1

[RUPSY777]

hi All,

My power bi dataset has a column where repeated value occurs and each time the same value will occur the count should get increased by 1 ( if Column A=SAME VALUE AS PREIOUS ROWS THEN COUNT= COUNT+1 ELSE COUNT)- how to get the count column in custom query or dax?

Column a	Count
tata	1
mdc	1
tata	2
tata	3
mdc	2

[AnkitBI · Accepted solution]

Hi @RUPSY777 

You can do directly in PQ as beow.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkksSVSK1YlWyk1JBtNwAVSZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column a" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column a", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column a"}, {{"AllRows", each Table.AddIndexColumn(_,"Count",1,1), type table [Column a=text,Count = number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Count"}, {"Count"})
in
    #"Expanded AllRows"

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[HotChilli]

If you add an index column in Power Query (from 1).

Then in Power Bi create a calculated column with this DAX

RunningCount = VAR _value = Table[Column a]
                VAR _index = Table[Index]
            
RETURN
    CALCULATE(COUNT(Table[Column a]), 
        FILTER(Table,
            Table[Column a] = _value &&
            Table[Index] <= _index))

You will substitute in your table and column names.