Automate your workflows with Power Automate and Azure Logic App

Why you need Power Automate and Azure Logic App for your enterprise


All companies are built by people. Their employees are the foundations that keep the business sustainable and growing. Ideally, your staff will spend most of their time to collaborate, share knowledge and be productive by focusing on your company’s core business. However, manual and repetitive tasks are still common and could limit your employees potential.


  • As a business decision maker, are you constantly waiting for your staff to update various data sources for your reports?

  • As a resource manager, have you been spending too much time on administrative follow ups of your employees?

  • As a process manager, are you overwhelmed by the email threads being exchanged to approve or reject specific requests?

  • As an administrative assistant, are you struggling to keep up with email requests to organize?


If those sounds familiar, and even if they don’t, you need to consider automating your workflows. Hence comes Microsoft Power Platform’s Power Automate, which was previously named Microsoft Flow.



What is the Power Platform?


Microsoft Power Platform overview diagram from docs.microsoft.com


Microsoft Power Platform comprise these applications:
  • Power BI: create dashboards and reports for data analysis from multiple sources.
  • Power Apps: easily build apps that can run on multiple devices, web or mobile.
  • Power Automate: automate workflow without code connecting to popular services.
  • Power Virtual Agents: quickly build chatbots, coding-free.

The Power Platform is designed to simplify the creation of fully functional solutions free of coding by business users. These products leverage the Common Data Service (CDS), an abstraction layer for secure data storage and management over Azure. The CDS enforces business rules and automation against the data, and simplifies the provisioning of resources on the Cloud, by exposing the Common Data Model (CDM), a set of standardized metadata definition to provide data consistency across applications and business processes.

Power Automate provides an user-friendly interface over Azure Logic Apps, the first targeting business users and the later, IT professionals and developers.


What is Azure Integration Services?


Azure Integration Services overview diagram from Microsoft white paper



Azure Integration Services is a set of cloud services for enterprise integration:
  • API Management: an API service that handles requests volume, security control, responses caching and monitors usage patterns.
  • Logic Apps: serverless technology to create multi-steps processes to connect two or more applications, for system-to-system or user-to-system workflows.
  • Service Bus: an enterprise message queueing system to allow non-blocking interactions between application components.
  • Event Grid: a highly scalable service that invokes receivers when a particular event has occurred, removing the need for applications polling.

Azure Integration Services lets you connect cloud and on-premises applications, but the real value is when all core services are used together. As an example, your customers submit orders through your Web site, going through the Service Bus they then generate an Event Grid which triggers a Logic App to execute your business process. This could involve querying your CRM, updating on-premises inventory and creating transactions on SAP. By leveraging the Azure Integration Services, your IT team could save a lot of development time and simplify maintenance.

Why automate workflow?


Going back to our workflow and process challenges, we will focus on Power Automate and Azure Logic App. So how can Power Automate or Azure Logic App help your workflow processes?


Use CasePower Automate / Azure Logic App Example
Decision maker has to wait for a report.Update Power BI dataset when a file is updated.
Resource managers need to follow up constantly.Track Microsoft Forms responses in Sharepoint and send notification.
Process manager overwhelmed by approval threads.Use approval email workflow to redirect to proper channel.
Administrative assistants with too many email requests to organize.Filter and save email content or attachments automatically into Sharepoint or organized folders.


These are just a few basic examples of the functionalities available. More advanced features include:

  • Track Tweets about a particular keyword and stream their sentiments to Power BI for analysis.

  • Open a bug in Azure DevOps and get an email notification whenever a rising trend in error traces is detected.

  • Request approval from your team members whenever a new document is uploaded in Sharepoint, via Teams.

  • Send an approval request when a purchase order is created in Microsoft Dynamics 365 Business Central, and on approval, take additional actions on that purchasing document.

  • And many more possibilities you can create with the available connected services.


How to design a workflow?


You can design your workflow from scratch or find a suitable predefined template among the vast collection offered by Power Automate. They are designed mainly for desktop applications, such as Productivity or Data collection. This is the recommended method to start your workflow; find the closest to what you wish to accomplish, then you can modify the generated template with the user-friendly designer, with simple clicks and edit.


Power Automate offers many pre-defined templates


Azure Logic App also provides its own set of pre-defined templates, though the options are limited and primarily designed for technical processes, such as Enterprise integration and Schedule. However most if not all, connectors available on Power Automate can also be found on Azure Logic App.



Azure Logic App provides its own set of templates, more technical-oriented


