How to Lookup EXCEL DATA with Microsoft Power Virtual Agents
Vložit
- čas přidán 31. 07. 2024
- Imagine the time and effort you could save by having your Microsoft Power Virtual Agents chatbot return data from your Microsoft Excel table!
In this Power Virtual Agents tutorial I’ll show you how to create a Power Virtual agents topic that calls on a Power Automate flow to look up information in an Excel Table and return it in a neatly formatted chatbot message.
If you aren’t familiar with creating topics in Power Virtual Agents-watch THIS VIDEO FIRST: • 🤖 CREATE A CHATBOT for...
IN THIS VIDEO:
✅ How to Add a Multiple Choice Question to Power Virtual Agents
✅ How to Create a Power Automate Flow for Power Virtual Agents
✅ How to Lookup Excel Data in a Table and Return Results to Power Virtual Agents
✅ How to use the Switch Action in Power Automate
✅ How to Format text in Power Automate for Power Virtual Agents
---
SKIP AHEAD:
0:00 Intro
0:27 Edit the Topic
1:41 Create the Flow
4:55 Set the Variable
8:20 Send Data back to the Chatbot
11:27 Chatbot Demo
12:03 Format the Output
SUBSCRIBE FOR NEW VIDEOS EVERY WEEK-ISH
👉 bit.ly/SubACreativeOpinion 👈
---
Teaching you how to make your workflows SUCK LESS.
Subscribe if you want to learn how to streamline your workflow, boost your productivity, and build innovative processes for yourself and your business! I wanna help you get more time in back your day.
Join me on my journey on learning new tools, levelling up my skills and creating more time to do the things that matter. I hope that I can inspire you to do the same and share all the things you learn from me with others.
Get curious. Be creative. Do better.
---
FOLLOW ME:
/ acreativeopinion
/ acreativeopinion
/ acreativeop
Have you considered importing your Excel data into a SharePoint list? If you aren't sure where to start-check out this video where I cover TWO WAYS to get your data from Excel into a SharePoint list: czcams.com/video/hQ5Tw3UL7ik/video.html
Also, while you're here... tell me what other information you'd like your Power Virtual Agents chatbot to look up in your Excel table. Leave a note down below! 👇
Sin esto ya estaría muerto👏👏👏
Thank you very much.
thanks for the video
Great video! I really enjoyed your explanation! I would love to see a video where you delve into the process of establishing a chatbot with a big data set
Thanks for watching. What type of data are you looking to use with your chatbot?
yine yapmışsın yapacağını eline emeğine sağlık harika
thanks for the Video..While i testing , the declaring variable "search" and their "type" is coming as default option of "choice" , but power automated input "type" coming as "string" due to that it showing the below error "binding incorrecttypeerror"..Kindly help for the same
Hi, Thank you so much for the amazing content. While trying the same I do not see DYNAMIC CONTENT and EXPRESSION options which is shown at 2:49 time. How can I get them. Can someone please help me with this.
On my results above the name its showing a returned result of phone number(Project Owner). It does this when I hit enter on the varProjectList but if I remove that line, the flow doesn't work.. any ideas?
Thank you very much, is a great video. The only thing is that sometimes is too rushed and give literally less than a second to see what or where you need to do.
But actually I ran into a problem: Every time I test the bot, it returns if it didn't find anything. basically only goes to the condition If yes and never to If no. Any suggestions what could it be?
Thanks for watching and for the feedback.
It's really hard to troubleshoot in the comment section. I would recommend posting in the PVA Community: powerusers.microsoft.com/t5/Get-Help-with-Power-Virtual/ct-p/pva_gethelp
Great video and just have a quick question if our name has a First name, and last name, can you still use the toLower command? or how do we get a result with our first name and last name? Thank you
Thanks for watching! The reason I use the toLower() function in my flow is to ensure all the text is in lower case is to ensure that the content being compared in the filter array action is in the same format. It converts all the content to lowercase letters, which means that uppercase and lowercase letters are treated as the same.
If you don't use the toLower() function and the text inputted from a user is written in uppercase or a different case, the comparison might fail because uppercase and lowercase letters are different. By using toLower() function, you convert everything to lowercase, making the comparison case-insensitive-increasing the chances of finding a match.
Hope this helps!
Hi,
Pretty nice content.
I was following smoothly until 10:47 and then I lost you, could you please explain where did you get the data that were shown in the upper part of the screen?
Thanks for watching! The data from the upper part of the screen is from the output of the filter array action. If you ran the flow as a test like I did in my tutorial, you should have a run where you can reference the outputs. Hope that helps!
Is there a way that when I enter "Give a list of details for project abc a bot will run a flow and get results? Thank you
Thanks for watching!
Can you pls post a topic in the Microsoft Power Virtual Agents Community: powerusers.microsoft.com/t5/Get-Help-with-Power-Virtual/ct-p/pva_gethelp.
It's really hard to troubleshoot in the YT comments section. Please tag me if you'd like me to be notified when you post. Thx!
is there anyway i could search a collum to verify, for example, if the e-mail the user gave is there?
Thanks for watching! It would depend on how your data is set up but you can either use a Filter Array query along with a length() function to check if any results are returned (like I have in my flow) or you can just use a condition action. Hope this helps!
Thanks for this amazing content. Is there a way to search array of values in filter array action ? For example, user input was “owenername1, ownername2”. In compose I have split this input by comma (split(triggerBody()[‘text_1’]),’,’).Can we search this array of values in Filter array ?
Thanks for watching!
Take a look at my YT Tutorial on the Filter Array action: Are you using the Microsoft Power Automate Filter Array Action wrong? - czcams.com/video/HVAf1aTEQNg/video.html
The first section of the tutorial shows you how to cross-reference an IDs from an Excel Table against those in a SharePoint list. You can use the same concepts and apply it to your flow. Hope this helps.
If not, be sure to post your issue in the Microsoft Power Automate Community: powerusers.microsoft.com/t5/Building-Flows/bd-p/BuildingFlows
Thanks for your quick reply, let me check this tutorial.
@@acreativeopinion I couldn’t able to achieve this, can you help me to build this logic without using foreach loop?
I only have 1 option and I did everything like you did except that I selected the users entire response. But everytime i search something, the message that nothing was found is coming. I have like 500 rows but even if i reduce them to under 260 it still isnt giving me the right answer. Any ideas why that would be? Thank you very much.
Thanks for watching! It's really hard to troubleshoot via YT comments. I would recommend posting a topic in the PVA Community: powerusers.microsoft.com/t5/Get-Help-with-Power-Virtual/ct-p/pva_gethelp
It will all depend on how your data is structured and what operator you are using in the Filter Array action. If you use the contains operator in the Filter Array action, it will look to match the string entered. I would recommend running tests on a separate flow using a manual trigger to see which strings of text will return your desired results. From that, you may need to adjust the selections available in your bot.
Hope this helps!
please help, I have 1200 lines of Excel data, but the bot is only able to retrieve data up to a maximum of 260 lines
Thanks for watching!
You can try to turn on pagination and set a threshold. I believe there is a limit of 5000 items at a time. I would also recommend filtering out your Excel data when possible so you aren't returning all rows from your table.
Very helpful video.
What if I don't have multiple options. What if I have only 1 option and want to show results "By Project Name"
Like soon after the trigger, bot should ask for "Project Name", and based on my entry I should get results if my entry is present in excel. If not, I should get a failure message
@@MrSHAREN123 Instead of using the Multiple Choice Options, select User's Entire Response. This would just take the user's response and store it in a variable which you can use in your Power Automate Flow.
If you want to send back a message if no projects are found you can do that by adding a condition and returning text back to Power Virtual agents through Power Automate.
Hope this helps!
Is it possible to make this work on several sheets?
Thanks for watching!
Yes, you should be able to make it work. You'll have to ensure that each sheet has a table. In Power Automate, you need to specify the table name (not the sheet name). Your flow will need to have logic built in to look up a specific table based on the user's input or selection.
Refer to this video: czcams.com/video/kEY6n-DP2Cg/video.html on how you can dynamically select a table in the List Rows Present in a table action.
Hope this helps!
Hi good one. I have an challenge where this same case instead of excel spreadsheet i need to retrieve data from a table in my power bi . Can you guide me ? Thanks in advance
Thanks for watching. Unfortunately, Power BI isn't something I'm familiar with yet (it's on my list) but I haven't gotten there.
I would imagine there is a way to pull data from Power BI. If so, there should be a way to filter out data similar to how I've done it in this video tutorial.
Sorry I couldn't be of more help.
@@acreativeopinion thanks i got it done !!!
Finally it’s live today 👍
HI, thanks for this video, I have a question, how the toLower(triggerBody()['text_2']) function should looks like in "Compose" action. There is no clarity under each of conditions. There ia only visible of text_x what variable did you chose ? Thanks
Thanks for watching! Sorry that I wasn't clear on what to select. If you pause the video at 5:12 you should be able to catch a glimpse of the dynamic content from the Power Virtual Agents.
In the Compose action I'm storing the dynamic content from Power Virtual Agents.
Hope this helps!
stressful content. too rushed and not enough explanation.
Thanks for the feedback!
Completely agree... It needed a lot of content and it would of been better
Great Video, but one thing I want to ask about, at the end of the bots flow as we say in the video you are getting respectively as outputs: Results, Results1, and Results 2 Besides we are also getting the following error:
"Error Message: The flow ‘How to Lookup EXCEL DATA’ (‘40b7fbe6-5f46-ee11-be6e-002248801012’) failed to run with response code ‘BadGateway’, error code: NoResponse. Error Code: FlowActionBadRequest Conversation Id: 18b5be94-68bf-486d-83cc-14e49854f708 Time (UTC): 2023-08-30T11:55:33.446Z"
When choosing by owner everything works fine, but when choosing other option in both cases we are getting this error
and here the error we got when we run history on the filter array
InvalidTemplate. The execution of template action 'Filter_array__-Category_' failed: The evaluation of 'query' action 'where' expression '@contains(tolower(item()?['go_category']), outputs('Compose-__Category'))' failed: 'The template language function 'tolower' expects its parameter to be a string. The provided value is of type 'Null'. Please see aka.ms/logicexpressions#toLower for usage details.'.
Please help
Regards,
Mario jreige.
Thanks for watching! It's really hard to troubleshoot via YT comments. I would recommend posting a topic in the PVA Community: powerusers.microsoft.com/t5/Get-Help-with-Power-Virtual/ct-p/pva_gethelp
However, from what I can tell it looks like whatever result you are trying to pass is empty. The error states that you are trying to pass a null value to the toLower() function.
Hope this helps!