Iterating over distinct or grouped by column values (CDS) in a Flow

[Anonymous]

Hi Power Automate Community!

I'm having a bit of trouble trying to get a Flow working. The trigger is fine, but after that everything is a mess 😞

I have a CDS entity called Expenses which looks somewhat like this:

Employee Budget Month Year Expense

Emp1	Budget123	May	2019	Taxi
Emp3	Budget123	June	2019	Hotel

My Flow is supposed to iterate over each distinct employee (Emp1, Emp3, etc.) and add the rows to a separate Excel file (per employee). However I can't find a way to make an Apply to each box iterate only over the different employees and not each row.

Any help with that would be greatly appreciated!

Feel free to ask for more details if needed.

[v-litu-msft]

Hi @Anonymous,

I think you should initialize two or three variables to store the Employee name of Expenses. For example, I initialize three variables:

Variable Expenses: Store All Data of Expenses - Employee, Budget, Month, Year, Expense.

Variable EmpName: Extract all Names to store in it, include duplicate names.

Variable EmpList: Remove duplicate names to store in it. Each name is unique.

union(variables('EmpName'),variables('EmpName'))

Before creating next steps, we need to create an Excel file with header but no data, it is for store the Expense of each employee, because Flow could not select new created Excel file, so we need to create it previously, and it needs to be used again and again:

So, continue to create the Flow, we need to create two Apply to each action, put the Emplist variable in the outside, and put the Expenses variable inside, loop through the Expenses to find the same name of the Emplist, if found it, add the row into the Excel file.

Each time end the loop of Expenses, we need to copy the excel file to a folder that stores all employees data, then we should empty the excel File by using Get all rows of the excel file and Delete a row:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-litu-msft,

Sorry I had to remove your post as a solution, but it isn't working for me. A few issues with the implementation:

- The Excel file is not being copied with the Copy file action (OneDrive for Business). It just creates a new empty Excel sheet.

- Power Automate can't seem to work with an Excel file that is being generated by itself. It doesn't exist for it.

So far I've tried a couple of things to avoid those:

1. Generating the Excel file from Power Apps: which doesn't work for me because it adds a column that doesn't suit my template (needs to be that format so that it can be imported).
2. Two Flows communicating via HTTP requests: doesn't work because it acts as one flow, that means the Excel is not accesible by the second flow with the modifications of the first one.
3. Two Flows: The first one creates/updates the Excel file and the second is triggered: Using "When a file is updated" gets triggered all the time. Using "When a file is created" triggers the flow, but since the copied Excel is empty, doesn't work like it should. Maybe if the copy is fixed, this should be the approach.

Hope this can help someone help me 🙂

Thank you in advance.