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= COUNT+1 ELSE COUNT)- how to get the count column in custom query or dax?

 

  
Column aCount
tata1
mdc1
tata2
tata3
mdc2
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.

2 Replies

  • AnkitBI's avatar
    AnkitBI
    Icon for Solution Sage rankSolution Sage
    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.