Posted by When it comes to automating business processes, there are scenarios where data from external systems is used as input for the business process/automation and data quality can be(come) an issue. Since the maintenance of/responsibility for the external data is out of our hands, we have to anticipate on data quality issues in our Power Automate flow design to prevent errors and flow run failures as a consequence.
We have a use case where data from a Learning Management System (LMS) is exported as a csv file and saved to a network folder. The file is copied to the OneDrive for Business of a service account that runs a scheduled dataflow to import the data into a Dataverse table. Added rows will trigger a Power Automate flow that will add new rows to a Assessment table and make the (imported) data “relational” by filling out several lookup columns for employee, manager and (resource) planner, all based on an e-mail address in the source data. Now the e-mail address of the employee and its manager is “always” available but the one for the planner is missing sometimes. How to handle this in our Power Automate flow design?
The planners are stored with their e-mail address in a separate table in Dataverse, and a ‘List rows’ action is used to get the planner id of the row that matches the e-mail address. In case the outputs of the ‘List rows’ action is empty, a new row is added to the Planners table if there’s an e-mail address available. If not, the lookup column for the planner should be left empty when adding the new Assessment row.
So the Power Automate flow design has to anticipate on three possible outcomes to set the lookup column for the planner:
- Use the planner id from the outputs of the ‘List rows’ action
- Use the planner id from the outputs of the ‘Add a new row’ action
- Leave the lookup column empty
The two actions to get the planner id are implemented in the following way:
The Name for the new Planner row is based on the name part of the e-mail address constructed by the expression:
replace(substring(triggerOutputs()?['body/xxx_planneremail'],0,indexOf(triggerOutputs()?['body/xxx_planneremail'],'@')),'.',' ')We could add extra conditions to check the outputs of the preceding actions but this will blow up the flow with -unnecessary- actions; we can do this inline in the input field for the lookup column in the ‘Add a new row’ (Assessment) action with the help of an expression that has the logic:
If the outputs are empty then leave the input field empty, else set the lookup column to /xxx_planners(guid)
The expression consists of two “code snippets”, the first one to check if the outputs are empty:
empty(coalesce(first(outputs('List_rows_|_Planner:_Email=Email')?['body/value'])?['xxx_plannerid'],outputs('Add_a_new_row_|_Planner')?['body/xxx_plannerid']))The coalesce function will return the first non-null value from the two outputs, either from the ‘List rows’ or the ‘Add a new row’ action. In the second one the concat function is used to build the string /xxx_planners(guid)
concat('/xxx_planners(',coalesce(first(outputs('List_rows_|_Planner:_Email=Email')?['body/value'])?['xxx_plannerid'],outputs('Add_a_new_row_|_Planner')?['body/xxx_plannerid']),')')The complete expression is as follows, in where the column is set to null if the outputs are empty or to the value of one of the outputs:
if(empty(coalesce(first(outputs('List_rows_|_Planner:_Email=Email')?['body/value'])?['xxx_plannerid'],outputs('Add_a_new_row_|_Planner')?['body/xxx_plannerid'])),null,concat('/xxx_planners(',coalesce(first(outputs('List_rows_|_Planner:_Email=Email')?['body/value'])?['xxx_plannerid'],outputs('Add_a_new_row_|_Planner')?['body/xxx_plannerid']),')'))In this way any of the three outcomes will let the flow run be successful. Flow Run Failure Is Not an Option !
One comment