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

Tachytelic.net

  • Get in Touch
  • About Me

Power Automate

Posts related to Microsoft Power Automate (aka Microsoft Flow).

Power Automate – How to sort an array

March 23, 2021 by Paulie Leave a Comment

There is no built in function in Power Automate to enable you to sort an array easily. But as with my previous post in which I demonstrated how to sum an array of numbers, this can be achieved very easily with an Office Script.

There is quite a bit of demand for the ability to sort an array in Power Automate, it has received a good number of votes on the ideas forum.

Here is the array I am going to work with, which is a list of Power Automate Legends, in no particular order:

[ "Paul Murana", "Damien Bird", "Tiago Mendonça", "Leon Tribe", "Ryan Maclean", "Fausto Capellan", "Jon Levesque", "Matt Beard", "Alison Mulligan", "Antti Pajunen", "Mark Stokes", "Azure McFarlane", "Sancho Harker", "Tomasz Poszytek", "Jon Russell" ]

This flow is going to make use of two Office Scripts:

  • One which sorts an array alphabetically.
  • Another which reverses the order of the array.

The Script Code

Below is the code which you can insert into your Office Scripts for each of the functions. Once you have added these functions to your Office Script repository, you’re good to build the very simple flow.

function main(
  workbook: ExcelScript.Workbook,
  sortArray: Array<string> = [],
) {

  sortArray.sort();
  return sortArray;
}
function main(
  workbook: ExcelScript.Workbook,
  reverseArray: Array<string> = [],
) {

  reverseArray.reverse();
  return reverseArray;
}

Sort Arrays with Power Automate

Here is a screenshot of the very simple flow which shows you how this all comes together:

Image of Power Automate Flow used to sort an Array

The first script action will sort the array in ascending order, and the second will reverse the order. Here are the outputs of the flow:

[
  "Alison Mulligan",
  "Antti Pajunen",
  "Azure McFarlane",
  "Damien Bird",
  "Fausto Capellan",
  "Jon Levesque",
  "Jon Russell",
  "Leon Tribe",
  "Mark Stokes",
  "Matt Beard",
  "Paul Murana",
  "Ryan Maclean",
  "Sancho Harker",
  "Tiago Mendonça",
  "Tomasz Poszytek"
]
[
  "Tomasz Poszytek",
  "Tiago Mendonça",
  "Sancho Harker",
  "Ryan Maclean",
  "Paul Murana",
  "Matt Beard",
  "Mark Stokes",
  "Leon Tribe",
  "Jon Russell",
  "Jon Levesque",
  "Fausto Capellan",
  "Damien Bird",
  "Azure McFarlane",
  "Antti Pajunen",
  "Alison Mulligan"
]

Script Files

You can download the scripts I created directly from here, simply stoe them in your in Onedrive \Documents\Office Scripts folder and they will appear in your scripts function in Excel connector of Power Automate.

array_sort.osts
array_reverse.osts

Performance

I tested these functions with an array of 20,000 items and it finished each action in around 5 seconds. So compared to creating a loop and sorting within Power Automate, this is blindingly quick.

Conclusion

I’m pretty excited about what is possible with Office scripts to extend the functionality of Power Automate .

Let me know how you get on, and if you think of any other useful ideas for Office Scripts we can use in Power Automate!

If you are new to Office Scripts (as I am) check out the overview from Micrsoft.

Filed Under: Power Platform Tagged With: Power Automate

How to Sum an Array of Numbers in Power Automate

March 23, 2021 by Paulie Leave a Comment

Although it sounds like a very simple thing to do, summing an array of numbers in Power Automate can be quite difficult if you have a large list of numbers to sum.

The most common method is to use an “apply each” action to loop round an array of numbers and increment the value of a variable. Here is a basic example of this:

Image of example flow used to sum an array of numbers.

This method is completely valid, and works just fine if you are only summing a small list of numbers. The problem is that there is very little scope for performance improvement. I tested the flow above with 1,000 numbers and it took 10 minutes to execute, which is unacceptably slow.

Finding a better way

Fellow Power Automate lover Damien Bird and I were trying to come up with a better solution for this and he created a neat method to sum an array which he posted on his blog, which solves the performance issue.

