Forum Discussion
Count rows with conditions in Power Query
Hi Cihan_G
Download this sample PBIX with code
Use this in Power Query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpMTASShkqxOjB+EpA0QuIno/FTgKQJmO8EZCUlIfRD+Aj9ED5IvzESH6o/FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, #"Parameter within key" = _t, Value = _t]), #"Filtered Rows" = Table.SelectRows(Source, each ([Value] = "2")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Key"}, {{"Count", each Table.RowCount(_), Int64.Type}}) in #"Grouped Rows"Regards
Phil
Hi Cihan_G
Can you please be clear about the desired result. What about counting 1's? Do you want to add the count of 3's and 4's togeher?
Bear in mind that the code may specifically be looking for the values 1, 2 3, etc so when you come to use it on real data not may not work. Can you supply some actual data?
Regards
Phil
Hey Phil,
sorry for beeing unclear.
The actual data also contains these values, so your code works just fine.
I want to add the counts (in the sample for 3's and 4's) together for the final table. The table, i need for the acual data will look like this:
Key // GREEN // YELLOW // RED
A // ... // ... // ...
B // ... // ... // ...
...
Where GREEN counts 1's, 2's & 3's YELLOW counts 4's and RED counts 5's ,6's, 7's
Other than that, the actual data looks just like the example, I provided.
Best regards,
Cihan