My data contains 13 columns that contain one of 3 codes, or may be blank. I would like to create a new column in my query that counts the occurrence of one of these codes. What is the formula in Power Query to add such a custom column? I know how to do countif in a spreadsheet See attached), but not how to do this in a query using M.
Thank you -
Cheryl
Hi Cheryl,
You want to summarise each row? I'm asking because the rows themselves don't have any identifier. Should there be student names in your data as well?
If so, the data should be unpivoted and then you can use a PivotTable to do the count. Unless you need those columns in PQ for some other reason, if so we need the full picture of what you're trying to acheive.
Mynda
Each row in my example represents a single student and the columns represent which "weeks or segments" of a class they completed. So not only do we want to know which ones they are enrolled in now (E), but also the total number they are enrolled in. The same is true for the code P which means pass. Therefore, I would want to summarize each row.
Ok, that's what I thought but with the missing student column is wasn't clear.
All you need to do is unpivot the data and then use a PivotTable to summarise it. See file attached.
Mynda
Thank you - this works - however, it is important to be able to see which of the segments (1-12) the student received an E or P in, and a count of each of those, plus a total in one table, that is why I wanted to use Count-If in the Query somehow. 
I am able to add my formula to the resulting table wanted to do use a Query to use in the multiple data sets that I do this in. Attached is a sample of what I would like this to look like (using PowerQuery rather than in Excel) if possible
Thank you,
Cheryl
Like this?
Yes - that is the result I wanted, but requires more queries than I anticipated. I think I will stick with my old method of creating the countIF in my table and not in the query. Thank you for your help.
Cheryl
