Power Automate - How To Filter Excel Rows Using Unique Column Names & Send Email?
HTML-код
- Опубликовано: 21 авг 2024
- Power Automate - How to filter Excel rows using unique column names and send email?
Technique used here is Union() expression and Filter for retrieving the relevant rows.
Power Automate Community
powerusers.mic...
This is the best tutorial! Exactly what I needed and it all worked for me! Thank you so much!!
Thanks a lot
lucky I found your channel, U don't know how much I appreciate your content!!
Thanks a lot for your support Nikky
Thanks for helping us.. Even after watching so many other video I was stuck as those were covering too many things in one video.. But, I successfully able to send email after watching your simple but very effective video..
Most welcome 😊
Thank you for this! This is a much simpler approach than other videos I have seen.
Glad it was helpful!
so good thanks! I've been tried for 3 days with no success. This is such an important flow and used a lot.
Great to hear!
Hi, thanks so much for this. Works perfectly, have also formatted values for date and currency as well as inserted a style table.
I have tried in many different ways using different methods such as conditions or additional filter query at start but however I try and implement this with conditions it sends one email per row instead of per address.
At the moment I use a filter query within the list rows action to only include rows which have a detail blank. Then I would use a condition or filter array to only select specific criteria from a different column. This all works fine and while doing tests everything seems as though should work until I add the send email function. This is keeping filters in place as in I am receiving the number of emails which match the number of rows after all filtering done but again, getting one for each row instead of just one.
In case it helps, in my final apply to each(2) it shows 2 records. One due to blank email (I can deal with another way), the second is for my email address and shows the only 2 lines out of the 8 which contain my email address (8 lines after all filters applied). yet when running I get 8 emails!
I have tried with just the filter query within list rows for a different scenario and works fine but this one requires further conditions. I also did this without the create html table and also works fine. At the moment the only additional step I have made to this video is a filter array after the list rows. I have tested along the way with this video to ensure all looks as should and struggling to see where I could be going wrong.
Can you please help? If possible a video of this with additional conditions would be great!
Thanks in advance!
*Edit: So basically when doing this from scratch, the ai turns one of the first steps into a loop unnecessarily. I tried to insert the filter array into an existing flow and same thing happened to the next step. I'm not sure how but eventually managed to insert the step without any ai intervention and with a little tweaking regarding outputs referenced finally managed to get it working.
Thanks again though as I found this video extremely helpful for someone new to Power Automate!
Hello Vikram
If you need further help in this please post in Microsoft Power Automate Community Forum. Tag me as @abm. I will have a look.
powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity
Thanks for this! I watch all your videos. A goldmine of info!
Hi Shakeel
Thanks a lot for watching the videos. Glad that it's helpful. Really appreciated your comments. If you have any questions please post in Power Automate Community (powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity) and tag me as @abm.
Thanks
Thank you Very Much!! Works Great!
You > Chat GPT
You're welcome!
Very Useful Content. Solved my longstanding issues. Thanks a ton
Glad it helped. Thanks a lot for your kind comments.
Amazing content. Keep doing this good work! The subscriber count should be far higher.
Hi Dipam
Thanks for the wonderful comments. If you need any help with Power Automate always reach me and happy to help you.
Thanks
Thank you so much for your steps!!! This was so much easier then what I was trying to do!
Glad it helped!
Proud to say still works. Thanks for the video :]
Thanks a lot for the support
Thanks for the wonderful video. This will help lots of people like me .I have a kind request , Kindly make a video along with this flow having personalized attachments and unique CC . That will be great .Thank you
Could you post some sample data. I will make a video for you.
@@abm4975 hi! Your video has helped me a lot. Could you please make a vid along with this flow by adding personalised attachments? You could use the same sample data and create your own unique attachments based on your own data. Thank you
Thank you so much for posting this. This worked well for me 😊
You’re welcome 😊
OMG! I found you! Than you for this video!!!
Thanks
this is so helpful! thank you so much. If you could also share how to make the email to dynamic content. sample: Hi (Name). tried connecting it to excel file but it is sending multiple emails to the same person. appreciate the help. thank you!
That means you are trying to send the email within the loop.
Thanks
This is outstanding, did you have any automation on Dataverse as backend and filter by email then send the data as attachment.
Hi
Apologies for the late reply. I been away for a family emergency. If you still need help in this please let me know. I could make a video for you.
Thanks
Thank you so much! You made that so easy!
Glad it helped!
Thanks so much for this
I think this is the simplest video I have seen on the topic. I have a question though on the parse json step. It appears you had to copy the result of the previous step into the schema payload input. For my use case, the list of emails will change. What do I do?
Best video ever
Thanks a lot
very good video. It helped me a lot. Thank you so much
You are welcome!
I have been looking for a solution like this for so long, thank you so much. One quick question, when I get my filtered table, I need to save down a seperate excel/csv of EACH table, can you let me know how I tweak the flow to do this please?? I have been trying but cannot get it worked out.
Essentially I have a large spreadsheet of Payroll Data which I need to split out to over 300 seperate files using the Cost Centre as a filter (every 2 weeks).
Hi
Thanks so much for the support.
I was away for few weeks. Do you still need help in this please?
Thanks
Excellent one. Thank you !
Hi Saleem
Thank you very much for the support.
So happy to find this! Thank you so much
One quesy how to remove iteminternal id and odata.etag columns from email body when automatic html table generated?
Action step Create HTML table, choose custom and on the left hand side column type your column you want and on the right hand side use expression item()?['yourcolumnname']
@@abm4975 Thank you ❤️ Also do i have to copy the output of get all unique emails and paste it to parse json step everytime with different data sets?
Hi
Not sure what you meant by. Parse JSON step is for validating against one schema set. You can't use dynamic schema set against Parse JSON step.
Thanks
Hi , Thanks for this helpful video . I tried to make same environment however the each end User is getting 5 times emails with same subject if there is 5 rows in the excel Is there any way the to get 1end user with 1 email .please suggest
Hi
Hope you resolved your issue. If you still need help please let me know. Catching up after holidays.
Thanks
Hi, words can't describe how useful this has been, but I have run into a problem. My source table is around 400 rows long, but the html table that gets sent in the email only contains the first 256. How can i make it so that the entire table is read, processed and sent?
Hi
Default is 256. Follow the steps for accessing more than 256 records.
Navigate to 'Settings' on the Excel action step. (Click on the three dots of that action step. Here you can see settings)
Enable pagination
Set the threshold above 256 e.g. 5000.
Thanks
Thanks for the wondeful tutorial, but may I have one more question? In my table, i also contain a CC colum which I need to cc those person in email, may i know what should I input in the CC field? Thank you!!
Hi
Hi
I need to see your flow. I could offer a screenshare help. If you up for it please contact me via Teams. See my team email under the Channel about section.
Thanks
Dude!?!?! Thanks a million.
You're welcome!
Wow.... This is great 👍
Thanks a lot
Great! Thanks bro for this content.
Hi Karthik
Thanks for your kind comments.
@@abm4975 I was successfully able to create the flow, thanks for this again. Just thought of asking if I could add borders to the table on the mail body, request your help on this.
Hi
Yes you can. Add some style sheet into that. declare a css. If you really want a custom one here is a good one from Ryan.
ryanmaclean365.com/2020/01/29/power-automate-html-table-styling/
@@abm4975 Much thanks 💯
First of all, thanks a lot for making such a detailed video. i have created this flow and now i have to send email to multiple users. but stuck at a point. i sent this email every month, so for January subject would be like ( Voucher email for Jan'24), for Feb it will be like ( Voucher email for Feb'24). i want to make dynamic flow to auto change month in subject.
And in email body, i want dynamic name like
Hi __Dynamic name,
in place of dynamic name i want flow to pick concerned person's name.
i tried to create a flow but did not worked.
it would be great if you can help me creating a flow for that.
Hi ,
Thank you so much for the detailed video. Really helpful for a newbie like me. I would like to add another email dynamically from Excel to the CC field. for example I need send email to the employee and cc his manager also. Could you help me achieve this?
Could you please post your issue in Power Automate community and tag me as @abm. I will have a look.
powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity
@@abm4975 Hi abm I’m also trying the same thing. Trying to send emails to the staff and also their manager as CC. I have posted my data and the flow in help community as you suggested and tagged you. Hope you will kindly looking into it . Thank you so much.
Hello, This video was very helpful. I have slightly different requirement where I have email id's in three different columns (Operation Manager 1 email , Operation Manager 2 email, Operation Manager 3 email) for each organisation. email Id present in first column for a organisation can repeat in second column or third column for different organisation. I need to send emails to a person only once. How can I achieve this?
Do the union expression to get unique emails
Hi There. Thanks for sharing this video. I have a question. what if Table 1 doesn't show when you have selected your spreadsheet on onedrive?
Hi
Good question. You could create table manually or if you want to create automatically I have few videos in my channel about Dynamic creation of tables in Excel sheet.
Thanks
Excellent video. I don't why my teammates are getting like 30 emails showing the same information. Do you know what can I do, so they can get only one email with the table?
Most likely this could be a looping issue using the same variable. Try re-initialising this. If you need further help in this please post in Microsoft Power Automate Community Forum. Tag me as @abm. I will have a look.
powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity
Hi, thanks for your videos.
I had a query rather a problem, I want to extract from a table, an email that can be a non-unique value of the last row, for which I use a compose which the formula is: last(outputs('List_rows_present_in_a_table')?['body/value']), to always get the last row but then when you file with select you get all the rows with all the information
Could you help me?
Hi
Could you post this in Power Automate community forum. Post a sample data of the table also. Tag me as @abm. I will have a look.
powerusers.microsoft.com/t5/Building-Flows/bd-p/BuildingFlows
Thanks
Great video, it took me a few tries here and there as I am still pretty new to Power Automate but I got it working and this is exactly what I needed. The only thing I would like to add is to personalise the beginning of the email text with for example "Dear (first name)" and the fist name to have that connected to the name that is in one of the columns in the Excel table. I have tried a few things but I'm unable to get it working. Would you have any idea how I could get this?
Thank you very much!
Hi
So under the email action step under the body, type Dear then map the dynamic value from excel sheet. If you still need further help could you post this in Power Automate community forum. Post a sample data of the table also. Tag me as @abm. I will have a look.
powerusers.microsoft.com/t5/Building-Flows/bd-p/BuildingFlows
Thanks
did you find a solution for this am also having the same issue
@@abm4975 Hi, I have same problem. I followed the instruction, Select an output from previous steps will show "Value", and after I test run, it sends emails to all the names and email on the list, can you please help on this? I appreciate
Hi
I need to see the flow. Could offer a screenshare help. Please contact me via Teams. My contact details are under the channel about section.
Thanks
Hi, can share the solution given?
Please show your final output after sending an email it says some error in arrays. We are missing something in the Timeframe 06:14 to 06:15.
Hi Revathi
That part was edited. So if you continue to look I did explained about item()?['SchemaName'] for the filter.
If I understand this correctly, it will send only certain (unique) part of values for certain user? This can be applicable on Power BI (query dataset to Email or Teams)? Because these steps are similar to mine.
Hi abm, I need help please :( I am trying to do the exact use case you have...however my starting point is that the trigger is a button click from within PowerBi. Error message """""The 'from' property value in the 'select' action inputs is of type 'Null'. The value must be an array.""""""""" is what I get when I try to follow your steps.
1) What should be the step directly below " PowerBi button clicked"
2) Can I actually use the same steps as you have in the video or I need to do it differently because I'm within PowerBi
Thanks so much for your help :(
Hi
Apologies for the late reply. Do you still need help in this?
Thanks
I have created a flow as per your instructions however the email is getting triggered as per row count eg:- if I have 10 rows it's getting 10 time email with same details.how to solve this
Hi
Thanks for the support. Most likely this is a logic issue.
Could you please post your question under Power Automate community. Tag me as @abm. I will have a look.
powerusers.microsoft.com/t5/Using-Flows/bd-p/UsingFlows
Thanks for this amazing tutorial, it was really helpful. Please if I want to copy the last message or messages from a chat conversation like WhatsApp, I can I automate this. I mean to copy only the last message or messages sent by a WhatsApp contact, so that I can paste it in another app. I only need to copy the most recent message that I received from a client and not the one I sent, and I can automate the process to only refer to the recently received messages. Thanks a reply will be highly appreciated
Hi Maido
You need to have a business account for WhatApp. Please have a look at this.
respond.io/blog/whatsapp-business-api
Thanks
Great video! how can we send the same output as excel attachment in an email? what step should be added?
Hi
You need to create a table first using the excel connector action step. Then use the insert rows to insert the records. Once the record is inserted use the File content by Path (SharePoint or OneDrive which ever you are using to store the Excel file) to extract the content and attach in an email.
Thanks
thank you for this, however, how do you send three separate emails to David since David has three columns. thanks
Hi
Thanks so much for the support.
I was away for few weeks. Do you still need help in this please?
Thanks
@@abm4975 yes please, and thank you in advance
Thanks for this video, is there any way to send the respective attachments also against each line
Now in mail, this is mentioned like "Dear "
I dont want to mention mail address. In "A" column, Name is mentioned. So from "A" column, I want to grab the attention(not from mail ID). So Mail will start like "Dear David"
How Can I do this, Please kindly advise.
Do a filter against the Excel using email address. Set the Top count to 1 as you need only one record. Once it filtered you can get the name you want.
Thanks
I am running a query against a power bi dataset, i convert to html table, i want to send multiple emails to the persons on the list with an overview of the tasks, can i apply this what you showed? So one bundled email for person X with 6 tasks for instance
Hi
Once you find the unique values (Use expression union) the filter the data using the unique values. In your case unique value is email. Once you filtered the data you can get all the related tasks and email to that user.
Thanks
how does this work when we add one more filter (for my case the date column should be one month today and does not equal to null) and the mail should be sent after this condition is satisfied
Has anyone tried to successfully build a flow with this setup wherein we need to further filter the data using a condition or a value from another column?
Hi, thank you for this video. I'm trying to add another arguement to the filter array step inside of the apply to each, where I want to only send emails where the filter is met. Using examples from this case, I would only want to send emails where Sport was basketball for example. And, for a certain user with entries for more than one sport, I still would only want to send the information for Basketball.
Hi
Apologies for the late reply. Do you still need help in this? Please let me know.
@@abm4975 I'm trying to do this as well. Do you have a video on it?
@@abm4975 Did you ever find a solution on this? I am needing the same filter
@amyfruites911, happy to help. Could you please describe more on this with an example. Check my channel about section and contact me via email. Happy to do a team meeting and find out more about your requirement, so that I could do a video based on this.
Thanks
Happy to help. Could you please describe more on this with an example. Check my channel about section and contact me via email. Happy to do a team meeting and find out more about your requirement, so that I could do a video based on this.
Thanks
What if 2 email has to be sent to David one is for badminton and other one is for second sport?
Unique email id has to be picked up but for each unique email id, one more filter to be done which is sports. How to do this?
Similar technique to find the unique sports first. Then iterate the values in a loop to send email individually for each sport.
Have some missing in 6.14 and 6.15 can you tell us which output need to select facing the error of filter array failed.
Hi
That was an edit I did to remove a step which was not needed. What error are you getting?
Thanks
Hi, Thank you so much for this video. I tried to replicate this model with the exact same steps and same sheet and names but I get stuck in the minute 6:40. When I run the test I don't get the same Output as shown in the video. Whereas your output shows the filters per person mine shows empty brackets. I am not able to know why or If I am doing something wrong... could you help me with this?
Hi
Happy to look via a screenshare. Check my Channel about section and connect me via teams.
Thanks
Hello, could you find a solution? I have the same issue
Thanks for the Video. Can you pls tell me from where you pasted the code on the Parse JSON portion? Thanks.
Watch 4:37
@@abm4975 Under Filter Array what to write? Between 6:14 to 6:16 there's code missing. Can you please help because I am finding my output blank. Thanks.
Sorry just saw this. Do you still need help?
Thanks
Hello, I have created a scheduled flow that runs monthly. I have two excel file in SharePoint, and I created the flow that will pull data from one excel file (let's say Excel 1) and paste the data to ther excel sheet (Excel 2). So, my flow is : Recurrence>List rows in a Table> Add a row in a Table. So for the very first month (January), it runs well, however when my flow runs for February, it also pulls January data again along with February data. How can I stop getting the data that is already pulled by the previous run? Please help me out.
If you use email in apply to each wont it send email number of times the loop runs?
Yes it does. Thanks
Suppose I want mail id once with corresponding records in next column then what needs to be done?
Hi Ritesh
Do you still need help in this? Please let me know.
Thanks
If I want to hv cc as the manager again unique. What additional steps would be there?
Hi
Use expression union() which will remove the duplicates.
Thanks
Sir is there a way we can get in touch with you, I want some issues and use cases where I need your help
Hi
Check my channel about section and connect me via teams.
Thanks
Hi. Tried this code today but it is sending separate email for same email id. Like i have 2 unique email but 5 line items so it send 5 times email. 1 time in 1 email and 4 time in same email id with same content.
Hi Monika
Was on holiday and catching up with work and all. Do you still need help in this?
Can you do the same process but instead of sending an HTML table, copy all the data in a separate excel spreadsheet and send each user the new spreadsheet created?
I have other vidoes about how to create an Excel table dynamically. Please have a look. Once the table is created in your new excel file then its easy to insert the rows. If not you need a new excel sheet with a header and a table.
Is there a way to send one mail to two email recipients (two columns of email instead of one in the excel file)? Please, i'd appreciate it :)
Yes you can. What you meant by two columns of email?
@@abm4975 Hi! I meant that I have an Excel table with N columns, 2 of them are called Email1 and Email2 and each column contains two different emails per row. I want to send them both one single mail containing the info. I don't care if one email it's CC or so, I'll take the easiest way :)
What if the list of email changes ? Will I have to update the schema every time ?
If the record changes then you don't have to do anything. If you change the column names yes thats schema change.
Thanks
Hi, can you show us one more step? How to add table formatting style after create HTML table? I copy the code and step "Compose" after create HTML table and before send an email, but it dosn't work. Thank you.
Hi
Please have a look at this excellent blog by Ryan.
ryanmaclean365.com/2020/01/29/power-automate-html-table-styling/
Thanks
Hello! I get my Filter array output empty, do you what could I have done wrong?
Hello sir, I have to select unique values from an excel and put it in an another excel. Can you please help me with that?
Hi
Thanks so much for the support.
I was away for few weeks. Do you still need help in this please?
Use union() expression and iterate the values using a for loop.
Thanks
@@abm4975 yess sir, thank you so much. I’ll try using that
Hello Sir, thanks for the guidance, I would like to ask some more guidance, how to filter Excel rows with a client code or unique email address and send them an HTML table with their list of invoices using Power Automate. I have a multiple clients' list in an Excel file, with columns for invoice dates, invoice amount, paid payment, remaining balance, and remarks. In the same Excel sheet, I have additional columns like a customizable subject column, a customer unique ID for each client, and email recipients (To and CC). Could you please guide me on how to filter the Excel rows based on the client code or unique email address and then send them an HTML table with their list of invoices using Power Automate?
Hi
This is already covered in this video. Could you please let me know what further help you need?
Thanks for the reply, i want send clients an html summary table using excel data set, which contains date , invoice numbers, balance
Excel data set also contains email address against each unique client id , cc , customized subject, that is like Subject: Customer Name , Customer id
By the way Thanks to you as i am already sending emails through power automate using ur guidance on yt video, to multiple customer but with only email content and payment reminder with just total outstanding balance. But HTML table would be more helpful as it will show exactly what invoice no. is due etc
Hi @neeminfo
Could you please contact me via teams. I could offer a screenshare and help. Check my channel About section and contact me via teams.
Thanks
Hie Amazing Video! How can we add people in CC?
Hi Alluri
Thanks for the support.
Send email action step click the show advanced options. You can see the CC. In my video the last step I used the send email. There you can see Show Advanced Options.
Thanks
6:26 what output you have selected?
Hi
Apologies for the late reply. I was away for a family emergency. Select the previous Compose action step and that's the output mapped here. If you need any further help in this please let me know.
Thanks
Thank you for the reply. Your video helped me to solve bigger problem at my work . Keep uploading such videos.
@@abm4975 I want to merge two Excel file and create a html table, data taken from both files . Can you guide?
Thanks
my excel seems to be to big, it only takes the first 274 lines
Hi Jonathan
You can increase the default limit by go to the settings and set the pagination limit.
Have a look at this
powerusers.microsoft.com/t5/Building-Flows/256-rows-limit/td-p/268351
Yes thanks, found it.
I use this for work now, thanks
My body is showing empty after filtering , need help.
Hi Sri
Happy to help. Could you tell me what you trying to filter?
If you could post your query under Power Automate Community thread I will have a look. A screenshot of your flow will be helpful. Tag me as @abm.
powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity
Thanks
@@abm4975 Did you solve this? I cannot find the solution on the MS Community Page... At minute 6:15 there is a point when your Filter array Output is an empty Body []
@@kayleaazealiix5262 Well noticed. Thanks for your question. That was edited part but I did mentioned about the filter just after item()?['schemaname']. If you need further help please let me know. Happy to help.
Hi Sir, i tried same steps but mails are sending multiple time please help here
I have 30 column data and need to send mails to 3 manager, under each manager have 10 employees, but when I send mail as per video it goes 30 times, each manager received 30 mails with set of 10 employees. Can you please help me here?
Hi Sandeep
There is something not quite right with your flow. Could you post this in Power Automate community and tag me as @abm. I will have a look.
powerusers.microsoft.com/t5/Using-Flows/bd-p/UsingFlows
Thanks
Hi.. im trying using this step.. it's work..wihout adding any cc emails. But when i try to add cc emails at the send email step, using 'item()['ccmail'] code, its sending with multiple email with same item. Note - 'ccmail' - pull from same excel list.. Kindly help! Thank You.
Hi @dilareman9791
Hi
I need to see your flow. I could offer a screenshare help. If you up for it please contact me via Teams. See my team email under the Channel about section.
Thanks
Hi, Thanks for the wonderful video. Lsst step which is sending an email doesn't work. It says array of emails cannot be converted to type string. Please answer this. Thank you
Hi Malaka
Check your mappings. You might be mapping the wrong dynamic content. That's the only reason it doesn't work.
If you need further help in this then please create a thread in Power Automate community and tag me as @abm. I will have a look.
powerusers.microsoft.com/t5/Building-Flows/bd-p/BuildingFlows
Thanks
And if I have to send email to Sports wise then what I have to do, ??, pls explain
when the same is implement on sports-wise then , emails send multiple times
Hi
Thanks so much for the support.
I was away for few weeks. Do you still need help in this please?
Thanks
Hi, thanks for the video. But i am stuck at 7:47 till filter by email is done while creating html tables its showing error.
Saying body/value is not available where as while getting data from excel its creating automatically eith body/value only and not like yours(body). I dont understand what is this, that is why its not converting table. The error says at line item 0 and column 0 no data available coz body/value doesnt exists as a output from filtering action.
same problem, did you manage to resolve it ?
Hi
I need to see your flow. Most likely your mapping is not quite right. Check my channel contact. Send me an email. Happy to do a screenshare and debug the error.
Thanks
Can someone help 6:13????
Hi
Apologies for the late reply. I was away for a family emergency. So you nee to add a Filter Array and map the Excel values. If you need any further help in this please let me know.
Thanks
Hi
When using the "List Rows in the table" function the source file is shared, how could I add it? Since it does not appear when selecting location automatically. Location: Rusel/Documentos/Oficina de Proyectos DUNO/Proyecto - DUNOHANA/07 DOCUMENTOS INTERNOS/CONTROL DE INCIDENCIAS DUNOHANA 2023.xlsx
Hi
Thanks so much for the support.
I was away for few weeks. Do you still need help in this please?
Thanks