Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to 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.
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
16 | |
15 | |
12 |