306 Views
7 minutes read
Categories
Microsoft 365 Power Automate

Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data

Preface

Recently I was tasked to extract some pattern values from an excel column data. Being limited in my ability to perform any language programming, and with the limited amount of time I had, there was no other choice left than to use default excel column filter and then manually segregate the required data pattern from the rest of the cell data. Presuming this is not a one-time ask and a similar situation may arise in future, a handy generic solution works best in interest of time.

Power users like me, who work on excel data, but are not much adept in use of excel macros or other methods to automate a data substring pattern solution can still feel empowered as long as they can work their way around with Power Automate.

Case Study

My data looked like the one in the below image.

Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data

A no-brainer guess after the first glance at data can identify Column1 as emails.

The task was to segregate email IDs of a certain domain ‘def.com’, for each row data of Column1. It could be thought of suggestively analogous to tell apart and extract certain outside organization domain email IDs from within organization domain email IDs.

In short, the output was desired in the below format.

Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data

Complexity and Cue

The possibility of multiple strings match in each row data, like in row #6 of my data, defeats the use of simple default column filters, while suggesting to still experiment with a complex formula-based solution.

The visual cue from a close analysis suggests that each row data of Column1 is orderly delimited by a disciplined formatting of separator (in this case a “,”). This arises a hope of being able to handle the delimited substrings of each cell data once each substring is identified individually.

Solution Framework

Apart from listing rows from excel and ending with updating corresponding column values into the excel, use of an array can be helpful to contain individual substrings of each cell data. Hence, the solution framework can be formulated as below:

⦁ Read tabular data from Excel
⦁ Transform each row data as individual input array
⦁ Read each input array and perform the following

  • For each input array execute the following for the entire array length
    • Perform desired pattern match on each element of the input array
    • Push each matched element into a corresponding output array for the input array (if multiple pattern matches are found in the array, each matched element gets appended)
    • Update each Excel row with the corresponding output array
  • Go to next input array

Caveat: It is advisable not to set expectations like response time, or speed as with any sophisticated programming process.

Solution Construction

List tabular data from Excel

My earlier blog already details about listing and updating tabular data from online Excel; hence I will skip details in this blog. Giving just a glimpse of the listing tabular data action, it looks like the one in the below image. The important thing to note here is the Table definition; hence, first time readers can reference my last blog for the same.

Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data

Initialize Variables

Working and reworking on errors made me finalize a set of variables which are initialized as sequential steps at the start of the process. To highlight their utility, they are described as below:

inputString – String type; Used as a placeholder to contain each row data from excel table since input tabular data is not directly read as an array
inputArray – Array type; Contains transformed inputString data as array
outputArray – Array type; Contains filtered array data to be updated to Excel table
outputString – String type; Used to transform outputArray data to string to update each row data in the excel table
emptyArray – Array type; Used to reinitialize outputArray before each excel table row update, since there is no method yet to reset variable values to empty or blank

Read and Update Excel Data

The entire excel data (‘value’) is pointed as input to the ‘Apply to each’ block, as in the following image. Hence, each step encapsulated in the block is expected to act on individual row data of the excel.

Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data

Set Variable Values

  • Set value of inputString as input from the desired row data
Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data
  • Substring value of inputString at delimiters, using Split expression in the Compose action, as in the below image.
Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data
  • Once the string is sub stringed, set inputArray from output of the last step; hence each substring forms an element of the inputArray.
Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data
  • Reset outputArray to remove any residual or old data (this might not make sense for the first run, but for subsequent runs of the loop, this variable needs to be reinitialized)
Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data
  • Perform condition matching (per the requirement) for each element of the inputArray using the item() expression adding another ‘Apply to each’ block, which loops through each array element for the entire array length
Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data
  • Since the visual cue of tabular data hinted at possibilities of multiple matching elements (or items) for each row data, for success of every pattern match, use Append to array variable action to keep appending each matched element (item()) from inputArray to its corresponding outputArray.
Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data
  • Once all elements of the array are inspected for the condition or desired pattern match for filtration, transform to write into outputString since an array data cannot directly update excel cell
Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data
  • Final step of the ‘Apply to each’ block is to update Column2 for each row of the excel table with outputString
Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data

You may also like : Microsoft Flow to List and Update Online Excel Table Rows

Final Solution

The final solution looks like the one below.

Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data

Learnings and Takeaway

My biggest learning here was dealing with arrays; be it the discovery of not being able to read or write arrays directly as excel tabular data, or be it the transformation to and from string type to array and reading each array element for condition match.

Also, luckily for me, the input data had a pattern set by delimiters. However, this may not always be a cakewalk.

Hence, to be able to use this solution, identification of some delimiter pattern to match and split the input data, wins half the battle by allowing data to be broken up into singular elements.

Though this is a specific case study, understanding different solution frameworks helps remodel the task and tweak solutions for specific requirements to work your way around the resolution.

Supriya Tenany

About Supriya Tenany

Supriya's profile as Principal Engineer primarily involves interacting with Netwoven customers and the project delivery team.

In the last 14 years of her experience in the IT industry, of which the past 9 years have been with Netwoven, her roles ranged from QA to Business Analyst.

Supriya holds a Masters of Technology in Remote Sensing from Birla Institute of Technology, Mesra, Ranchi.

LinkedinTwitterFacebook

Leave a Reply

Your email address will not be published. Required fields are marked *