Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
RUPSY777
Helper II
Helper II

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
1 ACCEPTED SOLUTION
AnkitBI
Solution Sage
Solution Sage

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.

View solution in original post

2 REPLIES 2
AnkitBI
Solution Sage
Solution Sage

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
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.