Forum Discussion

RUPSY777's avatar
RUPSY777
Icon for Helper II rankHelper II
6 years ago
Solved

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

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= ...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    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's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
6 years ago

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.