I have come up with another method, which is both fast and works for integers, floats and negative numbers.

Fast and Simple Solution to Sum an Array of Numbers in Power Automate

My solution to this actually turned out to be very simple. It works by harnessing the Power of Typescript in Excel online to perform the sum calculation on behalf of Power Automate.

In terms of performance I tested it with a list of 20,000 numbers ranging in size from -500,000 to positive 500,000 and it took 5 seconds to complete, here is how it is done:

Create an Excel Workbook to Store Functions

The first step is to head into Excel online and create a new Workbook called Power-Automate-Functions. Once you are in the workbook do the following:

  • Click on the Automate Tab
  • Click All Scripts
  • Click New Script

In the script window paste in the following code:

function main(
  workbook: ExcelScript.Workbook,
  numbersToSum: Array<number> =[],
  ) {

  let sum = numbersToSum.reduce((a, b) => a + b, 0);
  return sum
}

Rename it to SumArray, it should look like this:

Image of Excel online Office Scripts Editor creating a function to sum values.

The simple script accepts an array parameter called numbersToSum and has a single return value of sum.

Once you have saved the script, the Power Automate Flow is simple.

Execute the Excel function from the Power Automate Flow

Execution of this function is very easy, here is an example flow:

Image of a Power Automate Flow used to sum an array of Numbers

Simply choose the “Run Script” action from Excel online and provide your number array for the numbersToSum parameter. When you execute the flow you can easily see the return value in the output:

Image of the Output of a Flow

That is all there is to it! As I said above, I was able to process an array of 20,000 numbers in 5 seconds, so quite happy with the performance.

Conclusion

I’ve ignored the scripting functionality in Excel up until now. But I now realise that it gives Power Automate developer access to TypeScript which we can use to fill in lots of missing gaps in standard Power Automate functionality. I’m quite excited about it.

Being able to sum an array of values is just the first of many possiblities.

If you want to learn more about Office Scripts in Excel then check out this page from Microsoft.

Once again, thank you to Damien Bird for the inspiration for this idea.

Filed Under: Power Platform Tagged With: Power Automate

Update a Hyperlink Column in SharePoint with Power Automate

March 10, 2021 by Paulie Leave a Comment

This post explains how to update a Hyperlink Column in SharePoint Online using Power Automate. Unlike most field types, the hyperlink column has two components, the link address and the alternative text:

Image of new list item with Hyperlink being created in SharePoint Online

But when you try to create or update an item with a Hyperlink field in Power Automate, you are given only one field to populate:

Image of SharePoint Create Item action in Power Automate

The link text then becomes both the hyperlink and the descriptive text. If you have a long URL, this can ruin the look of your list and be confusing for users.

Create Hyperlink by using the Send an HTTP Request to SharePoint action

The solution is to use the “Send an HTTP Request to SharePoint” action to set both the URL and the Alternative text. This action would normally follow a Create Item action.

Select your Site Address and set the method to POST then set the URI to:

_api/web/lists/GetByTitle('Hyperlink Test')/items(ItemID)

Modify the above to contain your list name in place of “Hyperlink Test” and replace ItemID with the ID of your item, or dynamic content which references it.

Next set the Headers to the following:

{
  "Content-Type": "application/json;odata=verbose",
  "X-HTTP-Method": "MERGE",
  "IF-MATCH": "*"
}

You can copy and paste the JSON above if switch the headers to text mode:

Next is where the fun begins, an example HTTP Body looks like this:

{
	"__metadata": {
		"type": "SP.Data.Hyperlink_x0020_TestListItem"
	},
	"Link": {
		"Description": "Microsoft",
		"Url": "https://www.microsoft.com"
	}
}

But to make it easier for you, you can just type your list name, link description and Url into the form below and the correct code will be generated for you.

Copy the code generated above into the HTTP Body and then replace the static values with your dynamic content. The complete action looks like this:

Image showing the use of the HTTP Request to SharePoint action in Power Automate being used to populate a Hyperlink column.

Give it a try and see how you get on. It is quite straightforward. I hope that in the future the standard actions will handle creating and updating Hyperlink fields better and there will be no need to do this in the future.

Filed Under: Power Platform Tagged With: Power Automate

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

  • 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

Power Automate Support Image
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