Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |