• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Sco Openserver
    • Sco Unix Support
    • SCO Openserver Installation ISOs
    • SCO Openserver Binaries
    • Add a Hard Drive to an Openserver 5 Virtual Machine
    • Install SCO Vision SQL-Retriever ODBC Driver on Windows 10
    • License Expired on Virtual SCO Openserver Installation
    • How to reset the root password on SCO Openserver 5
  • Scripting
    • PowerShell
      • Add leading zeros
      • Check if a File Exists
      • Grep with Powershell
      • Create Environment Variables
      • Test for open Ports
      • Append to a Text File
    • VBScript
      • Check if a File Exists
      • lpad and rpad functions
      • Windows Update E-Mail Notification
  • Office 365
    • Connect to Office 365 with PowerShell
    • Add or remove an email alias using Powershell
    • Change Primary email address of Active Directory user
    • How to hide an AD user from the Global Address List
    • How to hide mail contacts from the Global Address List
    • Change the primary email address for an account with PowerShell
    • Change Primary email address of an AD User
    • Grant a single user access to access to all calendars
    • Forward email to an external address using Powershell
    • Convert shared mailbox to user mailbox with Powershell
  • Get in Touch
  • About Me
    • Privacy Policy

Power Platform

How to generate a chart with Power Automate

February 26, 2021 by Paulie Leave a Comment

I recently discovered QuickChart and it is a fabulous tool for generating charts. It’s really easy to use from both PowerApps and Power Automate. In this post I am going to explain how to use it to generate charts with Power Automate using data from a SharePoint list.

Here is a sample of the SharePoint List:

SharePoint list used as data source for a chart generated in Power Automate

Here are two samples of the Output Charts that are produced:

Sample Doughnut Chart generated by Power Automate and QuickChart.io
Another Sample Chart generated by Power Automate and QuickChart.io

The charts are embedded into an email that produces a summary of the items in the list for that week. So let’s get on with it.

The HTTP action is used to make an API request to QuickChart, so a premium license is required.

The first chart above was created with a compose action and this HTTP Request:

A Power Automate Compose action which builds the HTTP content for a request to QuickChart.io
A HTTP Request from Power Automate to QuickChart.io

So the complete JSON body looks like this when submitted:

{
  "backgroundColor": "white",
  "width": 500,
  "height": 250,
  "format": "png",
  "chart": {
    "type": "doughnut",
    "data": {
      "datasets": [
        {
          "data": [
            4,
            3,
            2
          ]
        }
      ],
      "labels": [
        "John Lilleystone",
        "Paul Murana",
        "Mohammed Islam"
      ]
    },
    "options": {
      "title": {
        "display": true,
        "text": "Issues by Assignee"
      }
    }
  }
}

The easiest way for you to generate the required JSON is to use the chart maker on QuickChart.io.

The most difficult part in this flow is reshaping the data from the SharePoint list, this is obviously highly dependent on what data you are trying to bring out into the chart so it’s difficult for me to explain, but this video should give you a good idea of how you can achieve it.

The other part you may get stuck on is embedding the image as Base64 into the email. I’ve got a separate post on how to embed an image in an email here.

I produced a weekly sales summary for a customer, which goes out to them every Friday:

Sample Bar Chart generated by Power Automate and QuickChart.io

I would be interested in hearing how you get on generating charts with Power Automate. Let me know if you come up with some cool use cases.

Filed Under: Power Platform Tagged With: Power Automate

Power Automate: How to parse a CSV File to create a JSON array

February 19, 2021 by Paulie 6 Comments

There are no built in actions in Power Automate to Parse a CSV File. There are external connectors which can do this for you, but this blog post will cover how to Parse a CSV in Power Automate without the use of any external connectors. The aim is to end up with a JSON array that we can use in other actions. I’ve exported this flow and you can download it here.

Here is a video explanation of the process:

For the purposes of this blog post, the sample CSV will have the following contents:

