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.
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.
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:
Hope this can help someone help me 🙂
Thank you in advance.