Power Platform Community Forum Thread Details

I am needing help setting up a flow to update a List date from an Excel table matching on SKU. I am wanting this to imitate VLOOKUP in Excel.

My List contains the following four columns: SKU, Product Type, Info, and Update Date. There is data for ten SKUs.

My Excel table stored in OneDrive has the following two columns: SKU, Update Date. There is data for four SKUs that are contained in the List.

I want to update the Update Dates in the List with the Excel Update Date for the matching SKU.

I am viewed several YouTube videos that seem headed in this direction. Some I can't get to work, but many want to update the whole row instead of just updating one corresponding column.

I appreciate any help, including pictures, you can provide for this novice.

Categories:

Building flows

First do Excel Online List rows present in a table or whatever the action is called to get the rows from the Excel file.

Next, do a SharePoint Get items action to get the contents of the list

Next, do an Apply to each loop and put the output of the Excel List rows action into its input. The loop goes round the items in the Excel list (4 iterations)

Within the loop, add a Filter array action and filter the output ("value" dynamic content) of the SharePoint Get items action. The condition should be the SharePoint SKU column is equal to the Excel SKU column. Assuming the column in each table is called exactly "SKU" it'll be @equals(item()?['SKU'],items('Apply_to_each')?['SKU']) in the advanced view. You could copy and paste straight into there and then switch to basic mode, or just find the relevant dynamic content from the list.

Now, still within your Apply to each, do another Apply to each, but put the output of Filter array as its input. Within there, add a SharePoint Update item action. The Select the site and library and then for the ID column, use the expression items('Apply_to_each_2')?['ID'] and then update the Update Date column with items('Apply_to_each')?['Update Date'] (note: no _2 in this one). Use the expression editor for both of these.

What you're doing here in plain English is getting all the items from the Excel table, and for each item in the Excel table, filter the SharePoint list for matching SKUs, then for each matching SKU from the SharePoint list, update each row with the Update date from Excel.