Get distinct count of instances/rows from Excel Online in PowerAutomate Flow

[HarshavardhanG]

I have an excel online file which collects MS form responses from users (salespersons) about their sales activity.

Here's an example of the sales hierarchy:

Sample Sales Hierarchy

And an example of the form responses for a specific date looks like:

Sample form responses for the date

What I am trying to accomplish here is:

1. Count the number of entries made by each sales person (0 if no entry).
2. Send a table to each Boss with each salesperson name & count of entries made for the day.
3. And, if possible, calculate the sum of total sales count by each salesperson.

For example, I wish Boss 1 to receieve a mail with the following in mail body:

Mail to Boss 1

And Boss 2 to receieve a mail with the following in mail body:

Mail to Boss2

Can you please provide any hints/suggestions?

@abm , @Paulie78 , @PrasadAthalye , @DamoBird365 , @Pstork1 , @tom_riha 

Thanks and regards.

[v-bofeng-msft · Accepted solution]

Hi @HarshavardhanG :

I assume there are two tables:

The flow should be:

Expression:

1\

item()?['Boss']

2\

union(body('Select'),body('Select'))

3\

item()?['Boss']

4\

items('Apply_to_each')['Boss']

5\

item()?['Boss']

6\

items('Apply_to_each')['Boss']

7\

body('Filter_array_2')

8\

body('Filter_array')

9\

item()?['Name']

10\

items('Apply_to_each_2')['SalesPerson']

11\

body('Filter_array_3')

12\

items('Apply_to_each_3')['SaleCount']

13\

items('Apply_to_each_3')['BossEmail']

14\

items('Apply_to_each')['Boss']

15\

items('Apply_to_each_2')['SalesPerson']

16\

length(body('Filter_array_3'))

17\

variables('CoutSalesPerson')

Best Regards,

Bof

[HarshavardhanG]

Thanks for the detailed steps. Let me try this and revert. 

I'm guessing that I will have to use 2 'ListRowsPresentInATable' functions (excel online connector) for 'SalesHierarchy' & 'FormResponses' variables, instead of the 'initialize variable' used in the example.

I'm a beginner, so please bear with me.

Thanks again!

[abm · Accepted solution]

Hi @HarshavardhanG 

You can follow the steps given by @v-bofeng-msft 

There are different ways you could implement this.

1. Read the first excel file and get names

2. Filter the second excel file using names from step 1

3. Check the count result of the filter

4. Construct a compose and get all the details you want. Format the compose.

5. Append this result in a string

6. Add an If condition to check whether the Boss value is changed from the first loop. This means you can send email using the  append string value under the body of the email.

7. Also remember to re-initialise the value of the boss where you need to compare for the IF statement.

8. Finally outside loop use send email again to send the last Boss record details.

Hope it make sense.

Thanks

[HarshavardhanG]

Thank you so much for taking out your precious time to demonstrate your logic (stated above) on Teams. I definitely learnt some new concepts and will surely try and troubleshoot the remaining stuff.

Thanks a lot once again!

[DamoBird365]

Hi @HarshavardhanG 

Another consideration for you is using Xlookup, this would allow you to combine your two tables and you can query the data using excel scripts, rather than the excel table action.  I've also done a video on creating pie and column charts using your scenario as I had been looking for a proof of concept use case for the charts and so this has inspired me 😉.  My Flow has 7 or so actions and uses a form as a method of input to the excel table.  Hopefully it inspires you too.

Take a look here https://youtu.be/Tj2jasorczU

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