Deleting Rows From a Screening Report Using Rules

Article author
Jenny Zaret
  • Updated

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:

  1. Perform complex actions without being concerned about how they work.
  2. Centralise standard practices so that you can re-use them in all your Flows.
  3. 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.

 

Prerequisites

DocJuris for Power Automate requires a paid subscription to DocJuris. Please get in touch at www.docjuris.com or email DocJuris Support.

How to Get Credentials

⚠️ Your DocJuris login is not your username/password for the Power Automate connector. To grab your connector username/password, please see below.

  1. Head over to your DocJuris Organization Settings in your DocJuris account.
  2. Scroll down and copy your Organization ID. Paste this into Power Automate as theOrganization ID. You'll see this key underHere is the Organization ID that you should use when accessing the DocJuris API:
  3. Under API Keys, create a new key (name it something useful) and copy the API key. Paste this as theapiKeyfield in Power Automate.

 

Installation

You can refer to Installing DocJuris' Auxiliary Flows if you have any questions on how to install auxiliary Flows. This article will explain what auxiliary Flows are and how to import them to your environment so that you can use them.

 

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.

 

screeningreport1_clean_pg3_2.png

 

When we run the auxiliary Flow, the file will look like this. Note that the updated file will display without the specified fields:

 

screeningreport2_clean_pg3_2.png

 

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”.

 

findRunaChildFlow_pg5.png

 

 

You can then find the “Run a Child Flow” action.

 

runachildflowactionarrow_pg6.png

 

Here’s what it looks like:

 

runachildflowactiondropdown_pg6.png

 

 

After pressing the dropdown, please search for the auxiliary Flow by pushing the dropdown menu in the field.

 

runachildflow_pickfromavailableFlows_pg7.png

 

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.

 

2022-08-31_14.29.19.gif

 

 

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:

 

screeningreport1_clean_pg3_2.png

 

Then we can use the “Name of the Column” as “Status”:

 

nameofcolumn_status_pg10.png

 

 

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:

 

screeningreport1_clean_pg3_2.png

Then we can call the Flow as follows:

 

keywordtosearchmissing_pg11.png

 

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.

 

Excelscreeningreport3_arrows_pg11.png

 

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.

 

ExcelTabletoSearch_Table1_pg12.png

 

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:

 

2022-10-13_10.50.55.gif

 

Now that you have a key column, you can insert it into the auxiliary Flow as follows:

 

ExcelKeyColumn_keycolumn_pg13.png

 

Running the auxiliary Flow:

Let’s look at a sample file:

 

screeningreport1_clean_pg3_2.png

 

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:

 

runachildflow_removeexcelrowsbycolumn_keycolumn_pg14.png

 

Here’s what the Flow will return if it runs successfully.

 

runachildflow_inputs_statuscode200_pg15.png

 

 

Here’s the new Excel file:

 

screeningreport_Missingcolumnremoved_clean_pg16.png

 

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.

 

 

 

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.