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

[Lorem1960]

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:

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.  

[DamoBird365]

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:

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.

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)').

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.

Output as follows:

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

Let me know how you get on.

Cheers,

Damien

[Lorem1960]

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]

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"
]

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
))

[DamoBird365]

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

[Lorem1960]

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

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

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

[DamoBird365 · Accepted solution]

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

[Lorem1960]

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