Excel and Power Automate

How to use Power Automate for AML, KYC, and CTF screenings in Excel.

In this tutorial we show how our API Check product can be integrated with Microsoft Excel and Power Automate in order to perform AML, KYC and CTF screenings. We choose to use Power Automate in this tutorial as it enables end users to orchestrate automated process flows without coding experience.

info

The tutorial was written in April 2024 and with the then available technology and software versions.

First we create a master Excel sheet which stores the customer names and some additional information relevant for screening. This serves as a customer database and is not further modified after the initial set-up.

In our example use case we want to trigger the screening on a daily basis and store the results in a screening result Excel sheet. The master Excel sheet is used as a basis for regular compliance screenings and the following steps are executed each time in Power Automate:

• Create a screening result Excel sheet for the current execution
• Call the dilisense API Check for each customer name
• Update the Excel sheet with the screening results
• Store the detailed screening results in a file

And this is the high-level flow in Power Automate:

Prerequisites

• Microsoft 365 Apps for Business
• Microsoft Power Automate Premium
• dilisense API key (https://dilisense.com/en/register)

Excel setup for Power Automate integration

The foundation of our tutorial is an Excel file named Customers.xlsx, stored on OneDrive. This file is crucial for our workflow, and it's structured with several key columns:

• Id: Essential for accurately updating rows based on the API's response.
• Name: Required to perform the screening.
• Gender: Used to filter results and reduce false positives.
• Fuziness: Helps in fetching results for names that are similarly spelled, enhancing the accuracy of your search (see fuzzy_search parameter).
• Additional parameters can be configured (refer to request parameters); however, for simplicity, we will focus on these essential fields in this tutorial.

tip

The table needs to be formatted as table, as otherwise you can not refer to it from Power Automate.

1. Select your data range.
2. Navigate to the "Home" tab.
3. Click "Format as Table", and choose your preferred style.

After populating the Customers.xlsx with sample data, here’s how it might appear:

Any similarity to actual individuals, existing or past, is purely coincidental.

Setting up your Power Automate flow

Initiating a Power Automate flow is a straightforward process, crucial for automating tasks efficiently. Follow these steps to create a new flow optimized for customer screenings:

1. Start from blank: Navigate to "Create" and select "Instant cloud flow." This option allows you to start building your flow from scratch.
2. Naming your flow: Assign a descriptive name to your flow — "dilisense - Customer Screening Flow" — to easily identify its purpose.
3. Trigger selection: Choose "Manual trigger a flow" for initial setup and testing. This trigger allows you to start the flow manually from Power Automate. Then click on "Create" to finalize the creation of your new flow.

While "Manual trigger a flow" is excellent for testing, consider using "Schedule cloud flow" for operational environments. This setting ensures that customer screenings are performed regularly and automatically, enhancing compliance and efficiency.

With the setup complete, we're ready to dive into constructing the targeted flow within Power Automate, detailing each step to ensure a robust implementation. Stay tuned as we walk through the process step-by-step, ensuring you can replicate and customize it according to your organizational needs.

1. Create new screening result sheet

When conducting Anti-Money Laundering (AML), Know Your Customer (KYC), and Counter-Terrorism Financing (CTF) screenings, it's crucial to organize and store results systematically for compliance and auditing purposes. Follow these steps to create a new Excel sheet specifically designed to capture and store each session's results. We use the information provided in the Customers.xlsx as a basis.

Compose - Empty Excel file content

• Goal: Define content of an empty Excel file.
• Action "Compose" from "Data Operation"
• Input:

{
  "$content-type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  "$content": "UEsDBBQAAAAIAAAAIQB2pmxuTgEAAAgEAAATAAAAW0NvbnRlbnRfVHlwZXNdLnhtbKyTzU4CMRSF9ya+Q9OtmSm4MMYwsPBnqSzwAWp7h2noX3oLwtt7p4ALg4iBzTTT9p7v9Nx2NFk7y1aQ0ATf8GE94Ay8Ctr4ecPfZy/VPWeYpdfSBg8N3wDyyfj6ajTbREBG1R4b3uUcH4RA1YGTWIcInlbakJzM9JvmIkq1kHMQt4PBnVDBZ/C5yr0GH4+eoJVLm9nzmqa3ThJY5Oxxu7FnNVzGaI2SmZyKldc/KNWOUFNl2YOdiXhDNrg4SOhXfgfs6t4ommQ0sKlM+VU6siHWVnyGtPgIYVEfFzngMrStUaCDWjpKoMaYQGrsALKzdRlrJ43f+z7Ep+JpChEpxgT/d7DPqa+uIglByga+kzpKpBacfWTom6xBn8jepV2iQVGG4YVj77tZhI+lTj4y3W7Yfs+3UMT+AGLeWMBLX7IiuieL8o7HXwAAAP//AwBQSwMEFAAAAAgAAAAhALVVMCP0AAAATAIAAAsAAABfcmVscy8ucmVsc6ySTU/DMAyG70j8h8j31d2QEEJLd0FIuyFUfoBJ3A+1jaMkG92/JxwQVBqDA0d/vX78ytvdPI3qyCH24jSsixIUOyO2d62Gl/pxdQcqJnKWRnGs4cQRdtX11faZR0p5KHa9jyqruKihS8nfI0bT8USxEM8uVxoJE6UchhY9mYFaxk1Z3mL4rgHVQlPtrYawtzeg6pPPm3/XlqbpDT+IOUzs0pkVyHNiZ9mufMhsIfX5GlVTaDlpsGKecjoieV9kbMDzRJu/E/18LU6cyFIiNBL4Ms9HxyWg9X9atDTxy515xDcJw6vI8MmCix+o3gEAAP//AwBQSwMEFAAAAAgAAAAhAB+bXYvvAAAAigEAABAAAABkb2NQcm9wcy9hcHAueG1snJBNT8MwDIbvSPyHKPc1GUgTmtJM40tcJjiM3aPU7SJaO0rC1P57DBMFceRm+7Ufv7bZjEMvTpByIKzlstJSAHpqAna1fN0/Lm6kyMVh43pCqOUEWW7s5YV5SRQhlQBZMAJzLY+lxLVS2R9hcLliGVlpKQ2ucJo6RW0bPNyTfx8Ai7rSeqVgLIANNIs4A+WZuD6V/0Ib8p/+8mE/RTZszTbGPnhX+Eq7Cz5RpraIh9FDL56xDwhG/e4xO4eug2SNmqM7GqLDiUtz9MT4xNNvty4DC39yJh7Or7XLVaWvtf7a8l0z6ueJ9gMAAP//AwBQSwMEFAAAAAgAAAAhAALFlXmcAQAAJAMAABEAAABkb2NQcm9wcy9jb3JlLnhtbHxSTWvjMBC9F/ofjO6O7PSD1iQubEvpoYGFZtmyN3U0TbSxJSFN6vrfrywlTlLKgg6aeW+eRm9mdvfZNtkHOq+MnrNyUrAMNRip9GrOfi0f8xuWeRJaisZonLMePburz89mYCswDn86Y9GRQp8FJe0rsHO2JrIV5x7W2Ao/CQwdwHfjWkEhdCtuBWzECvm0KK55iySkIMEHwdyOimwnKWGUtFvXRAEJHBtsUZPn5aTkBy6ha/23BRE5YraKehv+tGv3WFtCAkf2p1cjseu6SXcR2wj9l/x18fwSv5orPXgFyOqZhIoUNVjP+OEabn779heBUnoMAgAOBRmXgDEINm+w74yTPiAnUaiR6MEpS2F4qe4kEdiN8LQI03xXKH/09UK5jcmeUOm3Layj3hfGMFeHH2rYh/TgIZIQHUydosyCJ1VycI/8vrh/WD6yehhrXtzm5fWyKKp4/gw+nNQPHqVEu+vwv4rTy7wI53Y5LavLoiqvjhT3AnVcS0G4Mq5P7cMYxY3VFFbmhQRtd36C+SZ1vNf1PwAAAP//AwBQSwMEFAAAAAgAAAAhAI2H2nDgAAAALQIAABoAAAB4bC9fcmVscy93b3JrYm9vay54bWwucmVsc6yRy2rDMBBF94X+g5h9PXYKpZTI2ZRCtsX9ACGPH8SWhGaS1n9f4YLdQEg22QiuBt1zJG13P+OgThS5905DkeWgyFlf967V8FV9PL2CYjGuNoN3pGEihl35+LD9pMFIOsRdH1ilFscaOpHwhsi2o9Fw5gO5NGl8HI2kGFsMxh5MS7jJ8xeM/zugPOtU+1pD3NfPoKopJPLtbt80vaV3b48jObmAQJZpSBdQlYktiYa/nCVHwMv4zT3xkp6FVvoccV6Law7FPR2+fTxwRySrx7LFOE8WGTz75PIXAAD//wMAUEsDBBQAAAAIAAAAIQAgtUK5mwIAAEIGAAANAAAAeGwvc3R5bGVzLnhtbKRUW2+bMBR+n7T/YPmdGmjIkgiomqZIlbZqUjtprw6YxJovyHYysmn/fcdAEqJO29S+4OPD8Xe+c01vWinQnhnLtcpwdBVixFSpK642Gf7yXAQzjKyjqqJCK5bhA7P4Jn//LrXuINjTljGHAELZDG+daxaE2HLLJLVXumEK/tTaSOrgajbENobRyvpHUpA4DKdEUq5wj7CQ5f+ASGq+7Zqg1LKhjq+54O7QYWEky8XDRmlD1wKottGElqiNpiZGrTk66bQv/EheGm117a4Al+i65iV7SXdO5oSWZyRAfh1SlJAwvoi9Na9EmhDD9tyXD+dprZWzqNQ75aCYgyJP7Q+0pwI0ESZ5WmqhDXJQJUhSp1FUst7itnHaokdqjP7ubWsquTj0/2Kv6Ko7GEsOufZK4v0Oh4VHXIgTi9izAEWeQrkcM6qACxrk50MDHBR0Vg/T2f3DemPoIYqT0QPSOczTtTYVdPI4/l6Vp4LVDogavtn60+kGvmvtHFQ7TytON1pR4UM5vhgECKdkQjz5bv9aX2C3NVI7WUj3UGUY5sYn4ShCIIPY4/UXjz9G67HfDIva+hIfEEe0L0if3CNf9Aw/+vEU0NIDBFrvuHBc/YEwYFbtOQWhr4Dzo9Yl5+QFMlGxmu6Eez79zPBZ/sQqvpPxyeoz32vXQWT4LPdWc++Dte6jhfaCE+0Mz/DP++WH+eq+iINZuJwFk2uWBPNkuQqSyd1ytSrmYRze/RoN/BvGvdtPeQpbY2EFLAUzBDuE+HTWZXh0+egbrZstArTH3OfxNLxNojAorsMomEzpLJhNr5OgSKJ4NZ0s75MiGXFPXrkWQhJF/YLx5JOF45IJro61OlZorIUiwfUvQfhQukqQ8/LPfwMAAP//AwBQSwMEFAAAAAgAAAAhAEHfy0zIBwAAESIAABMAAAB4bC90aGVtZS90aGVtZTEueG1s7FrNjxu3Fb8X6P8wmLus0Wj0tbAc6NMbe3e98MoucqQkSkMvZzggqd0VAgOFc+olQIC06KVAbz0EQQIkQINe+scYsNGmf0QfyZFmuKJirz+AtNgVsJjh/N7jj+89Pr4h5+4nVwn1LjAXhKVdv3Yn8D2czticpMuu/2QyrrR9T0iUzhFlKe76ayz8T+799jd30YGMcYI9kE/FAer6sZTZQbUqZtCMxB2W4RSeLRhPkIRbvqzOOboEvQmthkHQrCaIpL6XogTUPlosyAx7E6XSv7dRPqJwm0qhGmaUnynV2JLQ2Pl5TSHEWgwo9y4Q7frQz5xdTvCV9D2KhIQHXT/Qf3713t0qOsiFqNwjW5Ib679cLheYn4e6T76cbjsNRmE7qm31awCVu7hRW/22+jQAzWYwUsOlrLMWNcbtMMeWQObSpbtVr284lEDmsr7DudZuDsLI0q9BBh/tjnHcGQ0bFl6DDL6xg+8FYb9Tt/AaZPDNHXw06rXCkYXXoJiS9HwX3Wy1280cvYUsGD10wjvNZtAa5vACBdGwjS7VxYKlcl+sJegZ42MAKCBFkqSeXGd4gWYQxb1MMuENicgoWvtehlImoDkIg3FQh//qF+krbXF0gFFJWvECJmKnSfHxxIyTTHb9B6DVL0Fe/fTTyxc/vnzx95dffPHyxXfeEVnG0qiy5A5RuizL/fy3r/7zl997//7hrz9//Uc3XpTxr7/9w+t//POX1MNUK0zx6k/fv/7x+1d//vJf33zt0N7jaFqGT0iChXeCL73HLIEBalPY/PGU30xiEiNiSaAYdDtUj2RsAU/WiLpwfWyb8CmHLOMC3l89s7iexXwliaPnh3FiAY8Zo33GnQZ4qPoqWXiySpfuzvmqjHuM0IWr7wFKLQePVhmkV+JSOYixRfOUolSiJU6x9NQzdo6xY3SfEWLZ9ZjMOBNsIb3PiNdHxGmSCZlagVQIHZIE/LJ2EQRXW7Y5fur1GXWNeogvbCRMC0Qd5CeYWma8j1YSJS6VE5TQssGPkIxdJM/WfFbGjYQETy8xZd5ojoVwyTziMN6S0x8iSGxOtx/TdWIjuSTnLp1HiLEycsjOBzFKMidnksZl7KfiHEIUeadMuuDHzJ4h6h78gNK97n5KsOXuNyeCJ5DgypSKAFFPVtzhy/uY2fNxTRcIu7JMjydWdu1x4oyO/mpphfYRxhRdojnG3pNPHQz6LLNsXpB+EENWOcSuwHqA7FhV9ykWUCapumY3RR4RYYXsGV6yPXyO19cSzxqlCeL7NJ+A163QnXKYjA4Kj+jsvAw8IVD+Qbw4jfJIgI5ScI/2aT2NkbV2qXvhjtc1t/z3NnMM5uWzm85LkME3loHE/ta2mSBqdVAEzAQR78iVbkHEcn8hotZVLbZyyi3sSVu4AQojq95JSPqm4ucEcc4uy7VPrQZld5RXP7oCMnHz4Wufj1b1uBW/T72zL68cXqty9uH+B2ubIVqlpxiWk93EdVva3JY2/v99abNvLt8WNLcFzW1B43oF+ygFTVHDQHlTbPXojZ9k777PglB6JtcUHwm99SPgtWY+hka9J6U3Jrf7gFkMl2o80IGFW3KkZTzO5O+IjM9ilMH+UE3vYi5FrnopvIwJ2DbSzXo/FV/TrTefVskxm5vtTl1jBcaEAsmiPWhA8WXaYatKGnSzlTcqfhvqmu1Sb7VuCCjZm5AodWaTqDtItDaNbyChqscPw6LjYNFW6jeu2jEFUNt6Bd67PXhb7/qNyDCCHTmo0efKT8bVG+8q53xQT+8zJi1HABTYu57uKK57h6dGZ0LtLTxtkdBOMWFlk9C+0gWeiOFtOI/O8r77LwXcTX3dKVxq0VOm2MyGgkar/TF8rZLItdxA03KmoKl3CXM8hEnnezOUdf0F7BvDZZJB8Aj17oXoEg5fZpKbGf8uqSXjQg6RiI3FddYx/kmIxNyjJOn6avzbcKCpTiKGXAem7q+VXKgm3K+NHHjd9jJeLPBMlv1ealGWNreQ4k2ycD7V4u8OVpJsBe4+i+eX3pSu+GMEIdZo1ZR350TA8UHNuHpO4Dxsm8mK+Lu2MuXZ3zrkKvIxolmM8iWlnM0NXC8oWzr6bmuD0l0+ZjDorgmnS7XCvvey++a1WlmuWB87xaJppRW1bLqz6cdb5UusilXUYmVy9/Wc29kkOwhU5zLx/mt/iVrRmUVNMd7Nwypp5602tQ9YEZRWn+Yeu20XCacl3nXpB7nrUatWiE1hqQNfH5yXz7bZ9BkkjyGcIq6oOe2mKdzp0jI75dq3UzZf55dUmERjfK6KUpPKH+OFR+ZXXT90VY754XFeDdAU0LrmhRm2FXRWe7agWuxyUTNht8KmjL1Wr9rCW4nNMetWWG8tumjLq81ptqrVdc/KYWbUOg1rS8HVrhVhm5wjKJ3NYa7JvZBnrmReacOVt+Kk638eNHrRIGwMKkG7MapE9SiotBu9eqXXaNRro0YtGPbD50BPxkmtYb58GMNpEF3n3z/o9p1vIJLNgdedGUuqTH/jUNXe199A1ML930CAI4FWMxx36p1+s9Kp98aVaNhvVzqDZr8ybA5aw/Fw0Gh3xs9970KDo159EDVH7UqzNhhUomag6Lc7lVYUhr2o1WuPot7z3P0wcpODc1uA+TSve/8FAAD//wMAUEsDBBQAAAAIAAAAIQBj0p8MdgIAAJwFAAAPAAAAeGwvd29ya2Jvb2sueG1srFRRb5swEH6ftP9g+Z1iUyAJCqlKIFqkbqrWrn2cXHCCFcDIdhqiqf99Z1KSdXmpuiHwcRx8/r6746ZXXV2hZ660kE2M6QXBiDe5LESzjvGP+4Uzxkgb1hSskg2P8Z5rfDX7/Gm6k2rzJOUGAUCjY1wa00auq/OS10xfyJY3EFlJVTMDrlq7ulWcFbrk3NSV6xESujUTDT4gROo9GHK1EjlPZb6teWMOIIpXzAB9XYpWD2h1/h64mqnNtnVyWbcA8SQqYfY9KEZ1Hi3XjVTsqQLZHQ1Qp+AM4aIEFm/YCUJnW9UiV1LLlbkAaPdA+kw/JS6lb1LQnefgfUi+q/izsDU8slLhB1mFR6zwBEbJP6NRaK2+VyJI3gfRgiM3D8+mK1Hxh0PrIta231htK1VhVDFtskIYXsR4BK7c8dMDHyO1bZOtqCDqjUISYHd2bOdbBU6noiGft0YhuF+mN4B5x55hB9BRvDbgEiAo/UkSmgZhkk3m6XyejS/DhHjzbHQdLBKS+teTJFmkdB7allFhlEu2NeUrcQsbYx9YnoW+sm6IUBJtRXGi8Iu8Ho61fy1D7MXqsr/og+A7fZJoXdQ9iqaQO8iAD3r2g0chG2jXhx5FYUqQ549Pz75wsS6B75j01VSepRXjN3TSA50FHI5d3tBx/+DTTwLg1VvU9NW7s9OBwsixts8uVCuye6hlQa0gd/gsZ1V+q5A1/Yue59H+Dd6ZG21mU7BoqwTQoz65HpGJ75DsMnD88cRzxv6l58z91MuCUZZmSfDyP/9naCEaRMOItCxLpsy9YvkGBut3vkqYhk46CAKeIGtg7Q5fzX4DAAD//wMAUEsDBBQAAAAIAAAAIQDp4eOw0AEAAI0DAAAYAAAAeGwvd29ya3NoZWV0cy9zaGVldDEueG1snJJLa8MwDIDvg/0H43vrOG3HGpKWwSjrbex1dxwlMfUj2E4fjP33OenaDnIpNX7K1ifJUrrcK4m2YJ0wOsN0HGEEmptC6CrDnx+r0SNGzjNdMGk0ZPgADi8X93fpztiNqwE8CgTtMlx73ySEOF6DYm5sGtDhpjRWMR+OtiKuscCKXklJEkfRA1FMaHwkJPYahilLweHZ8FaB9keIBcl88N/VonEnmuLX4BSzm7YZcaOagMiFFP7QQzFSPFlX2liWyxD3nk4ZR3sbehzG5GSmlw8sKcGtcab040AmR5+H4c/JnDB+Jg3jvwpDp8TCVnQJvKDi21yiszMrvsAmN8IezrDuu2zSiiLD39FfG4WVdlN0mU53P3iRFiJkuIsKWSgz/EQxWaR98XwJ2Ll/e+RZ/g4SuIdggGLU1WZuzKZ7uA6iqFMlA91VX5uvFhVQslb6N7N7AVHVPkBmQeUXAAD//wAAAP//sinOSE0tcUksSdS3AwAAAP//AAAA//+yKUhMT/VNLErPzCtWyElNK7FVMtAzV1IoykzPgLFL8gvAoqZKCkn5JSX5uTBeRmpiSmoRiGespJCWn18C4+jb2eiX5xdlF2ekppbYAQAAAP//AwBQSwECCgAUAAAACAAAACEAdqZsbk4BAAAIBAAAEwAAAAAAAAAAAAAAAAAAAAAAW0NvbnRlbnRfVHlwZXNdLnhtbFBLAQIKABQAAAAIAAAAIQC1VTAj9AAAAEwCAAALAAAAAAAAAAAAAAAAAH8BAABfcmVscy8ucmVsc1BLAQIKABQAAAAIAAAAIQAfm12L7wAAAIoBAAAQAAAAAAAAAAAAAAAAAJwCAABkb2NQcm9wcy9hcHAueG1sUEsBAgoAFAAAAAgAAAAhAALFlXmcAQAAJAMAABEAAAAAAAAAAAAAAAAAuQMAAGRvY1Byb3BzL2NvcmUueG1sUEsBAgoAFAAAAAgAAAAhAI2H2nDgAAAALQIAABoAAAAAAAAAAAAAAAAAhAUAAHhsL19yZWxzL3dvcmtib29rLnhtbC5yZWxzUEsBAgoAFAAAAAgAAAAhACC1QrmbAgAAQgYAAA0AAAAAAAAAAAAAAAAAnAYAAHhsL3N0eWxlcy54bWxQSwECCgAUAAAACAAAACEAQd/LTMgHAAARIgAAEwAAAAAAAAAAAAAAAABiCQAAeGwvdGhlbWUvdGhlbWUxLnhtbFBLAQIKABQAAAAIAAAAIQBj0p8MdgIAAJwFAAAPAAAAAAAAAAAAAAAAAFsRAAB4bC93b3JrYm9vay54bWxQSwECCgAUAAAACAAAACEA6eHjsNABAACNAwAAGAAAAAAAAAAAAAAAAAD+EwAAeGwvd29ya3NoZWV0cy9zaGVldDEueG1sUEsFBgAAAAAJAAkAPgIAAAQWAAAAAA=="
}

note

It is not possible to add this content directly into the "Create file" action. Therefore the preceding "Compose" action is required.

Create file - Empty Excel file

• Goal: Create file in which the customer names and screening results are later populated.
• Action: "Create file" from "OneDrive for Business".
• Folder Path: /dilisense/Screenings/@{formatDateTime(utcNow(),'yyyy-MM-dd')}
• File Name: Customer Screening Result.xlsx
• File Content: @{outputs('Compose_-_Empty_Excel_file_content')}

Create table - Screening result table

• Goal: We want to add a blank table to the just created Excel sheet.
• Action: "Create table" from "Excel Online (Business)"
• Location: OneDrive
• Document Library: OneDrive
• File: @{outputs('Create_file_-_Empty_Excel_file')?['body/Path']}
• Table range: A1
• Table name: screening_result_table
• Columns Names: Id,Name,Gender,Fuzziness,Hits,Timestamp

The Id, Name, Gender and Fuzziness are filled from the Customers Excel file. The Hits and Timestamp will be filled dynamically from the dilisense API Check.

List rows - From customer table

• Goal: Before we can add the information from Customers.xlsx to the create table, we need to iterate through it.
• Action: "List rows present in a table" from "Excel Online (Business)"
• Location: OneDrive for Business
• Document Library: OneDrive
• File: /<your_parent_directory>/Customers.xlsx
• Table: Table1

Apply to each - Add rows to screening result table

• Goal: Iterate through each row.
• Action: "Apply to each" from "Control"
• Output from previous step: @{outputs('List_rows_-_From_Customers_Table')?['body/value']}

Add rows - To screening result table

• Goal: Add each row from Customers.xlsx to the newly created table.
• Action: "Add a row into a table" from "Excel Online (Business)"
• Location: OneDrive for Business
• Document Library: OneDrive
• File: @{outputs('Create_file_-_Empty_Excel_file')?['body/Path']}
• Table: screening_result_table
• Row: @{items('Apply_to_each_-_Add_rows_to_screening_result_table')}

2. Conduct screening of names

Properly managing and storing data is crucial in any automated process, especially when handling sensitive information like customer screenings. Initializing variables at the start of your flow can enhance data management and provide flexibility for later uses. Follow these steps to set up a variable in Power Automate:

• In your Power Automate flow, select "Add an action".
• Choose "Initialize variable" from "Variable".
• Name: Enter screening_result as the variable name to clearly identify its purpose.
• Type: Select Array from the type options. This type is chosen to accommodate potentially multiple entries from each screening, providing a structured way to store and access the results.
• Value: Leave the value field empty, as this variable will be populated dynamically with screening results during the flow execution.

The flow will look as shown below:

List rows - From screening result table

• Goal: Before we can screen for each name in Customers.xlsx, we need to iterate through it.
• Action: "List rows present in a table" from "Excel Online (Business)"
• Location: OneDrive for Business
• Document Library: OneDrive
• File: @{outputs('Create_file_-_Empty_Excel_file')?['body/Path']}
• Table: screening_result_table

Apply to each - Add rows to screening result table

• Goal: Iterate through each row.
• Action: "Apply to each" from "Control"
• Output from previous step: @{outputs('List_rows_-_From_screening_result_table')?['body/value']}

Parse JSON - From screening result table row

• Goal: Enable an easy use of the data from the Excel.
• Action: "Parse JSON" from "Data Operation"
• Content: @{items('Apply_to_each_-_Call_screening_API')}
• Schema:

{
    "type": "object",
    "properties": {
        "@@odata.etag": {
            "type": "string"
        },
        "ItemInternalId": {
            "type": "string"
        },
        "Id": {
            "type": "string"
        },
        "Customer name": {
            "type": "string"
        },
        "Gender": {
            "type": "string"
        },
        "Fuzziness": {
            "type": "string"
        },
        "Hits": {
            "type": "string"
        },
        "Timestamp": {
            "type": "string"
        }
    }
}

Compose - RequestURL

• Goal: Assemble the request URL needed to call the dilisense API for the API Check product.
• Action "Compose" from "Data Operation"
• Inputs:

 @{concat(
    'https://api.dilisense.com/v1/checkIndividual?',
    'names=', uriComponent(body('Parse_JSON_-_From_screening_result_table_row')?['Name']), 
    if(not(empty(body('Parse_JSON_-_From_screening_result_table_row')?['Gender'])), 
       concat('&gender=', uriComponent(body('Parse_JSON_-_From_screening_result_table_row')?['Gender'])), 
       ''),
    if(not(empty(body('Parse_JSON_-_From_screening_result_table_row')?['Fuzziness'])), 
       concat('&fuzzy_search=', uriComponent(body('Parse_JSON_-_From_screening_result_table_row')?['Fuzziness'])), 
       '')
)
}

note

You need to add this as Expression as otherwise it will be considered a string.

HTTP - Call API Check

• URL: @{outputs('Compose_-_RequestUrl')}
• Method: GET
• Headers: x-api-key = <your_api_key>
• Queries: Leave this empty
• Body: Leave this empty
• Cookie: Leave this empty

Switch - Response from screening API

• Goal: Handle API responses according to their status code.
• Action: "Switch" from "Control"
• On: @{outputs('HTTP_-_Call_API_Check')?['statusCode']}

Case - 200 Success

• Goal: Handle API responses with status code 200.
• Action: Case
• Equals: 200

Append to array variable - Success

• Goal: Store each successful response in the screening_result variable for later processing.
• Action: "Append to array variable" from "Variable"
• Name: screening_result
• Value:

@{json(concat(
    '{',
        '"id": "', replace(string(body('Parse_JSON_-_From_screening_result_table_row')?['Id']), '"', '\"'), '",',
        '"data": ', string(body('HTTP_-_Call_API_Check')),
    '}'
)
)}

Case - 400 Failure and default

Logging errors, especially those related to API requests, helps in troubleshooting and improving the reliability of your automated processes. By keeping an accurate record of failures, you can:

• Analyze trends or recurring issues.
• Adjust your API usage strategy or address data-related issues.
• Maintain compliance with data processing standards, particularly useful in scenarios involving sensitive data like customer screenings.

We won't go into further details of logging, but recommend that you consider at least the 400 and the default cases.

3. Update Excel sheet

After conducting customer screenings using Power Automate, it's essential to update your results sheet with the number of matches found for each customer name. This data is crucial for analyzing the effectiveness of your screening process and for maintaining accurate records.

Parse JSON - screeningResult for Excel sheet

• Goal: Parse the variable screening_result to make it accessible for the Excel update.
• Action: "Parse JSON" from "Data Operation"
• Content: @{variables('screening_result')}
• Schema:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "id": {
                "type": "string"
            },
            "data": {
                "type": "object",
                "properties": {
                    "timestamp": {
                        "type": "string"
                    },
                    "total_hits": {
                        "type": "integer"
                    },
                    "found_records": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "gender": {
                                    "type": "string"
                                },
                                "date_of_birth": {
                                    "type": "array",
                                    "items": {
                                        "type": "string"
                                    }
                                },
                                "citizenship": {
                                    "type": "array",
                                    "items": {
                                        "type": "string"
                                    }
                                },
                                "description": {
                                    "type": "array",
                                    "items": {
                                        "type": "string"
                                    }
                                },
                                "source_type": {
                                    "type": "string"
                                },
                                "occupations": {
                                    "type": "array",
                                    "items": {
                                        "type": "string"
                                    }
                                },
                                "given_names": {
                                    "type": "array",
                                    "items": {
                                        "type": "string"
                                    }
                                },
                                "list_date": {
                                    "type": "string"
                                },
                                "entity_type": {
                                    "type": "string"
                                },
                                "pep_type": {
                                    "type": "string"
                                },
                                "name": {
                                    "type": "string"
                                },
                                "source_id": {
                                    "type": "string"
                                },
                                "address": {
                                    "type": "array",
                                    "items": {
                                        "type": "string"
                                    }
                                },
                                "functions": {
                                    "type": "array",
                                    "items": {
                                        "type": "string"
                                    }
                                },
                                "links": {
                                    "type": "array",
                                    "items": {
                                        "type": "string"
                                    }
                                },
                                "positions": {
                                    "type": "array",
                                    "items": {
                                        "type": "string"
                                    }
                                }
                            },
                            "required": [
                                "source_type",
                                "entity_type",
                                "name"
                            ]
                        }
                    }
                }
            }
        },
        "required": [
            "id",
            "data"
        ]
    }
}

