Advanced Excel in Microsoft Power Automate Desktop - Use Case for Accountants

This advanced use case shows how to work with Excel in Microsoft Power Automate Desktop. We're solving an RPA process for accountants, where we want to extract web data to Excel and automate and manipulate it in Excel.
📂 Download the course materials: andersjensen.org/microsoft-po...
VIDEOS FOR YOU:
🔹 Save Web Data to Excel - Power Automate Desktop Advanced Use Case: • Save Web Data to Excel...
🔹 Power Automate Desktop For Beginners (4.5+ hours): • Microsoft Power Automa...
POWER AUTOMATE DESKTOP GROUPS:
👨‍👩‍👧‍👦 Discord: / discord
- Join my network with 4000+ Automation/RPA developers, where we solve Automation/RPA problems, network and help each other upgrade our careers. It's free!
👩🏻‍🎓 LinkedIn: / 12566435
👨🏻‍💻 Facebook: / 754059285247921
FOLLOW ME:
💼 LinkedIn: / andersjensenorg
📸 Instagram: / andersjensenorg
👨 Facebook: / andersjensenorg
🐦 Twitter: / andersjensenorg
💌 Email Newsletter: andersjensen.org/email-newsle...
0:00:00 Intro
In today's Microsoft Power Automate Desktop lesson, we solved an advanced Excel use case.
0:00:55 Extract Web Data to Excel
Download the course data to a desktop folder. Save the path because we'll use it in Power Automate Desktop as a variable. This is best practice, as we can use the variable in multiple actions, and it will be easy if we move the project and need to change the path. Use a Launch Excel action to open an Excel instance and a Close Excel action to save and close it again. The Set active Excel worksheet action is used to ensure we use the correct sheet. Read the Excel data into a DataTable. For each row in the DataTable, we want to do an internet search based on the values in a column, extract the web data and save it to DataTable and then Excel. The data we want to save is currency data from the Danish National Bank. Using the Extract data from web page action lets us automatically extract the currency data into a variable. Then, we can write the data to the Excel sheet with a Write to Excel worksheet.
25:21 Using Subflows
It's best practice to use subflows in Power Automate Desktop. We do it to keep our projects organized.
29:40 Advanced Excel Accounting
This part teaches you a lot of advanced Excel in Power Automate Desktop. We'll practice DataTable work, referencing, lookups, and manipulating data for each row of Excel sheets/tables. It targets accountants, but everyone can learn much from the use case. When you want to do calculations on Excel data, remember to convert the data to numbers, even though it looks like numbers, e.g., a Convert text to number action.
47:46 Four Eyes Check
Based on the values in the Excel sheet, our Power Automate Desktop process will send email warnings to the compliance department if the values are significant - a four eyes check. Send an Outlook email message with the Send email message through Outlook action.
52:52 Error Handling
We can make very advanced error handling in Microsoft PAD. For example, here, we check for a blank value, meaning we can't do a calculation.
#powerautomate #powerplatform #office365

[@andersjensenorg]

Thanks for all your support 🙌🔥 Please rate the video by giving it a thumbs up or down - that helps me a lot, thanks 😊

[@rmlalwani]

@Anders Jensen , your videos have been really helpful and beneficial .. thank you for your contribution and support ... can only appreciate

[@andersjensenorg]

Hey Rajesh - thanks a lot for such a nice comment - highly appreciated 🙌😊 Kind regards, Anders

[@geraldinedelacruz506]

Another great video tutorial, learned something new again from this. Thank you Anders for doing it! Truly helpful. ❤❤❤

[@andersjensenorg]

Hey Geraldine. Thanks a lot 🙌😊 All your support helps me a lot. Have you seen my video from today: kzbin.info/www/bejne/ip7IeJSuoNili6M where I go a little more advanced. Have a great day. Kind regards, Anders

[@fayratta5062]

Super helpful and well-explained. Thanks!

[@andersjensenorg]

So happy you could use it, thanks a lot 😎😍

[@camera0n_worries0ff]

