This video tutorial shows how to update Excel with Microsoft Power Automate. Learn how to update multiple sheets simultaneously, add advanced conditions, and run Office Scripts - VBA Macros on steroids.
📂 Download the course materials: andersjensen.org/lesson/microsoft-power-automate/excel/
VIDEOS FOR YOU:
🔹 Microsoft Power Automate Beginners Course: ru-vid.com/group/PLXXz88_TPiHqGlJoWhBDCAxZ9m1N_jsB6
🔹 Advanced Invoice Processing in Power Automate: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-UH8jQTz-PMk.html
POWER AUTOMATE GROUPS:
👨👩👧👦 Discord: discord.gg/iloveautomation
- Join my network with 4400+ Automation/RPA developers, where we solve Automation/RPA problems, network, and help each other upgrade our careers. It's free!
👩🏻🎓 LinkedIn: www.linkedin.com/groups/12566435
👨🏻💻 Facebook: groups/754059285247921
FOLLOW ME:
💼 LinkedIn: www.linkedin.com/in/andersjensenorg
📸 Instagram: andersjensenorg
👨 Facebook: andersjensenorg
🐦 Twitter: andersjensenorg
💌 Email Newsletter: and
ersjensen.org/email-newsletter
0:00 Prepare the Data
Download the Excel book from the course materials. Then, place the Excel book in OneDrive so that Power Automate can access it. Our data is formatted as a table, which is required for Power Automate to read.
03:49 Read from an Excel Sheet
Use the List rows present in a table action to read the Excel data into Microsoft Power Automate. The result is a JSON, from which we can extract the information later.
8:04 Sum Values in Rows and Columns
Use the Apply to each action to iterate through Excel rows. Then the Update a row action adds two cells in each row together. Then use the Key Column and Value to map the data. Finally, create a Power Automate expression using the add function to add the two Excel values.
15:19 Subtract Values in Rows and Columns
The sub function can subtract two numbers in Excel with Power Automate.
18:22 Multiply Excel Values
The mul function can multiply two numbers in Excel with Power Automate.
20:13 Add a Row to an Excel Sheet
The Add a row into a table action adds a row to an Excel sheet.
23:00 ODATA Filtering
The ODATA filter approach in Power Automate is the simple approach. We can use the equal (eq), not equal to (ne), startswith and endswith. And we can't combine the filters.
25:59 Advanced Excel Filtering
You can use a Filter array action to completely customize your filter on the Excel data with Power Automate. And you can even combine the filters.
33:41 Excel Lookup
Use the Get a row action to perform an Excel Lookup. With a condition, we can handle errors in case the value doesn't exist. The condition uses a contains function, where we dynamically look for the lookup value. If true, we do the lookup afterward.
45:54 Update Multiple Excel Sheets
The Power Automate solution for updating tables on multiple Excel sheets is a bit more advanced. The Get Tables action will retrieve all tables from an Excel book. Then we iterate through each row in each table.
59:06 Provide the Item Properties
In the Update a row action, we need to update the fill the Provide the item properties parameter. It should be in JSON format.
1:06:29 Office Scripts and Power Automate
With Office Scripts, we can create advanced Power Automate solutions. We record our actions in Excel online and automatically have them as scripts. The scripts are written in TypeScript, a superset of JavaScript. I recommend learning TypeScript because Office Scripts are the future (VBA Macros will die). Not only can we create advanced Excel automation, but our Excel data doesn't need to be formatted as a table anymore. Edit the Office Script to take ingoing arguments, e.g., a sheet name. From Power Automate, use the Run script action to run an Excel Office Script.
#powerautomate #powerplatform #office365
15 ноя 2022
