cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heytherejem Member
Member

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

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. SampleData.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

Hi @heytherejem

 

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

5 REPLIES 5
Super User
Super User

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

Hi @heytherejem

 

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

Super User
Super User

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

@heytherejem

 

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

 

countacrossrows.png

heytherejem Member
Member

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

@Zubair_Muhammad you, sir, are a genious. 

heytherejem Member
Member

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

@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]

 

 

Excel.png

Highlighted
Super User
Super User

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

@heytherejem

 

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]