Arrays

Need to filter a deeply nested JSON object in Power Automate without burning through your API limits? In this post, I’ll show you how to repurpose and filter a JSON array in just two actions — no Apply to Each required — saving you thousands of API calls when working with large datasets. Here is an interesting sample JSON object {}, that contains a JSON array [] with 3 objects. The final solution will have 3000 objects. The aim is to retrieve two key values from the object whilst checking another nested array for a common string ‘ACABA’. Whilst it is perfectly acceptable to use an apply to each and loop through all 3000 objects, this will eat into your 24 hour API limit on the Power Platform and whilst this hasn’t really been a concern in the past, Microsoft are lowering those limits and will begin enforcement once a new admin level report has been released. ...

The ultimate free solution, parse any CSV file to a JSON Array via a Power Automate Flow. This flow will parse any CSV file, no matter the encoding. It will handle Unix, Windows, or Mac files. The key values (i.e. the header row) will be dynamically mapped and it will handle CSV files of all shapes and sizes. Whilst I have previously blogged about parsing an array with a select action, this solution did not handle varying header lengths automatically. ...

If you regularly receive emails with attachments that need filing into specific SharePoint folders or sites, this one’s for you. In this post, I’ll show you how to use Power Automate to automatically save email attachments to a custom folder or SharePoint site based on the subject line or file name. This use case was originally inspired by Xerox Docucentres, which enable end-users to email themselves a scanned document — but it applies equally to any routine email with attachments. This can be achieved relatively easily and would save you a lot of routine administration time. ...

Need to dynamically pull the choice values from a SharePoint column into your Power Automate flow? There’s a handy but little-known REST endpoint in the SharePoint API that makes this straightforward. In this post, I’ll show you how to call it and format the results into a clean array for use in your Cloud Flow. The SharePoint REST Endpoint Here is an example Choice Column in SharePoint: Using the endpoint http://myserver/_vti_bin/ListData.svc where myserver is the SharePoint site URL, you will retrieve an XML formatted list of all collections of information available on this site. Here you can browse a list of the various data collection names. ...

Need to combine data from multiple sources — like Excel tables and SharePoint lists — into a single array in Power Automate? In this post, I’ll show you how to create a one-to-one relationship between non-relational data sources using a single Apply to Each loop, filter arrays, and a compose trick to build a clean JSON array for the next stage of your flow. Data Sources Excel Sheet contains two tables of data that are related by the ID ...

Got a CSV file you need to turn into a JSON array in Power Automate — without premium connectors? In this post, I’ll share a copy-and-paste Select Action that does exactly that. Just paste it into your flow, adjust the columns, and you’re done — no Apply to Each loops required. I’ve recently covered how to efficiently parse a CSV here vs the age-old apply to each solution. Updated Solution Available Note, that I now have an ever more improved parsing solution. Handle the column keys dynamically. Any shape or sized CSV without the need to edit the existing flow. Retrieve data from OneDrive, SharePoint and Outlook Attachements. ...

Believe it or not, there’s no built-in SUM function in Power Automate. If you need to add up a list of numbers, you’re typically stuck using an Apply to Each loop with a variable — and for large lists, that’s painfully slow. In this post, I’ll show you a creative hack using the Select action that summed over 600 numbers in literally zero seconds. I also cover how to handle negative numbers and floats. ...

Looking to convert a CSV to JSON in Power Automate without using an Apply to Each? In this post, I’ll show you a super efficient method using the Select action that processes your entire CSV in zero seconds. If you’ve been looping through rows one by one, you’ll want to see this. Background Are you looking to parse any CSV to JSON without having to worry about the file encoding, the header names, or the length and shape of the CSV? I have a new one solution fits all method here (May 2021) which I encourage you to try. The method I propose below is off the back of my demo of the Union, Except, Intersect actions in Power Automate where I learned something new. How to use the Select Action. How about using this action to create an efficient method for converting CSV to JSON? ...

Need to compare two datasets in Power Automate — finding what’s common, what’s unique, or combining them all? In this post, I’ll show you how to efficiently perform Union, Intersect, and the elusive Except operation without using a single Apply to Each loop. The results are dramatically faster. Image from EssentialSQL.com Watch my demonstration or continue reading below ...