topic Re: Count rows containing value in Power Query

Count rows containing value

Anonymous — Thu, 05 May 2022 15:14:06 GMT

Hi,

I'm trying to count the rows containing specific values.

I've tried this:

Table.FindText(PQPFocus[Vekt utenfor norm],2)

... but I'm getting a cyclic error.

My column [Vekt utenfor norm] contains the values 5, 2, norm, -2, -5.
I need to know how many rows contain the value 5, how many contains 2 and how many that contains the sum of those.

Any suggestions? 🙂

Re: Count rows containing value

Querynalyst — Thu, 05 May 2022 15:38:01 GMT

Hello,

I would suggest you to use DAX instead of Power Query for your need.

Example:

NumberOfTwo = CALCULATE( COUNT(PQPFocus[Vekt utenfor norm]), PQPFocus[Vekt utenfor norm] = 2 )

Same thing for 5 etc.

I hope this will answer your question.

Re: Count rows containing value

Anonymous — Thu, 05 May 2022 16:02:20 GMT

I used countrows, but I need it to give me the number in each row, not just mark the numbers with 1...

Re: Count rows containing value

Vijay_A_Verma — Thu, 05 May 2022 17:08:19 GMT

For counting 2, use following (Replace Previous Step with your previous step)

= List.Count(List.FindText(#"Previous Step"[Vekt utenfor norm],"2"))

For any other value say 5, replace 2 with 5.

Re: Count rows containing value

Crangel — Tue, 30 May 2023 15:53:11 GMT

I tried applying this calc to my issue, but it didn't work, can you tell me what i did wrong? Or maybe I'm using it wrong. I have a survey table (Form1) with column [Language]. I'm trying to create a visual table that contains a column for where the survey originated [Center], and then column English with the total of English language surveys submitted for that center, and again with a column for Spanish. I thought i could just create a measure to count the occurange of English and add that to my table but the calc below does not work. The rest of the calc is valid, but it doesnt recognize "english". Maybe if I transform it to a numeric value? Thanks so much for your help!

English = CALCULATE(COUNT(Form1[Language]),Form1[Language]="English")