Skip to main content
Community site session details

Community site session details

Session Id : Y6kQI41R0BZaO5d/8K/QlL
Power Automate - General Discussion
Unanswered

Through Power automate, I need to be able to group my excel sheet by category to get the subtotal of each category.

Like (0) ShareShare
ReportReport
Posted on 5 May 2022 13:21:45 by 30

Hello, I have an excel file that I need Power Automate to group by Account Name and total the categories. How would I attempt to do this?

 

For example: I want power automate to subtotal for KL and subtotal for AD. 

Account NameAmount

KL

500
KL100
AD100
AD300
  • KJoy17 Profile Picture
    30 on 05 May 2022 at 20:11:02
    Re: Through Power automate, I need to be able to group my excel sheet by category to get the subtotal of each category.

    Thanks! Looks like it is working kind of, but I have a couple issues. Would you know how to fix this for the Select activity because it's output gives me this KL : ""

    KJoy17_0-1651781408176.png

     

    Also, how did you code it to send an email? I was doing it for notifications before.

     

  • vivekpande18 Profile Picture
    894 Super User 2024 Season 1 on 05 May 2022 at 18:49:28
    Re: Through Power automate, I need to be able to group my excel sheet by category to get the subtotal of each category.

    Hi @KJoy17 ,

     

    Please find the flow step below:

    My excel file contains the below data and is stored in SharePoint

    vivekpande18_0-1651774067831.png

    1- Fetching the above excel from SharePoint.

    vivekpande18_1-1651774184385.png

     

    2- Initialize variable to subtotal the amount for each account name

    vivekpande18_2-1651774258372.png

     

    3- Select the Account Names only.

    vivekpande18_3-1651774300866.png

    From: select the dynamic value from List rows present in a table action

    Map : @item()?['Account Name']

    output after running above action:

    vivekpande18_4-1651774360543.png

     

    4- Get the distinct account names

    vivekpande18_5-1651774530925.png

    Input

    intersection(body('Select_-_Get_The_Account_Names'),body('Select_-_Get_The_Account_Names'))
    output after running above action:
    vivekpande18_6-1651774596417.png

     

    5 - Iterate over  output of above compose action

    vivekpande18_7-1651774696993.png

    Select an output from previous steps : outputs('Compose_-_Get_Distinct_Account_Names')

     

    6 - Filter the data based on account names and get all the details mapped to the given account name.

    vivekpande18_8-1651774828407.png

    From: outputs('List_rows_present_in_a_table')?['body/value']

    item()?['Account Name'] is equal to items('Apply_to_each')

     

     

    7- Again Iterate over the details got from above action to calculate the amount for each account

    vivekpande18_9-1651775064967.png

    Select an output from previous steps : body('Filter_array')

     

    8 - Add Compose action to store Amount (initialized above )variable value

    Untitled.png
     
    9 - Set Amount variable to add the amounts.
    Untitled2.png
    Valueadd(outputs('Compose_-_Previous_Amount'),int(items('Apply_to_each_2')?['Amount']))
    Note: In my excel sheet Amount column is of type string, that is why I have converted it into an integer to calculate
    int(items('Apply_to_each_2')?['Amount'])
     
    10 - After Apply to each 2  loops add send email notification and set the Amount variable back to zero
     
    Untitled2.png
    Final output calculation:
    Untitled.pngUntitled.png
     
     
    Entire flow:
    Untitled.png
    Untitled.png
     
     
    Mark this response as answered if this solves your query.

     

  • KJoy17 Profile Picture
    30 on 05 May 2022 at 17:14:24
    Re: Through Power automate, I need to be able to group my excel sheet by category to get the subtotal of each category.

    I will need it to send a notification of that subtotal if the amount is over 600 as a grand total by account name. I know how to send the notification part, but I need help on how to have power automate calculate the amounts by category.

  • vivekpande18 Profile Picture
    894 Super User 2024 Season 1 on 05 May 2022 at 16:01:06
    Re: Through Power automate, I need to be able to group my excel sheet by category to get the subtotal of each category.

    Hi @KJoy17 , 

     

    Can you tell us more about it. What will you do after subtotal? 

    Do you want to store subtotal somewhere? 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 1

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 1

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 1

Loading complete