Using M Query in Power Query Editor to Count across multiple columns

[Anonymous]

Afternoon,

Not sure if this is possible - I have about 20 columns in a dataset which contain either the value: Pass, Fail or N/A

I want to create a 'Total checked' column which will count across all 20 columns and sum the instances of Pass and Fail, ignoring N/A. 

So in a single row, if 'Pass' in 2 columns, 'Fail' in 2 columns, 'N/A' in 2 columns, the new colum would result a 4. 

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

You can do like this

First Step

Add an index column (From 0)

Final Step

add this custom column

List.Count(
List.Select(
Record.ToList(AddedIndex{[Index]}),
each _ ="Pass" or _ ="Fail")
            )

AddedIndex refers to previousstep in your M code

[goofydoggy]

Hi @Zubair_Muhammad

Do you mind advising to my query, please?
https://community.powerbi.com/t5/Desktop/How-to-create-Clustered-Column-Chart-without-counting-Blank...

Thank you.

[Anonymous]

@Zubair_Muhammad you, sir, are a genious. 

[Zubair_Muhammad]

@Anonymous

Please see attached file's Query Editor with some sample data

[Anonymous]

@Zubair_Muhammad I have a second challenge for you.

Below is an excel equivalent of what I want to do in Power Query Editor with a new Custom Column - I want to create a [Fail Ratio] which takes the sum of Fails from the Total Checks to give a percentage. Easy.

Did that using [Total Fail]/[Total Checks] 

But there are two elements - [Patient Name] and [Person to Pay] where if they fail, the [Fail Ratio] should be a complete fail, irrespective of whether these are only these two fails. 

So I need the measure to give complete fail if [Patient Name] or [Person to Pay]="Fail", else [Total Fail]/[Total Checks]

[Zubair_Muhammad]

@Anonymous

Sorry I missed your last reply

You can use a custom column like

=if
List.Contains({[Patient Name],[Person To Pay]},"Fail")
then
0
else
[Total Fail]/[Total Checks]