cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HarshavardhanG
Helper I
Helper I

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

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 HierarchySample Sales Hierarchy

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

Sample form responses for the dateSample 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 1Mail to Boss 1

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

Mail to Boss2Mail to Boss2

Can you please provide any hints/suggestions?

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

 

Thanks and regards.

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @HarshavardhanG :

I assume there are two tables:

v-bofeng-msft_0-1623045032822.pngv-bofeng-msft_1-1623045057158.png

The flow should be:

v-bofeng-msft_2-1623045140654.png

v-bofeng-msft_3-1623045197841.png

v-bofeng-msft_4-1623045262233.png

v-bofeng-msft_5-1623045301821.png

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

View solution in original post

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

View solution in original post

5 REPLIES 5
v-bofeng-msft
Community Support
Community Support

Hi @HarshavardhanG :

I assume there are two tables:

v-bofeng-msft_0-1623045032822.pngv-bofeng-msft_1-1623045057158.png

The flow should be:

v-bofeng-msft_2-1623045140654.png

v-bofeng-msft_3-1623045197841.png

v-bofeng-msft_4-1623045262233.png

v-bofeng-msft_5-1623045301821.png

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

View solution in original post

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!

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

View solution in original post

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

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 😉

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,947)