If you create a custom workflow on Power Automate, it can be exported to a JSON file which can then be imported into Azure Logic App. This is a great way to migrate a custom workflow created by a Business user; for example, to be evolved into a more elaborate process and then shared with the team.

Every flow starts with a trigger, either instant or scheduled, followed by a series of actions. Triggers and actions, may or may not establish a connection to a data source. Examples of triggers include, a file created or HTTP request received, a record created in SQL Server or Dynamics 365. Triggers can also be initiated from other vendor products such as Salesforce, Bitbucket or Gmail and more.

Both platforms offer similar connectors, triggers and actions.


A partial view of the supported connector and triggers in Power Automate



A partial view of the supported connector and triggers in Azure Logic App


The designer interface on both platforms is very user-friendly and identical in use, as you can see below. You edit a trigger or action by simply clicking on the box which expands with the properties available for customization. Additional settings can be updated by clicking on “...”. To insert new actions, click on the “(+)” or “New Step” button.



Workflow example in Power Automate, the designer is similar to Azure Logic App



To test the execution of your workflow on Power Automate, click on “Test”; on Azure Logic App, click on “Run”. In both cases, this action will execute your workflow.


Some tips for designing workflow:

  • Your workflow must be in Enabled state to be executed.

  • Always disable your workflow after design and test; this will ensure your recurring trigger does not automatically start your process or that it remains offline.

  • Azure Logic App keeps a history of every version you saved, so you can always go back to a previous version in case of accidental changes. This is not available on Power Automate, so remember to use the “Save As” feature to make backups.

  • Both platforms allow you to export the template to a JSON file, although some restrictions may apply to Power Automate.


The exported JSON template is a great option for IT professionals and developers to leverage ARM templates automation. By using Azure CLI or PowerShell, you can automate the creation of your Azure resource, along with Logic App.


What about on-premise connectivity?


Many enterprises may still be running an hybrid architecture, with part of their servers and databases still hosted on-premise, while new services are created on Azure. If your Power Automate or Azure Logic App requires a connection to data located on-premise, you do have a few options:

  • Create a Site-to-Site Virtual Private Network (S2S VPN) that establishes a connection between two endpoints: a VPN device on the on-premise network and an Azure VPN gateway on the Azure VPN. Traffic will travel over the public internet.

  • For better security and reliability, use ExpressRoute, which is a direct connection between your organization and Microsoft’s network. Traffic does not travel over the public internet.

  • To keep your architecture simple, you can install an on-premises data gateway. This will handle encrypted communication between the on-premises data gateway and Azure gateway cloud service, allowing Power Automate and Azure Logic App to access your organization’s data sources.


Power Automate or Azure Logic App?


If you are a productivity user, go with Power Automate; the Power Platform will manage all the required Cloud resources. You do have the option to share a workflow with others, granting them access to edit and execute your template.


If you are an IT professional or developer, the preferred solution is Azure Logic App. The benefits include:

  • Managed versioning of your Logic App on Azure portal.

  • Development using Visual Studio or Visual Studio Code.

  • JSON templates and parameter files for customization.

  • Deployment automation with Azure CLI or PowerShell.


And because you will be developing the solution for other users, the deployment model on Azure allows you to centralized the maintenance of your application without granting users access to modify your workflow.


Power Automate workflow is suitable for individual users who need their own custom processes. Azure Logic App is for workflow shared by multiple users or complex processes maintained by IT. This decision is also driven by their specific pricing model.


  • Power Automate license by user: $15 USD per user per month.

  • Azure Logic App is charged by execution: $0.000025 USD per action, and $0.000125 USD per Standard Connector.


Follow the links under References for more details on their pricing. Unless you have few power users who are creating a lot of workflows on Power Automate, Azure Logic App will be the most economical solution.


This article was an overview of Microsoft Power Automate and Azure Logic App, offering a brief comparison. I encourage you to log on Microsoft Flow and Azure Logic App to try them out and explore their potential for automation of your business.




Note

There is a bug caused by the workflow designer interface with the action “Create HTML table”. The property “Columns” is reset to “Automatic” each time it is open in the designer. If your workflow does not use the default value for this action property, you will need to edit each time you modify the workflow in the designer UI.



- Eric Chan



References

What is Common Data Service?

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-intro


Common Data Model

https://docs.microsoft.com/en-us/common-data-model/


About Common Data Service

https://docs.microsoft.com/en-us/power-platform/admin/wp-cds-for-apps


Azure Integration Services white paper

https://azure.microsoft.com/mediahandler/files/resourcefiles/azure-integration-services/Azure-Integration-Services-Whitepaper-v1-0.pdf

On-premises data gateway architecture

Power Automate pricing

https://canada.flow.microsoft.com/en-us/pricing/

Logic Apps pricing

https://azure.microsoft.com/en-us/pricing/details/logic-apps/



Visualize Covid-19 on Python Notebook

Create your first Python notebook to analyze the Covid-19


The Covid-19 pandemic shocked our entire world but also brought interest in data science and predictive analytics. John Hopkins University of Medicine led the field by compiling and releasing worldwide cases statistics on their well-known dashboard. It was an opportunity for myself to explore Python and its capabilities for data wrangling and data visualization on Covid-19 data when it became pandemic. In this article, I will share my beginner experience and walk you through how to create your first Python chart.



Canada Covid-19 Monthly New Cases and Deaths


Install Visual Studio Code, Python and Jupyter Notebook support


Microsoft Azure Notebooks preview has been available for free, unfortunately it will be retired on October 9th, 2020. It did provide a great web-based platform to develop IPython-style Notebook worry-free of software installation and configuration. Instead, I strongly recommend to use Microsoft Visual Studio Code, a powerful open-source code editor with wide-range of extensions. It is available for Windows, Linux and Mac OS, with built-in support for Source Control Management (SCM) Git.


  1. Visit https://code.visualstudio.com/ to download Visual Studio Code.

  2. Launch Visual Studio Code.

  3. In Extensions, search for Python; install the extension published by Microsoft, it includes support for Jupyter Notebook.

  4. You may need to restart the editor.


Get your Covid-19 data source


Several data sources on Covid-19 are available on GitHub, however I prefer the files shared by Tableau Software on Data.World. I find their CSV file easier to work with and though their data is updated daily only, this frequency is sufficient for our Python project.


Their data is sourced from The New York Times, the European Centre for Disease Prevention and Control, and the Public Health Agency of Canada. Not all countries' data may be included.


  1. Visit https://data.world to create your account.

  2. Go to this page https://data.world/covid-19-data-resource-hub/covid-19-case-counts.

  3. On the file COVID-19 Activity.csv, click the Download button, then select Share URL.

  4. Copy the link, it will be used to retrieve the data in your Python notebook.


Feel free to explore the file using Data.World viewer to get yourself familiar with the columns and data format of the file. The site also provides a convenient online querying tool to browse the data file.


Design your Python Notebook on Visual Studio Code


In Visual Studio Code, start a new Jupyter Notebook; use the command Ctrl+Shift+P then type Python: Create New Blank Jupyter Notebook to open a new .ipynb file.


Jupyter Notebook on Visual Studio Code


The box where the cursor is located is called a Cell. Notebooks support two types of cells: Code and Markdown. To toggle a Cell between the two types, click on .


Code cells are for Python coding, while Markdown is a lightweight markup language for text formatting. Markdown is commonly used as .md extension files across SCM platforms like Git or GitHub. Markdown cells are convenient to present and describe the results of your Notebook.



Markdown language syntax and rendered output


Markdown cells will be automatically rendered when you leave the cell. On Code cells, click on to execute the code. New Cell is added for you, but you can insert a new Cell by clicking on the + button.


Enter the code below into your Code cell, replacing the URL you copied from Data.World. This script imports the popular pandas library and stores the CSV content into a Data Frame, Python’s grid data store structure for easy manipulation.



# Import pandas library.

import pandas as pd

 

# Define the columns to keep.

columns = ['REPORT_DATE', 'COUNTRY_SHORT_NAME', 'PROVINCE_STATE_NAME', 'PEOPLE_POSITIVE_CASES_COUNT', 'PEOPLE_DEATH_COUNT']

 

# Retrieve CSV file into Data Frame.

df = pd.read_csv('https://query.data.world/YOUR-OWN-URL', parse_dates=True, skipinitialspace=True, usecols=columns)

 

# Filter on Canada data only and store in another Data Frame.

canada = df.query('COUNTRY_SHORT_NAME=="Canada"')

 

# Explicitly format REPORT_DATE column to datetime.

canada['REPORT_DATE'] = pd.to_datetime(canada['REPORT_DATE'])

 

# Fill NaN count with 0.

canada['PEOPLE_POSITIVE_CASES_COUNT'].fillna(0, inplace=True)

 

# Display top 5 rows.

canada.head()



Execute the Cell; an asterix [*] on the left of the Cell indicates the code is being executed. A sequential number will appear when it has completed, indicating how many cell runs completed so far within your Notebook. The output is shown below.