Session Data by SSID,,,,,
SSID,Session Count (%),Client Count (%),Duration (%),Total Usage (%),Usage (In/Out)
test1,90 (54.22%),26 (48.15%),1d:11h:35m (62.06%),939.09MB (50.69%),814.94MB/124.15MB
-,36 (21.69%),13 (24.07%),0d:2h:55m (5.09%),0.00B (0.0%),0.00B/0.00B
test2,21 (12.65%),13 (24.07%),0d:8h:35m (14.97%),538.12MB (29.05%),500.54MB/37.58MB

We are going to transform it into this:

[
  {
    "SSID": "test1",
    "sessionCount": "90 (54.22%)",
    "clientCount": "26 (48.15%)",
    "duration": "1d:11h:35m (62.06%)",
    "totalUsage": "939.09MB (50.69%)",
    "usage": "814.94MB/124.15MB"
  },
  {
    "SSID": "-",
    "sessionCount": "36 (21.69%)",
    "clientCount": "13 (24.07%)",
    "duration": "0d:2h:55m (5.09%)",
    "totalUsage": "0.00B (0.0%)",
    "usage": "0.00B/0.00B"
  },
  {
    "SSID": "test2",
    "sessionCount": "21 (12.65%)",
    "clientCount": "13 (24.07%)",
    "duration": "0d:8h:35m (14.97%)",
    "totalUsage": "538.12MB (29.05%)",
    "usage": "500.54MB/37.58MB"
  }
]

The first thing to note is that the first two lines of this CSV need to be excluded, because they do not contain any data. So lets get started!

Step 1 – Get the CSV Data and Split it into lines

The first thing is to get the CSV data and split it into lines:

Image showing Power Automate actions retrieving a CSV file and splitting it into an array of lines.

This compose action will use the split function to convert the original CSV into an array of lines, it will now look like this:

[
  "Session Data by SSID,,,,,",
  "SSID,Session Count (%),Client Count (%),Duration (%),Total Usage (%),Usage (In/Out)",
  "test1,90 (54.22%),26 (48.15%),1d:11h:35m (62.06%),939.09MB (50.69%),814.94MB/124.15MB",
  "-,36 (21.69%),13 (24.07%),0d:2h:55m (5.09%),0.00B (0.0%),0.00B/0.00B",
  "test2,21 (12.65%),13 (24.07%),0d:8h:35m (14.97%),538.12MB (29.05%),500.54MB/37.58MB",
]

The expression I used was:

split(outputs('Get_file_content_using_path')?['body'], decodeUriComponent('%0A'))

If your file is stored in SharePoint you will use the action “Get file content”, so the expression will be:

split(outputs('Get_file_content')?['body'], decodeUriComponent('%0A'))

This flow uses only compose actions. There is a very good reason for this which I will come to later. There are no variables whatsoever.

Important note regarding line endings

I used the decodeUriComponent function, to split the CSV.

decodeUriComponent('%0A')

This represents a new line feed character (LF), often displayed as \n. This is the Unix standard.

CSV Files generated in Windows, may use this format but often use a carriage return and line feed (CR+LF). This is represented as \r\n.

The split expression above will still work with CR+LF, but you will be left with \r characters in your data. The correct expression to split on a CR+LF is:

decodeUriComponent('%0D%0A')

If you load your CSV file into Notepad you can easily see which format your file is in, in the bottom right hand corner it will show either “Unix (LF)” or “Windows (CR LF)”.

Image of notepad with a CSV file loaded to determine the format of the line endings.

Step 2 – Process each line of the CSV and create JSON record for each line

Now that we have our CSV formatted as an array, we can loop through each line. Take a look at this loop:

Image of Power Automate actions splitting the line of a CSV file and creating a JSON object from it.

In the select an output from previous steps I used an expression, which requires a bit of explanation. The expression is:

skip(outputs('splitNewLine'),2)

The skip function returns an array, but removes items from the beginning of the collection. My sample CSV had two lines at the beginning which I did not want to include. So by using the Skip function, they will not be sent into the apply-each loop.

This loop will process each line individually, so every iteration will be working with a single line.

The first compose action splits the incoming line on a comma, here is the expression:

split(item(), ',')

This will produce yet another array, within the loop, which contains each of the column values, sample from example above:

[
  "test1",
  "90 (54.22%)",
  "26 (48.15%)",
  "1d:11h:35m (62.06%)",
  "939.09MB (50.69%)",
  "814.94MB/124.15MB"
]

The next compose action, called JSON is more interesting. First, manually build a JSON string which represents an empty record, like this:

{
  "SSID": "",
  "sessionCount": "",
  "clientCount": "",
  "duration": "",
  "totalUsage": "",
  "usage": ""
}

Then, within the quotes for each column header you can use an expression to access each element of the splitByComma action:

outputs('splitByComma')?[0]

This expression represents the first element of the array produced by the previous compose step. Arrays in Power Automate are numbered from zero. My complete compose action has this code:

{
  "SSID": "@{outputs('splitByComma')?[0]}",
  "sessionCount": "@{outputs('splitByComma')?[1]}",
  "clientCount": "@{outputs('splitByComma')?[2]}",
  "duration": "@{outputs('splitByComma')?[3]}",
  "totalUsage": "@{outputs('splitByComma')?[4]}",
  "usage": "@{outputs('splitByComma')?[5]}"
}

This is everything you need to include in the loop. You might be wondering why this is of any use as the JSON data has not been added to a variable, so you have no method of accessing it! A secret of apply-each loops is that they output an array of output, so our final compose step has the simple expression:

outputs('JSON')

This will compile all of the results from the compose action within the loop, into a nice array containing JSON objects.

Performance

The flow above will work fine, but you may find it takes a long time to execute with a large CSV file. Because no variables have been used, only compose actions, the concurrency setting of the loop can be adjusted so that it will work on many records simultaneously.

Animated gif of an apply each loop in Power Automate showing how to change concurrency settings for the loop.

With the three sample rows, the flow took just 1 second to run without concurrency enabled.

I increased the number of rows to 500, and without concurrency it took 1 minutes and 46 seconds to execute.

Enabling concurrency with 30 degrees of parallelism reduced the execution time to 7 seconds! Not a bad improvement.

Image showing performance difference for a Power Automate flow used to Parse a CSV with and without concurrency enabled.

The Complete Flow

Here is an image of the entire flow, to make your life easier I have exported the flow. You can download it here, import it into your environment and modify to your requirements:

Image of an entire Power Automate Flow used to Parse a CSV file and create an array of JSON objects.

This is a very simple flow, which you can recreate easily. You could add a Parse JSON step after the final compose and then the contents of your CSV will be available as dynamic content in the remainder of your flow.

I hope this helps you to Parse your CSV files, reach out if you need any help!

Filed Under: Power Platform Tagged With: Power Automate

How to embed an image into an email with Power Automate

February 17, 2021 by Paulie Leave a Comment

This post explains how to embed images inside an email sent from a Power Automate flow. This is a common requirement and can cause frustration depending on the requirements.

Generally, people want to include images stored within SharePoint, and this will be the basis of this blog post.

There are a few different methods, and I will explain the differences as we go through the post. If you prefer, here is a video explanation:

The file I am going to use for my test is in a SharePoint document library, it is called powerAutomateTestImage.png:

Image used in Power Automate Flow to test embedding an image

The full link to the file is:

https://accendo1.sharepoint.com/sites/PowerAutomateText/Images1/powerAutomateTestImage.png

Provide a simple link to the file in your email

This scenario is simple. On a client that is signed in to Office 365, you can specify the URL of the image in SharePoint and it should show in the email client:

Power Automate Flow showing the most basic method to embed an image into an emal

This works well in the Outlook Web client, but not in other clients as the file is not reachable. So unless all of your clients are using Outlook on the Web, I do not recommend this method.

Embed the image content directly into the email with Base64

This method is much more reliable as the actual content from the image is sent within the email. So there is no need for the receiver to retrieve the image from SharePoint.

Image showing Power Automate Flow that embeds an image into an email using base64

The file content is retrieved with the “Get file content using path” step. Then injected into the email as base64, with these expressions:

outputs('Get_file_content_using_path')?['body']['$content-type']
outputs('Get_file_content_using_path')?['body']['$content']

The complete image expression would be:

<img src="data:@{outputs('Get_file_content_using_path')?['body']['$content-type']};base64,@{outputs('Get_file_content_using_path')?['body']['$content']}" />

