cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Count syntax in query editor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Count syntax in query editor

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!

View solution in original post

2 REPLIES 2
Highlighted

Re: Count syntax in query editor

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

Highlighted
Frequent Visitor

Re: Count syntax in query editor

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!

View solution in original post

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors