cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerAppsDaniel
Level: Powered On

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

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

Emp1Budget123May2019Taxi
Emp3Budget123June2019Hotel

 

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.

2 REPLIES 2
Community Support Team
Community Support Team

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

Hi @PowerAppsDaniel,

 

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

 

Annotation 2019-11-29 103707.png

 

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:

Annotation 2019-11-29 104737.png

 

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.

Annotation 2019-11-29 105433.png

 

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:Annotation 2019-11-29 110023.png

 

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.

PowerAppsDaniel
Level: Powered On

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

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.

 

 

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (6,391)