This method works really well, however some mail clients do not support base64 encoded images within the email body. For example, gmail does not show any base64 encoded images.

Another disadvantage of using this method is that the if the image is large, the email will large also. So be mindful of the size of the image that you are sending.

Use Power Automate as a HTTP Web Server

The most compatible way of embedding images in an email is to a publicly available image on the internet.

Uploading the image to a web server or CDN is best. But if you really want to use SharePoint as your source, it can be done. Its quite easily achieved by creating a flow which will serve image content via HTTP.

See this very simple flow:

Image of a Power Automate Flow that serves email images via HTTPs

This flow responds to a HTTP request with a reference to the Image in the URL. The picID in the URL refers to the ID of the image in the SharePoint document library:

Image of SharePoint document library used to store images.

The link to my HTTP Server Flow is:

https://prod-48.westeurope.logic.azure.com/workflows/59c5a92ccebe4f97adfd52eb6cf213a8/triggers/manual/paths/invoke/picID/{picID}?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=DNrMaouJY_Sifq8DRvbeWcuDl8TGKgeFpvP9NxDmOcQ

By modifying the {picID} to the ID of the image you want to serve, your private SharePoint Image will be accessible to the public. The test image used in this flow is ID 24, so this is the link to the dynamically served image. By changing the ID in the URL, a different image would be served to the client.

So now, getting back to our original email flow, we can modify it like so:

Image of Power Automate flow linking to to a Power Automate HTTP action to download embedded email images.

Now I can send the email to almost any client and it will be able to display the image. There are a couple of downsides to this method:

  • It requires a Premium Power Automate license to use the “When a HTTP Request is received” action.
  • The flow will execute every time someone looks at the email. If you are sending the email to many recipients, it will use a lot of flow capacity.

You could modify this flow to track if emails have been read, by embedding the recipient address in the URL. When the email is served to the client, you know the email has been read.

So what method should you use?

The method you should use to embed an image mostly depends on the capabilities of the recipient:

  • Don’t use the first method, unless every one of your recipients is using Outlook Web Access.
  • The base64 embedding method works well for the majority of clients.
  • The HTTP method works well for all clients. But is more work to implement and not suitable if you are sending to many recipients at once.
  • For transaction emails, I recommend using Sendgrid with Power Automate instead, it is much easier and produces a beautiful result.

Let me know in the comments how you get on and what method you choose!


Filed Under: Power Platform Tagged With: Power Automate

Power Automate – How to extract numbers from a string

February 10, 2021 by Paulie Leave a Comment

Power Automate does not have a function to extract numbers from a string. I’ve seen this requirement come up a few times in the Power Automate community forums so thought I would post the solution here. Here is an image of the flow:

Image of a Power Automate flow which extract numbers from a string.

It works by creating a do until loop that looks at each character of the string that contains the number. In this case a compose step called “string”. If the character is equal to 0 to 9 or a period then it is appended to the variable called newString, otherwise it is discarded.

Most of the work happens in the append to string variable step, which contains the expression:

concat
(
if(equals(substring(outputs('string'), variables('index'), 1), '0'), '0', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '1'), '1', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '2'), '2', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '3'), '3', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '4'), '4', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '5'), '5', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '6'), '6', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '7'), '7', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '8'), '8', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '9'), '9', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '.'), '.', '')
)

This sort of flow can work well inside a solution as a child flow. You should be able to copy the flow from the image above quite easily. But you can also download the example above here.

Hopefully a function will be added to Power Automate which allows you to extract numbers from a string. But for now, I hope this helps.

Filed Under: Power Platform Tagged With: Power Automate

Super Simple flow to get more than 5000 SharePoint Items

December 9, 2020 by Paulie 2 Comments

I’ve done a series of blog posts on how to get more than 5000 items from a SharePoint list using Power Automate. While helping someone else out I figured out a way to make it much easier.

If you’re not interested in knowing how it works, just download the flow and reconfigure it for your environment.

Overview

In this example the flow is triggered by PowerApps, but the same method could be used for any trigger. Let’s take a look at the complete flow and then I will explain each step in detail:

