In this article, we’ll cover how to use rules in Power Automate to delete specific rows from a DocJuris Screening Report.
DocJuris’ auxiliary Flows are pre-defined templates of complex actions that you can import to your environment and use as a “black box,” meaning that the complex actions are hidden and centralized. This allows you to:
- Perform complex actions without being concerned about how they work.
- Centralise standard practices so that you can re-use them in all your Flows.
- Use them and take advantage of DocJuris’ expertise and speed up your Flow development
If you’re starting with Power Automate, you’ll want to check out our Overview of Power Automate guide.
One of the most common actions is to remove rows in Excel files based on key values in columns. For example, let’s say that you have a column “Status” in your Excel, and some rows contain the value “Missing.” Power Automate can review all rows and remove the ones you don’t want.
In this article, we’ll show you how to take advantage of this auxiliary Flow so that you can clean your Excel files efficiently.
Installation
Step 1: Install the DocJuris Auxiliary Flows as an imported solution
You can refer to Installing DocJuris' Auxiliary Flows for further information about initially installing auxiliary Flows. The article will explain what auxiliary Flows are and how to import them to your environment so you can use them efficiently.
Step 2: You must add the flow you're creating to the solution
First, create a flow and save it. Next, find DocJuris Auxiliary Flows under the Solutions tab. Click DocJuris Auxiliary Flows and then click "Add Existing" -> Automation -> Cloud flow. Pick the flow you're creating and add it to the solution.
Overview
When sending a Screening Report from DocJuris to an email recipient, you may want Power Automate to remove specific rows or values from the Report in the Excel file. The current Flow will help you remove a defined value from a column in Excel.
As mentioned before, we’ll be using it as a “black box”, meaning that we’ll use an action to call it and edit your Excel file by removing the information that you provided (in this case, the red Missing Non-Solicitation row). Here’s an example of an Excel File Screening Report.
When we run the auxiliary Flow, the file will look like this. Note that the updated file will display without the specified fields:
Please note that we’re changing the original file since you’re providing the path to it. No additional copies will be generated, so please backup the Excel file before running the auxiliary Flow in case you want to keep both versions.
Find the Flow
As mentioned before, you must import the auxiliary Flow to your environment before using it. You can do it by following the instructions on Installing DocJuris' Auxiliary Flows.
You must use the “Run a Child Flow” action to run an auxiliary Flow. You can find that action in Power Automate and select “Built-in” followed by “Flows”.
You can then find the “Run a Child Flow” action.
Here’s what it looks like:
After pressing the dropdown, please search for the auxiliary Flow by pushing the dropdown menu in the field.
The “Child Flow” field will contain the name you defined at the time of import. Your name might be different than the one in the example above.
The auxiliary Flow’s fields
Now that we’ve selected the Flow, let’s check each field we need to provide. All fields are required and are dependent on the Excel file that you’re going to import. We cannot detect the fields dynamically, so if you have Excel files with different structures, you must build other “Run a Child Flow” action calls with the corresponding fields from each structure.
Excel File Path
The “Excel to Parse” field will contain the content of the file to parse. Please insert the file’s path in this field and not the file’s contents since we’ll work on an existing file. Also, we’ll work on your OneDrive for Business, so please keep this in mind if you have the original file in SharePoint or Teams, for example. You need to put them in OneDrive for Business before you can use this auxiliary Flow.
Here’s an example of how to get the file’s path. We’ll use the “Get file content” action to get the path quickly, but if you want, you can use any action that provides you with a path. You can even build the path dynamically, get the path from a variable or get it from other actions as long it’s formatted correctly.
In our example below, we’ll only use it to get the correct path, so after, we’ll delete it.
Select the path in the UI and copy and paste it into the auxiliary Flow’s “Excel File Path”. This way,you know that the path to the file is always correct. Considering that your file can have complex names like the one demonstrated above, we recommend this approach so that nothing is missing in the path, including capitalization and special characters. You can delete the “Get file content” action (or any other action you decide to use to get the path) after you do it to avoid another action running in your Flow.
Name of the Column
Since our objective is to remove elements in a column, we need a column to search for the data. In this field, you’ll define that column name. You must copy the name exactly as you can find in the Excel file (including spaces and capitalization) to avoid issues while running the Flow. Here’s an example.
If we have the following file:
Then we can use the “Name of the Column” as “Status”:
Keyword to Search
Now that we have a column to search, we need a value to remove. The same rules apply for the “Name of the Column” where we’ll search for precisely the value you provide (including spaces and capitalization), so please be sure that you have the information uniform in Excel. In case of different capitalization, you need to run the auxiliary Flows multiple times or edit the original file to have the same capitalization.
If we want to remove “Missing” from the following file:
Then we can call the Flow as follows:
Excel Table to Search
Power Automate requires us to target tables when editing a file, so we need to ensure that the information is inside an Excel table. You can check if a file is inside a table by clicking anywhere in the table and see if you get a new tab in the top ribbon.
You can change the table’s name, but if you do, you need to change it in the auxiliary Flow’s call.
Here’s what it looks like for the table presented in the example above.
Excel Key Column
Finally, Power Automate requires a key column where each row is unique. It’s the only way we can use the action that deletes the row in Excel, so this record is required.
What if you don’t have one? You can create one yourself inside the table and add a name like this:
Now that you have a key column, you can insert it into the auxiliary Flow as follows:
Running the auxiliary Flow:
Let’s look at a sample file:
If we want to run the Flow to remove the “Missing” value from the “Status” column, then we can call the auxiliary Flow as follows:
Here’s what the Flow will return if it runs successfully.
Here’s the new Excel file:
As you can see, the original file is now changed so that it doesn’t include the rows with the “Missing” keyword.
Things to Consider:
Here are some further essential things to consider:
The file needs to be an Excel Workbook (xlsx)
Power Automate only supports operations in Excel files if they are Excel Workbooks (xlsx) files. Please ensure that you’re using one; otherwise, the Flow will fail with an error.
The key column should be numeric for better results
Although some string columns could be considered key columns because they always have unique values, we recommend having numeric values for better results. As mentioned before, you can generate one. The sequence can start at any number; the numbers don’t need to be in order as long as they are unique.
The key column has restrictions on the name
Power Automate has some restrictions on what names are supported for key columns. It’s not fully documented, so you can find some that don’t work, but we know that “Id” or “identifier” are not supported. The Flow has some safeguards and will warn you about invalid names, but if the Flow fails, try using another name as the key value. A safe name is “Key Column” for example.