Count rows with conditions in Power Query

[Cihan_G]

Hey Guys,

I need help counting the occurance of a value within a specific key value.

The data I have looks something like this:

Key	Parameter within key	Value
A	aaa	1
A	aab	2
A	aac	2
A	aad	4
B	bba	1
B	bbb	2
B	bbc	3
B	bbd	4

What I need would be this:

Key	# of 2's
A	2
B	1

I already made this work using DAX, but I have to implement this in the query editor using Power Query / M Language, because I have to merge the resulting table to another table and calculate more columns using "# of 2's"

Can someone please help me out with this?

Thanks in advance and best regards,

Cihan

[v-eqin-msft]

Hi @Cihan_G ,

you could follow these steps:

duplicate the table --> Filter and Groupby respectively--> finally merge the two tables as shown below:

But it will lead to an increase in memory/cpu to a certain extent since I have create a new table.And normally memory consumption will reduce the performance and brings heavier load to the model. 

So you could disable "Enable load" option.

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

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

[Cihan_G]

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

[Cihan_G]

Hey Phil,

thank you for your fast reply! 

This worked so far, but how can I now add more columns, based on other filters to that?

Key   //   # of 2's  //  # of 3's and '4s

A      //       2        //            1

B      //       1        //            2

Can you please help me out with this aswell?

Thank you very much,

Cihan

[PhilipTreacy · Accepted solution]

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