Super easy flow to get more than 5,000 items from a SharePoint list

Step by Step Explanation

Here is a explanation of every step of the flow in the order they appear in the screenshot above

  1. Trigger Action
    Is the flow trigger action. This could be PowerApps, the HTTP connector or anything that suits your situation.
  2. Compose Action ‘querySettings’
    This is a compose action which specifies the query settings for the flow.
    Image of a Compose Action which specifies the query settings for the SharePoint API Query
    It has two properties:
    • listName – Specifies the name of the SharePoint list that you want to retrieve items from.
    • fields – Is a comma separated list of fields that will be retrieved from the SharePoint list.
  3. Send an HTTP Request to SharePoint ‘getLowestID’
    This step gets the ID of the first record in the SharePoint List.
  4. Send an HTTP Request to SharePoint ‘getHighestID’
    Gets the ID of the last record in the SharePoint List.
  5. Compose Action ‘threads’
    This flow is going to execute multiple simultaneous queries to SharePoint. Each of which will return a maximum of 5,000 records. Specify the number of threads you will need to cover the number of records that you have in total.
    For example, if you have 9,000 records you will need 2 threads. If you have 23,000 you will need 5 threads. For example:
    [ 0, 1, 2, 3, 4 ]
  6. Compose Action ‘itemsPerThread’
    This action calculates the number of items to fetch in each thread. If you have 9,000 items it will get 4,500 items per thread. The expression is:
    add(div(sub(body('getHighestID')?['value'][0]['ID'],body('getLowestID')?['value'][0]['ID']), length(variables('threads'))),1 )
  7. Apply to each
    The apply to each action is where the SharePoint queries take place. It is important to configure the concurrency options on this step to ensure it is set to at least the number of threads you have configured:
    Image showing how to specify apply to each concurrency
    Within the apply to each the following actions take place:
    1. Compose action ‘lowID’
      Calculates the lowest ID to find in the current iteration:
      add(body('getLowestID')?['value'][0]['ID'], mul(outputs('itemsPerThread'), item()))
    2. Compose action ‘highID’
      Calculates the highest ID to find in the current iteration:
      add(body('getLowestID')?['value'][0]['ID'], mul(outputs('itemsPerThread'), add(item(), 1))
    3. Send an HTTP Request to SharePoint
      This is where the actual SharePoint query takes place. Using the inputs from the previous steps. The expression in the Uri field is:
      _api/Web/Lists/GetByTitle('@{outputs('querySettings')['listName']}')/[email protected]{outputs('querySettings')['fields']}&$filter=ID ge @{outputs('lowID')} and ID le @{outputs('highID')}&$top=5000
      Image of SharePoint HTTP Action being used to collect 5000 records from SharePoint
  8. Compose Action ‘Sample Output’
    This action isn’t required for the flow to run. It is used to output two records from the first iteration of the SharePoint API query. This can be used to generate the required JSON Schema in the final step.
    take(outputs('querySharepoint')[0]['body']['value'],2)
  9. Response Action
    This is used to send the data back to PowerApps. You need to modify the Response Body JSON Schema. If you run the flow once you can use the output of the “Sample Data” step and use the “Generate from Sample” function.

    The body content unions all of the data that is returned from SharePoint into a single array. This will need to be adjusted according to the number of threads that you have configured:
union(
outputs('querySharepoint')[0]['body']['value'],
outputs('querySharepoint')[1]['body']['value'],
outputs('querySharepoint')[2]['body']['value'],
outputs('querySharepoint')[3]['body']['value']
)

Conclusion

This is an incredibly easy way to get more than 5000 items from a SharePoint list. It is also very fast. I was able to get 17,000 rows in 5 seconds and someone I am working with was able to get 5,000 rows in two seconds!

Download the Flow

This flow is simple enough that you don’t really need to understand how it works, you can just import it into your environment and reconfigure to your needs.

Download the flow here and import it into your environment.

Filed Under: Power Platform Tagged With: Power Apps, Power Automate, SharePoint Online

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 6
  • Go to Next Page »

Primary Sidebar

Link to my LinkedIn Profile

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 107 other subscribers.

Go to mobile version