cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lorem1960
Resolver III
Resolver III

How to create a Group By Report using a Power Automate flow?

Does anyone knows whether it is possible to create a Group By report using power automate?

 

I have created a Power Apps application that gathers monthly expenses (Mileage)  for some of our employees, and I created a report for that using a flow, so a user select a Month/Year and then clicks on a button that triggers a flow which then in turn produces a PDF listing all employees and the expenses incurred. So a report might look like this:

 

Date Expense - Name Employee - Amount Euro 

 

04/02/2022      John Doe                €122                

04/05/2022      John Doe                 €55                  

04/08/2022      Pete Free                 €88 

04/10/2022      John Doe                 €100

 

Total €: 365

 

But what I really need is a report that groups the expenses per Employee, something like this:

 

Date Expense - Name Employee - Amount Euro 

04/02/2022      John Doe                €122                

04/05/2022      John Doe                €55

04/10/2022      John Doe                €100   

Total €:  €277

--------------------------------------------

04/08/2022      Pete Free                 €88

Total €: €88

 

This is the flow that I am currently have to produce the monthly expenses report:

 

Lorem1960_0-1650436465911.png

 

Since I am fairly new to power automate I have no idea how I can create a report that allows you to create a Group By report per Employee and that runs a sum per Employee. Thanks for any advise or tips you can provide.  

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Lorem1960 

 

Ok, I can see what is happening from the screenshot.  You want to be filtering the original array but I think you are filtering on the array of distinct names?  The purpose of this filter is to take the original array, check the employee name against a distinct name from the apply to each loop. I believe you have used the distinct array as your input (From) instead of the array from your power app and therefore it cannot find the employee key that is being referenced.

 

If you download the sample flow from my github that is in my first post, you will be able to see the inner workings.  I reference the compose action in From that contains the array, you will want to reference the input from the Power App.

 

Damien

View solution in original post

7 REPLIES 7
DamoBird365
Microsoft
Microsoft

Hi @Lorem1960 

 

I've stuck your data into a compose and then used select in text mode to get the employee names and combined that with a union to get the distinct names:

 

DamoBird365_0-1650482393128.png

 

Then with an apply to each, I have inserted the distinct names, filtered on the current item equaling item()?['Name employee'] and created an html table on the result.

 

DamoBird365_1-1650484360725.png

 

Within the same loop, we can then calculate the total using XPATH.  There is currently no way to sum in Power Automate without an apply to each or using variables.  

 

Use select in text mode to replace the euro symbol with ''.  replace(item()?['Amount Euro'],'€','')

 

Then create an object with root element in a compose which can then be passed to the expression xml(outputs('Compose_Root')). 

 

Finally we can sum those nodes to get a total xpath(outputs('Compose_XML'),'sum(//root/Amount)').

 

DamoBird365_3-1650484507504.png

 

You can then combine the output of the create HTML Table with the Compose SUM to send an email. If you put the html table and total in a compose at the end of your apply to each, you can use outputs('ComposeHTMLTableANDSum') to create an array of all tables.

 

I have a compose that has a couple of <br> tags and some dashes (to join on and match your example) and then a final compose to join the objects from my array above with the return line and dashes.  

 

join(outputs('Compose_Array_Of_TablesNSums'),outputs('Compose_Return_And_Dashes'))

 

This can then be chucked into send email with HTML editing enabled.

 

DamoBird365_7-1650485319726.png

 

Output as follows:

 

DamoBird365_8-1650485412581.png

I've uploaded the flow to download from my github too.

 

Let me know how you get on.

 

Cheers,

 

Damien

 

 

Lorem1960
Resolver III
Resolver III

Thank you so much for taking the time to reply, I will try to see if I have enough time tonight to create the flow you suggested. Much appreciated and have a nice day!

Lorem1960
Resolver III
Resolver III

Okay I currently have this for my new flow, but for some reason it fails on the filter Array action:

By the way, the outputs from action Compose Distinct Name look like this:


[
"John, Doe",
"Peter, Pan",
"Eric, Winter",
"Lesley, Summer"
]

 

Lorem1960_0-1650619979745.png

 

From within my Canvas app I have added a button with this code

 

ForAll(
GalleryMileages.AllItems,
Collect(
colDataToExportMileage,
{
MonthYear: ThisRecord.MonthYear,
DateExpense: Text(ThisRecord.DateExpense,"mm/dd/yyyy"),
Employee: ThisRecord.Employee,
Destination: ThisRecord.Destination,
Km: ThisRecord.Km,
AmountEuro: Text(ThisRecord.AmountEuro,"####.00"),
Residence: ThisRecord.Residence

}
)
);

GroupbyReportMileageExpenses.Run(JSON(
colDataToExportMileage,
JSONFormat.IncludeBinaryData & JSONFormat.IgnoreUnsupportedTypes
))

 

 

 

Hi @Lorem1960 

 

The input to your filter isn’t an array. Try putting it in a compose prior to the filter and then looking at the history of the flow. 

Damien

I am a bit lost here, so this is what I currently have before the flow goes into the apply to each loop:

 

Lorem1960_0-1650779318083.png

 

And then I have this in my apply to each loop:

 

Lorem1960_1-1650779400332.png

I have no idea where exactly I need to put in another compose action. Thanks for helping.

Hi @Lorem1960 

 

Ok, I can see what is happening from the screenshot.  You want to be filtering the original array but I think you are filtering on the array of distinct names?  The purpose of this filter is to take the original array, check the employee name against a distinct name from the apply to each loop. I believe you have used the distinct array as your input (From) instead of the array from your power app and therefore it cannot find the employee key that is being referenced.

 

If you download the sample flow from my github that is in my first post, you will be able to see the inner workings.  I reference the compose action in From that contains the array, you will want to reference the input from the Power App.

 

Damien

Thanks Damien for your help on this, very much appreciated!!!

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (2,295)