Power Platform Workout 1 - Automating Data Workflows & Transformations

Workouts Power Platform
1

Automation is a game-changer in the world of data analysis. Power Automate, with its vast array of connectors and intuitive interface, offers the tools to streamline and automate your data processes. Dive into this workout to master the art of automating data workflows and transformations with Power Automate.

Scenario:

You’re a data analyst at a company that receives daily sales data in the form of Excel files emailed by various regional managers. You need to consolidate this data, clean it, and then load it into a central database for reporting. How can Power Automate help in automating this repetitive task?

Objectives:

By the end of this workout, you should be able to:

  1. Understand the capabilities and features of Power Automate in the context of data analysis.

  2. Set up automated workflows for data extraction, transformation, and loading (ETL).

  3. Address common challenges and optimize data workflows using Power Automate.

Interactive Task:

Given your understanding of Power Automate, answer the following:

  1. How would you set up a trigger in Power Automate to initiate the workflow whenever a new email with an Excel attachment arrives from the regional managers?

    • Your Approach: ________________________

  2. Once the Excel data is fetched, how can Power Automate assist in cleaning and transforming the data before loading it into the database?

    • Your Approach: ________________________

  3. How can you ensure that the automated workflow sends a notification (e.g., an email) in case of any errors or failures during the process?

    • Your Approach: ________________________

Questions:

  1. In Power Automate, which feature allows you to add conditions, switch cases, or loops in your automated workflows?

    • i) Data Connectors

    • ii) Control Actions

    • iii) Flow Modifiers

    • iv) Workflow Structures

  2. Why is Power Automate’s ability to integrate with a vast array of applications (e.g., SharePoint, SQL Server, Teams) crucial for automating data workflows?

    • i) It ensures data security and encryption.

    • ii) It reduces the cost of data storage.

    • iii) It facilitates seamless data flow and operations across different platforms.

    • iv) It enhances the visual appeal of the automated workflows.

Duration: 20 minutes

Difficulty: Intermediate

Period:
This workout will be released on Tuesday, September 5, 2023, and will end on Thursday, September 28, 2023. But you can always come back to any of the workouts and solve them.

3

Hello Team - Thanks for the Workout. Please find my responses below.

Interactive Task:

Given your understanding of Power Automate, answer the following:

  1. How would you set up a trigger in Power Automate to initiate the workflow whenever a new email with an Excel attachment arrives from the regional managers?
  • Your Approach

i) Create a new Automated Flow
ii) Add a new Trigger, when a new mail arrives
iii) In Advanced settings add condition for “Has attachment” as yes. Can add other filters also like for Subject, From etc.

  1. Once the Excel data is fetched, how can Power Automate assist in cleaning and transforming the data before loading it into the database?
  • Your Approach: Power Automate involves multiple option to clean up the data like.

i) Keeping only the needed column and ignoring other column.
ii) Adding Filter/Conditions to data, like only keep records where Amount > 100
iii) Adding Fitler to keep only yesterday Sales data etc.\

  1. How can you ensure that the automated workflow sends a notification (e.g., an email) in case of any errors or failures during the process?
  • Your Approach: For sending notification, one can use “Configure Run after” option available on each task. In case of Failure like “Has Failed” or “Has Timed Out”, trigger a new Action that will send notification to the users.

There might be other options but this is the one I have primarily used.

Questions:

  1. In Power Automate, which feature allows you to add conditions, switch cases, or loops in your automated workflows?
  • i) Data Connectors
  • ii) Control Actions
  • iii) Flow Modifiers
  • iv) Workflow Structures

ii) Control Actions

  1. Why is Power Automate’s ability to integrate with a vast array of applications (e.g., SharePoint, SQL Server, Teams) crucial for automating data workflows?
  • i) It ensures data security and encryption.
  • ii) It reduces the cost of data storage.
  • iii) It facilitates seamless data flow and operations across different platforms.
  • iv) It enhances the visual appeal of the automated workflows.

iii) It facilitates seamless data flow and operations across different platforms.

Thanks
Ankit J