Rendering a Data Frame to a graphical chart is fairly simple in Python. However, we need to pivot the province and territories into columns, then the plot() method can easily produce the line chart. Run the code below in a new Cell.



# Pivot the Province and Territories into columns and store into another Data Frame.

cases = canada.pivot(columns='PROVINCE_STATE_NAME', index='REPORT_DATE', values='PEOPLE_POSITIVE_CASES_COUNT')

 

# Plot the pivoted Data Frame into chart, lines by default.

cases.plot(figsize=(20, 10), title='Canada Covid-19 Cases')


This will produce a multiple lines chart by province and territories for Canada Covid-19 cases. You may need to click the Run button a second time to see the actual chart.


Canada Covid-19 positive cases by province and territory timeline


Due to the huge population discrepancy between provinces and territories, this chart is not particularly interesting. Also there was no real data prior to March 2020. Let’s apply an additional filter and group some provinces and territories. Enter the following code in a new Cell then execute.



# Ignore data prior to March 2020.

groupCases = cases.query('REPORT_DATE >= "2020-03-01"')

 

# Group Maritimes Provinces

maritimes = ['New Brunswick', 'Newfoundland and Labrador', 'Nova Scotia', 'Prince Edward Island']

groupCases['Maritimes'] = groupCases[maritimes].sum(axis=1)

groupCases = groupCases.drop(maritimes, axis=1)

 

# Group Western Central Provinces

westCentral = ['Manitoba', 'Saskatchewan']

groupCases['West Central'] = groupCases[westCentral].sum(axis=1)

groupCases = groupCases.drop(westCentral, axis=1)

 

# Group Northern Territories

territories = ['Northwest Territories', 'Nunavut', 'Yukon']

groupCases['Territories'] = groupCases[territories].sum(axis=1)

groupCases = groupCases.drop(territories, axis=1)

 

groupCases.head()



The resulting Data Frame has grouped some provinces and territories together. We also filtered out rows prior to March 2020.




Add a new Cell then generate the chart with the grouped cases.



# Plot the grouped Data Frame into chart, lines by default.

groupCases.plot(figsize=(20, 10), title='Canada Covid-19 Cases')


The new lines chart with grouped provinces and territories looks like below example.


Canada Covid-19 positive cases timeline


Python Data Frame includes many useful methods. Next we want to visualize the latest total deaths count. To do so, we find the last REPORT_DATE to filter the data on. Run the code below.



# Get latest REPORT_DATE available.

maxDate = canada['REPORT_DATE'].max()

 

# Filter on latest REPORT_DATE and store in another Data Frame.

deaths = canada.query('REPORT_DATE==@maxDate')

 

# Fill NaN count with 0.

deaths['PEOPLE_DEATH_COUNT'].fillna(0, inplace=True)

 

# Display top 5 rows.

deaths.head()



For this data visualization, we plot into bar charts and add the formatted date. To display the value of each bar on the chart, use annotate() along with patches to get the coordinates of the bars. On a new cell, input then execute this code.



# Visualize total deaths by province and territory with formatted date.

ax = deaths.plot.bar(x='PROVINCE_STATE_NAME', y='PEOPLE_DEATH_COUNT', title='Canada Covid-19 Total Deaths on ' + maxDate.strftime("%Y-%m-%d"), figsize=(20, 10))

 

# Display count value atop each bar, offset the top position by 100.

for p in ax.patches:

    ax.annotate(str(p.get_height()), (p.get_x(), p.get_height() + 100))



The execution produces a bar chart of the total Covid-19 deaths by province and territories, with the total count value atop each bar.


Canada Covid-19 total death count by province and territory


As you can see, Python on Jupyter Notebook is a powerful language and tool to easily wrangle and visualize data. By adding Markdown cells, you can integrate detailed analysis or explanation of the output results. IPython-style Notebook is also used on Databricks, a Spark-based distributed computing data platform available on Azure. Did this exercise help you a quick start into Python? Let me know.


- Eric Chan



Related links


John Hopkins University of Medicine Coronavirus Resource Center

https://coronavirus.jhu.edu/map.html


Coronavirus (Covid-19) Data Hub

https://data.world/covid-19-data-resource-hub


Azure Notebook

https://notebooks.azure.com/


Visual Studio Code

https://code.visualstudio.com/




Automate your workflows with Power Automate and Azure Logic App

Why you need Power Automate and Azure Logic App for your enterprise All companies are built by people. Their employees are the foundations t...