Solved: Flow Moving excel rows to multiple sheets based on...

Mick_gibbons1 · ‎06-25-2023

Hi all,

I have been trying to create a flow that moves data from one Excel; the flow below works for that 1 set of data (north), but when I repeat the flow for Central, South west, South east etc it copies the North information again, before failing as it's try's to delete the same information.

v-wenjuan-msft · ‎06-28-2023

Hi @Mick_gibbons1 ,

Delete filter query here and add filter array action like this:

Community Support Team _ Wenjuan Zou

If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

wskinnermctc · ‎06-30-2023

Hi @Mick_gibbons1 , good work! We at least have the flow working! Seems like we just need to troubleshoot why the outputs are incorrect.

Using your Example:

Array 1 Central goes to Central sheet

Array 2 North goes to North sheet

Array 3 and 4. These do not work, array 1 data is distributed to their sheets

First:

It would be very helpful for me and you if you organized the flow and renamed the filter arrays.

Even from your example of, Array 1 goes to Central and Array 2 goes to North, the photos do not correspond to that. Your photo shows Filter Array 2 goes to SouthEast.

(When you rename the filter arrays, you might have to go into the Apply to Each source and x/delete the old Body and put in the new. Sometimes the name doesn't automatically refresh in objects linked to it.

For example the source for Apply to Each will be body('Filter_array'), but if you go and rename the Filter array to "Filter Array North" and the source of the Apply to Each stays as body('Filter_array'), it will error. You have to make sure that the source of the Apply to Each changed to body('Filter_Array_North') so it will get the correct object.)

So Rename the Filter Array AND the Apply to Each so that they correspond. Also put them in order within the flow.

"Filter Array 1-Central"
- "Apply to Each 1-Central"
  - "Add a row into a table 1-Central"
"Filter Array 2-North"
- "Apply to Each 2-North"
  - "Add a row into a table 2-North"
"Filter Array 3-SouthEast"
- "Apply to Each 3-SouthEast"
  - "Add a row into a table 3-SouthEast"
"Filter Array 4-SouthWest"
- "Apply to Each 4-SouthWest"
  - "Add a row into a table 4-SouthWest"

Second:

Double check the source of each one of the Apply to Each so that the body corresponds to the filter array. This should be done as part of the renaming.

Third:

Double check the values that are in the Add a row into a table. The fields should have values that correspond to the Apply to Each that they are in.

So values in Apply to Each 3-SouthEast should show items('Apply_to_Each_3-SouthEast')?['ID'] in the field.

See how all the names match below:

Corresponding Names

Note:

When you add a new value to a field using the Expression input, the box in the field might look like a burgundy fx items(...) but after you run the flow it looks like a light purple box with the correct name. This is because the flow doesn't really know what it is until after it runs.

Note about display of fields

Summary:

Basically want you to organize the flow and double check the values. Clearly the flow works, we just need to ensure we are getting the right values to the right places.

Let me know how it goes,

View solution in original post

v-wenjuan-msft · ‎06-26-2023

Hi @Mick_gibbons1 ,

Sorry for the bad experience with Power Automate.

Unfortunately, this is a known limitation with "List rows present in table" action. Refer to official document for details:

Excel Online (Business) - Connectors | Microsoft Learn

As a workaround, you can delete the filter query and add a "Filter array" action for the same purpose.

Community Support Team _ Wenjuan Zou

If this post helps, then please consider Accept it as the solution to help the other members find it.

v-wenjuan-msft · ‎06-28-2023

Hi @Mick_gibbons1 ,

May I know is there any progress or do you need any further help?

Community Support Team _ Wenjuan Zou

If this post helps, then please consider Accept it as the solution to help the other members find it.

Mick_gibbons1 · ‎06-28-2023

I would be grateful if you could show me how filter array would work in my flow. I have watched a few videos and it does not make sense to me

wskinnermctc · ‎06-28-2023

@Mick_gibbons1 Are you just doing this one time?

I'm curious because I want to know how the "Main" table is getting populated but the other tables are not. Is the Main table a report or does it get filled from a MS Form?

