Need help with Power Query

[virendrajadhav1]

Hello,
I am new to Power Query. I am trying to count date columns (just occurances) w.r.t to sales owner. Please see the snap below on how the table looks like.

Here I am trying to as below.

In one column, I need sales_owner distinc values and next to it I need counts of meeting which has been done by the sales_owner.
The ideal output should look like this.

I have tried following M code suggested by one of the member of forum in advanced query but seems like I am still doing something wrong

let
Source = Excel.Workbook(File.Contents("C:\Users\159795\Downloads\Meeting Data for Dashboard - Jun 2023.xlsx"), null, true),
#"SALs Data for Dashboard - Jun 2_Sheet" = Source{[Item="SALs Data for Dashboard - Jun 2",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"SALs Data for Dashboard - Jun 2_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"record_id", Int64.Type}, {"first_name", type text}, {"last_name", type text}, {"company_name", type text}, {"job_title", type text}, {"sales_owner", type text}, {"pspm_owner", type text}, {"ise", type text}, {"inquiry", type text}, {"first_meeting_date", type date}, {"lead_stage", type text}, {"lead_status", type text}, {"next_step", type text}, {"lead_category", type text}, {"lead_sorce_note", type text}, {"account_type", type text}, {"location", type text}, {"email", type text}, {"phone_number", type text}, {"industry", type text}, {"seniority", type text}, {"sales_geo", type text}, {"partnership", type text}, {"offering", type text}, {"month_of_qualified_month", type date}, {"month_of_proposal_submitted", type date}, {"month_of_won", type date}, {"proposal_summary", type text}, {"po_value_doller", Int64.Type}, {"contact_owner", type text}, {"primary_associated_company_id", Int64.Type}, {"associated_company", type text}, {"record_id_1", type text}, {"company_name1", type text}, {"added_to_list_on", type text}, {"company_owner", type text}, {"create_date", type datetime}, {"last_activity_date", type datetime}, {"phone_number_2", type text}, {"city", type text}, {"country", type text}, {"industry1", type text}}),
#"Custom" = Table.Group(#"Changed Type","sales_owner",List.Transform(List.Skip(Table.ColumnNames(#"Changed Type")),(x)=>{x,each List.NonNullCount(Table.Column(_,x))}))

in
#"Changed Type"

Can you please suggest. Thank you in adavance.  

[BA_Pete · Accepted solution]

Hi @virendrajadhav1 ,

Select your [sales_owner] column and go to the Home tab > Group By.

In the Group By dialog, select the 'Advanced' radio button and add new aggregated columns using the Count Rows operation for each of your different date columns.

Once you've done this, edit the Group By code in the formula bar:

// Change each instance of this:

each Table.RowCount(_)

// ...to this:

each List.NonNullCount([ColumnName])

Pete

[virendrajadhav1]

Hey Pete,
Thanks for the answer. This gives the same result in both the columns.
Here is how I did

And the result looks like this

When I use count operation, the column option in "Add Aggresion" is blurred out.

Your thoughts please.
 

[BA_Pete]

Ah, so sorry, I didn't include the last bit of the solution! 😂

I'll update my original answer to include it.

Pete

[virendrajadhav1]

Hey Pete,
Thanks again.
Somehow I am geeting Syntex error. Can you please point what exactly I doing wrong.
Here is the full formula
= Table.Group(#"Changed Type", {"sales_owner"}, {{"count_first", each List.NonNullCount(["first_meeting_date"]), Int64.Type}, {"count_qualified", each List.NonNullCount(["month_of_qualified_month"]), Int64.Type}, {"count_proposal", each List.NonNullCount(["month_of_proposal_submitted"]), Int64.Type}, {"count_won", each List.NonNullCount(["month_of_won"]), Int64.Type}})

This is the error

[BA_Pete]

Remove all the quote marks from your column names that are inside square brackets ( [..] ).

So, instead of ["first_meeting_date"], it should be [first_meeting_date] etc.

Pete

[virendrajadhav1]

I did and it ran without an error but the result is not what is expected. It shows all same values in all the clumns. If I filter data manually in excel, I could see the first guy in the salas_owner column should have "2" in "count_qualified" column.
This is how it looks

[BA_Pete]

I suspect that your columns are blank where there's no date and not actually null.

Select your 'Changed Type' step, then multi-select (Ctrl+click) all the columns that you want to count.

Once selected, right-click the column header of one of the selected columns and go to 'Replace Values.

In the replace values dialog, leave the top box and just type "null" in the secod box with no quotation marks:

Pete

[virendrajadhav1]

Pete,
Thank you so much !!
It worked very smoothly. I truly appreciate your help. Thanks again 🙂

[BA_Pete]

No problem, happy to help 🙂

Don't forget to give a thumbs-up on any posts that helped you 👍

Pete