Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Zubair_Muhammad
Community Champion
Community Champion

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


Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

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


Regards
Zubair

Please try my custom visuals

Anonymous
Not applicable

@Zubair_Muhammad you, sir, are a genious. 

@Anonymous

 

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

 

countacrossrows.png


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@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

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

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.