Mick_gibbons1 · ‎06-28-2023

So the MS form populates the main table. I then want to create a flows that takes those rows, and populated other Excel sheets based on a column in the spreadsheet. That column is not populated by the ms form

wskinnermctc · ‎06-28-2023

Instead of using an Excel workbook, could you use a SharePoint list as the Main table? You don't have to delete the items from the sharepoint Main list, or you can if you want, but you could easily get things from a SharePoint list and put them into excel tables.

Process would be something like:

Form is submitted by someone.
PA creates a new item in the SharePoint list - MainSPList
- The MainSPList has the 'Region' column as a choice option for North, South, etc.
Person goes to MainSPList and selects the choice option for the region
PA Trigger is based on "When an Item is Modified" and then "Get Changes for an Item" so the flow will check if the 'Region' column was changed.
- This flow will put the individual item in the specific table

So basically this is using a SharePoint list as a base of data, and then when the individual item Region is identified it will be moved to the correct Excel table.

I know that is a completely different process, but I think it would be a more long term solution and prevent any issues PA has with running data in and out of Excel.

Mick_gibbons1 · ‎06-28-2023

I've been considering using Sharepoint,

Is there away of having a lookup function in SharePoint, like I do in excel? There for no need for having someone select north south etc

Thank you so much for the reply and thoughts

wskinnermctc · ‎06-28-2023

What is being looked up?

I thought the 'Region' column was not in the MS Form but it is in the Main Excel table? How is that Region column getting populated in the Main excel? I assumed it was manual.

Mick_gibbons1 · ‎06-28-2023

Hi mate

No it's not manual, the postcode/zip code entered into the ms form. Then in excel, the formula checks the postcode against a list and then adds in the region

v-wenjuan-msft · ‎06-28-2023

Hi @Mick_gibbons1 ,

Delete filter query here and add filter array action like this:

Community Support Team _ Wenjuan Zou

If this post helps, then please consider Accept it as the solution to help the other members find it.

Mick_gibbons1 · ‎06-29-2023

Now i understand. So list the row, filter array, then set all the add rows to the table, then delete. Thank you, i will check this out asap

wskinnermctc · ‎06-29-2023

Similarly, you could Get the form response, filter the excel Zip Code table with the form response zipcode, get the region, and add a new row directly to the specific table.

This will skip the main table and delete requirement. It also doesn't require a dependency on a formula to lookup the zipcode.

I think formulas will not work if the file is not opened or refreshed, but you can test that. It might work for you since this is a scheduled flow.

I've seen when people try to use a workbook as a intermediate calculator too rapidly with a PA flow, the formula may not update the item.

Just as I typed this, the question comes up in the forum:

https://powerusers.microsoft.com/t5/Building-Flows/Cloud-flow-is-not-using-the-latest-version-of-my-...

Mick_gibbons1 · ‎06-29-2023

So I have tried, the following flow. All it does is copy all rows to both sheets and not seperating into their tables

Mick_gibbons1 · ‎06-29-2023

I would not know where to start with that idea lol

wskinnermctc · ‎06-29-2023

You have to use the Filter Array as the source of the Apply to Each and for the values of add new row to the table.

You will have to individually put an expression of item()?['ColumnName'] into each field. There will not be any dynamic content to click and put values into the fields like when the Excel action is the source.

Excel Filter and Delete

Let me know how this works for you.

I believe you will have problems deleting the rows due to these actions running in parallel, but I don't know that for sure. It might work fine since it is using a unique row identifier to delete the specific rows.

Mick_gibbons1 · ‎06-29-2023

Does not work in Parallel, and when i try and run in order, it does the same. Thank you for your help

wskinnermctc · ‎06-29-2023

I don't understand. You made your flow look exactly like my example, but it is still putting all rows on all tables?

I tested my example and it worked without an issue.

Mick_gibbons1 · ‎06-29-2023

I tried, but it does not let me run a parallel branch for a second lot of data. Then when I try and do your version with a rerun under it, it does not work. It keeps changing the Fx to dynamic.

I have 4 types of data, that needs to go into 4 different tables.