For each - Customer name

• Goal: Iterate through each customer name.
• Action: "Apply to each" from "Control"
• Inputs: @{outputs('Parse_JSON_-_screeningResult_for_Excel_update')['body']}

Update a row - In screening result Excel sheet

• Goal: Update the customer record with the number of matches and a timestamp
• Action: "Update a row" from "Excel Online (Business)"
• Location: OneDrive for Business
• Document Library: OneDrive
• File: @{outputs('Create_file_-_Empty_Excel_file')?['body/Path']}
• Table: screening_result_table
• Key Column: Id
• Key Value: @{items('For_each_-_Customer_name')['id']}
• Provide The Item Properties:

{
  "Hits": @{items('For_each_-_Customer_name')?['data']?['total_hits']},
  "Timestamp": @{items('For_each_-_Customer_name')?['data']?['timestamp']}
}

4. Store results in file

In addition to recording basic screening outcomes in your Customers.xlsx — such as whether there was a hit — it's essential to store the detailed responses from your screening API call. This practice is critical for subsequent reviews and analyses, enabling your team to accurately identify true positives, false positives, and to support the development of a whitelist.

Parse JSON - screeningResult for file storage

• Goal: Parse the variable screening_result to make it accessible for the Excel update.
• Action: "Parse JSON" from "Data Operation"
• Content: @{variables('screening_result')}
• Schema: See Parse JSON action above

Compose - File result content

• Goal: Define content that will be stored in a file.
• Action "Compose" from "Data Operation"
• Inputs: @{string(variables('screening_result'))}

Create file - Results

• Folder Path: /dilisense/Screenings/@{formatDateTime(utcNow(),'yyyy-MM-dd')}
• File Name: Output.txt
• File Content: @{outputs('Compose_-_File_result_content')}

Resources

• Download Customer.xlsx

FAQs

Is a Power Automate connector available?

There is no connector available yet. If you face issues with using our API through Power Automate get in touch with us at support@dilisense.com

Is free screening volume available for the integration?

Yes, we provide free screening volume for the integration. Contact us at sales@dilisense.com