Count syntax in query editor

[MB1]

Hi everyone,

Some background – My dataset is coming from a connection to a share point location. 

The table has heaps of columns but the ones I am interested in are 6 columns called Error Type column 1 through to Error type column 6. The data in these columns is text, I want to count the number of times a specific string of text appears in these columns, and sum this total.

For example,

if the word “EIC” appeared in column 3, the answer would be 1.

if the word “EIC” appeared in column 1 and column 2, the answer would be 2.

I was able to do this easily enough by creating a new column in the table with the following formula:

EIC = if([Error type column 1]="EIC",1,0)+if([Error type column 2]="EIC",1,0)+if([Error type column 3]="EIC",1,0)+if([Error type column 4]="EIC",1,0)+if([Error type column 5]="EIC",1,0)+if([Error type column 6]="EIC",1,0)

But I’ve since realised that I need to do this in the query editor, as I need to then go on to unpivot the data to get it in the structure I need. This is where I am hitting the problem, I am unable to find any syntax that does this in the query editor!!

I cant seem to sum several IF statements in the one query, and I can’t find a count syntax that works. I have 17 error types over the 6 columns, so I cant break it down step by step as i'd then be introducing over 100 additional columns to my table.

Any help would be greatly appreciated!

Thanks.

MB1.

[Datatouille]

Hi @MB1

You can leverage M List Functions.

Try this:

Let

  Source = "YourSource",

  Check = Table.AddColumn(Source, "Check", each List.Count( List.Select( { [Col1], [Col2], [Col3], [Col4], [Col5], [Col6] } , each _ = "EIC")))

in

  Check

[MB1 · Accepted solution]

Hi @Datatouille

Thanks for your suggestion. I couldnt get it to work properly (I am an M novice) but I found a work around. I created a 2nd table linked to the same sharepoint and deleted all the columns except the 6 error type columns and a unique identifier column, called Call ID. I then unpivoted the 6 error type columns which put them all into one, and created a join on Call ID with my original sharepoint table that contains all the additional information such as name, team etc.

This fixed my issue as it removed my need to count the text within the query editor, and because now its in one column it slots in nicely in the charts and automatically counts the occurrences of each group, such as EIC (used in my example), and the join on Call ID allows me to tie it back to the person or any of the other details in the original dataset. It all appears to be working, so far so good anyway!