Answered

Trying to unite 2 excel files and put the equivalent on same row (just like Vlookup does in excel)

(0) Share

Report

Posted on by DaftNewb

Hello everyone, i'm struggling with a task of having to unite 2 excel files that i've extracted from 2 diferent sharepoint lists, and i need them to align with the right person (a single line of text column) at the same row. Not everyone who is present on the first sharepoint list is also present on the second one, i've managed to get the data from the person i needed from the first Sharepoint list and on the second sharepoint list i've gathered another kind of data but i'm trying to use the email so i can compare.

I am able to dump the info from both of them on the same excel file but i cant make it place on the right row on the table. If there is info from the first sharepoint list there will be a blank space where the second sharepoint list info should go. Visual example of it:

same happens with the info from the second sharepoint list wich has a blank space where the info from the first sharepoint list should go. The expected result should be more like this:

but i'm not managing to find a way to make it happen, so any help will be more than welcome.

I also tried to paste a "vlookup" excel formula after the first splist data but power automate doesn't seem to accept excel formulas on it.

Thanks in advance everyone for the attention and i'm waiting hopefully for some help.

Categories:

Building flows

All responses (4)

Answers (1)

Sort by

Verified answer

v-xiaochen-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @DaftNewb ,

Please check the sample:

Result:

Best Regards,

Wearsky
takolota1 4,980 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

@DaftNewb @v-xiaochen-msft

You can get the Vlookup columns into Power Automate.
Just create the Vlookups in columns set further down & once they have all the data you want, cntrl + shift + down key to highlight them all and copy them. Then right click in the columns where you ultimately want the data and in the Paste options select Values to just paste the text values without the formulas. Then you can use them in Power Automate.

Alternatively, if these are relatively large lists, you could use a batch update SharePoint method to directly update one list from another list based on a primary key column like in this template: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-SharePoint-List-With-External-Data/td-p/1365410
DaftNewb on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, @v-xiaochen-msft this would be perfect but i need to use it with dynamic content wich does not load the column names and configuration when i use it.
DaftNewb on at

Like
a
(0)

Report
Copy link

Link copied!

somehow after poking the update a row for a few time by adding and removing an standard file that already had the columns i needed and swaping bacl for the dynamic content an option called "provide the item properties" showed up, where i've inserted a json from the columns i needed and was able to use dynamic content on it if anyone is wondering about the json mine was like:
{
"name2": "items('Apply_to_each')?['name2']"
"info5": "items('Apply_to_each')?['info5']"
}
so finaly @v-xiaochen-msft was sucessfull for me, thanks a bunch for that