Mapping of 2 excel spreadsheet

[gurleenm]

I have an excel spreadsheet with the VideoId,Name, State ,Description ,PublishedDate, LastViewDate ,Size (in Bytes) ,Views ,Likes, ContentType, PrivacyMode, Creator, Owners ,ContainerId, ContainerName, ContainerType ,ContainerEmailId ,ContainerAadId, MigratedDestination ,ContainerVideosInClassicUI ,IsEligibleForMigration

another excel spreadsheet that has all the containeremailid with no duplicates and arranged alphabetically

now what i want my power automate to do is check in second spreadsheet if the column 1 matches value of first spreadsheet and put in separate sheet or table forexample if the first column of second spreadsheet is gurleen@xxx.ca and it has 3 records in first spreadsheet then it will take that 3 records and put it in table and send an email to that person and then look for 2 column in second spreadsheet and map it to first one

How can i do that?

[harshdeol · Accepted solution]

Hi @gurleenm ,

I created the flow in my environment. Please give it a try.

My Excel1 File Table contains following columns:  VideoId,Name,State,Description,PublishedDate,LastViewDate,Size(inBytes),Views,Likes,ContentType,PrivacyMode,Creator,Owners,ContainerId,ContainerName,ContainerType,ContainerEmailId,ContainerAadId,MigratedDestination,ContainerVideosInClassicUI,IsEligibleForMigration

My Excel2 File Table contains the column:  containeremailid

When the flow runs, it will get all the records of Excel2 and checks for the records that have same ContainerEmailId  and if it finds the records, the flow will add them in the Table and send the table via email to that person. Please Find the input and output at the end.

Please follow these steps provided below. 

Here is the full screenshot of the flow:

Steps:

1. Please initialize a variable of Array Type

2. Add "List rows present in a table" action and configure it for "Excel2". Also, please make sure to add the filter query to filter empty records of the table.

3. Add a apply to each and In Appy to each add "List rows present in a table" and configure it for Excel2. Please make sure to add the Filter Query here.

4. Add a Condition to check the records exists or not. If condition is false it will move to the next iteration of the loop.

If condition is true, it will iterate of all the records of excels 1 which are having the same ID of current item and Append the Value in an Array variable.

Note: In Append to array Variable Action, I'm only using two values "VideoId" and "Name". please configure similarly for all values that you want in the Table.

{
  "VideoId": "",
  "Name": ""
}

5. Add Create an HTML table Action and add the "VarAppend" Variable in from Dynamic content in "From". Refer to image below.

6. Add Compose Action and Paste this CSS for the Table Styling:

<style>
Table {
  font-family: Arial, Helvetica, sans-serif;
  background-color: #EEEEEE;
  border-collapse: collapse;
  width: 100%;
}
Table td, Table th {
  border: 1px solid #ddd;
  padding: 3px 3px;
}
Table th {
  font-size: 15px;
  font-weight: bold;
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #1C6EA4;
  color: white;
}
</style>

Note: You can Modify it Accordingly.

7. Add a Send an email Action and added the values of "To", "Subject" and "Body".

In Body, Add Output of Compose-CSS First and then Output of the Create an HTML Table.

Please try it at your end and let me know if you face any issues. Also, please feel free to ask if you have any questions.

Input:  

Excel 1: [Data]

Excel 2: [Data]

Output in Email:

Thanks,

Harsh

[gurleenm]

Hi

Thank you so much for solution 

But i am having little problem

I am getting headers after values , how i can solve that

[harshdeol]

Hi @gurleenm , 

It seems that there is a wrong configuration in the "Append to Array Variable" Action.

Please make sure that the schema should be like this.

Additionally, can you please share the screenshot of the current "Append to array variable" Action?

Thanks,

Harsh