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.
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.
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.
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.
Set Variable Values
- Set value of inputString as input from the desired row data
- Substring value of inputString at delimiters, using Split expression in the Compose action, as in the below image.
- Once the string is sub stringed, set inputArray from output of the last step; hence each substring forms an element of the inputArray.
- 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)
- 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
- 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.
- 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
- Final step of the ‘Apply to each’ block is to update Column2 for each row of the excel table with outputString
You may also like : Microsoft Flow to List and Update Online Excel Table Rows
Final Solution
The final solution looks like the one below.
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.