Thank you for this tutorial Anders

[@andersjensenorg]

I'm happy you could use it, Akshay 🙌😍

[@HASAN_AL_ABD]

Thank you for all the good information. Please keep it up 👍

[@andersjensenorg]

My pleasure, HASAN 🙌😎

[@shaunrowley7230]

Thanks for this and it was great - my project that i am working on is that a customer sends us an Excel spreadsheet with stock to be imported but the columns vary each time they send to us as they cannot follow a template e.g. Barcode of product should be column B but this week is D and next week will be F although the column heading will be called Barcode. I then need to copy this data and write it to a CSV file and set the column for the barcode to a number with zero decimal places then save for our WMS to import very time consuming to copy and paste each time - hopefully this video will assist me in writing an RPA process using PAD

[@andersjensenorg]

Hey shaun, thanks a lot. Let me know, how it goes and you're very welcome on my Discord, where we help each other and network (4000+ RPA developers). Have a great day. Kind regards, Anders

[@lawrence5304]

Hello Anders ,do you give captcha courses,that will be a fun to watch. BT W,always enjoy your videos!

[@andersjensenorg]

Hey Lawrence. Thanks a lot. Yes, and it will be in my advanced PAD course, expected early 2023. So either you have to wait of find another teacher 😞 Have a great day. Kind regards, Anders

[@ALVINMAN452]

I saw a lot of your videos and this one it was for me a litle difficult, especally the Account part. But i saw it a few times to understand it.Very good video Thank you. Question: why my Excel sheet change its properties after i run the PAD, and after that that´s impossible to run again?

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 10,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@sweetyvakacharla8482]

Hi Anders, Thankyou!!! this poc helped me to understand most of the power automate commands. As u said that DKK is our base currency, it means that Amount DKK column is to convert all the exchange rate values of their currencies to DKK? If yes, how did we take the formula. Is that /100 means DKK rate value?

[@andersjensenorg]

Yeah, that's because the site, where we get the currency rates from use 100 of that currency (instead of 1). Thanks a lot, Sweety! 🙂

[@alsafeerq82009]

I didn’t see you use Subflows in your videos… i think it works like functions in programming if your flow has repeated work

[@andersjensenorg]

Hey Alsafeer_kw. Thanks a lot for the valuable comment. You're right, it's always nice to reuse. At the same time, when I created this video, it was also about keeping it simple, so left it out. In my "own" flows, I have subflows for init, logs, error-handling etc. Please continue with comments like this - they're gold. Have a great day. Kind regards, Anders

[@SANATANATION]

thankyou sir

[@andersjensenorg]

My pleasure, Shubham - glad you liked it 😊🙌

[@MM-li7uj]

Hi, your videos are great. Can I get data from the web that I have to log in?

[@andersjensenorg]

Thanks a lot 😊 So you want to scrape data, but you need to login first? Sure no problem. If you mean the data that I use in the video, sure they are publicly available. Kind regards, Anders

[@MM-li7uj]

@@andersjensenorg I can filter evaluations of my company from the web (employee login is required). I would like to insert this data into Excel, make it fancy and forward it to certain people by mail.

[@andersjensenorg]

No problem, but you need a few more actions in the process then.

[@brijendraydv]

Thanks but real challenge is how to schedule this on server PC machine? Could you pls make a video so far I have not seen anyone makit scheduling video

[@andersjensenorg]

Hey, brijendraydv. Thanks a lot. You need to make the connection to PA Cloud: kzbin.info/www/bejne/o6nJgHimiappntk and then you can easily schedule it from there. Let me know, if you have any questions. Kind regards, Anders

[@nareshkumar-qo9qy]

how to loop through each row of a data table in a webpage

[@andersjensenorg]

Hey naresh kumar Thanks for writing. I'm getting more than 50 questions/comments every day and having difficulty answering everyone (I try my best). I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: kzbin.info/www/bejne/lYK1emCPpJebbrc Have a great day. Kind regards, Anders

[@powerbinareal]

Top"! @powerbinareal