Power Community

Power Community

Process AI Builder data using xpath in Power Automate

Today I was asked to have a look at the dynamic data that is generated by AI builder and make it easier to process AI Builder data.

AI Builder data

AI Builder is frequently used for scraping data of documents, like Invoices or Purchase Orders.

When you create a model in AI Builder, Power Automate is used to process data and then create records in for example SharePoint or Dataverse.

{

"EQ_0020_826e4c4cc70eb44460953fb9aad00103": {
"value": "15%",
"displayName": "EQ %",
"fieldType": "string",
"confidence": 0.69,
"text": "15%",
"valueLocation": {
"pageNumber": 1,
"boundingBox": {
"left": 0.378235284019919,
"top": 0.328181808645075,
"width": 0.021764727199778844,
"height": 0.00863636623729358,
"polygon": {
"coordinates@@@@odata.type": "#Collection(Microsoft.Dynamics.CRM.crmbaseentity)",
"coordinates": [
{
"x": 0.378235284019919,
"y": 0.328181808645075,
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
{
"x": 0.40000001121969786,
"y": 0.328181808645075,
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
{
"x": 0.40000001121969786,
"y": 0.3368181748823686,
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
{
"x": 0.378235284019919,
"y": 0.3368181748823686,
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
}
],
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
"regions@@@@odata.type": "#Collection(Microsoft.Dynamics.CRM.crmbaseentity)",
"regions": [
{
"pageNumber": 1,
"polygon": {
"coordinates@@@@odata.type": "#Collection(Microsoft.Dynamics.CRM.crmbaseentity)",
"coordinates": [
{
"x": 0.3780941121718463,
"y": 0.3281272758137096,
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
{
"x": 0.40000001121969786,
"y": 0.3281272758137096,
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
{
"x": 0.40000001121969786,
"y": 0.33637272227894177,
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
{
"x": 0.3780941121718463,
"y": 0.33680909330194647,
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
}
],
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
}
],
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
"@odata.type": "#Microsoft.Dynamics.CRM.expando"
},
"XYZe631743fad000fc5fc81902eb59baf34": {
"value": 16,
...

The above data can be useful, however it is also complex to process. Especially if the fieldnames ( in the above example EQ_0020_826e4c4cc70eb44460953fb9aad00103 and XYZ631743fad000fc5fc81902eb59baf34)

Required data format

Instead of the above single object with many properties, my client preferred the data in the following format:

[

{
"fieldName": "EQ_0020_826e4c4cc70eb44460953fb9aad00103",
"value": "15%",
"displayName": "EQ %"
},
{
"fieldName": "XYZe631743fad000fc5fc81902eb59baf34",
"value": "16",
...

So we need:

  1. Flexible fieldnames (no hardcoding of those internal fieldnames)
  2. Display the values and the display name as configured within AI Builder.
  3. Use a child flow so that it can be called form other flows.

Where to start Process AI Builder data?

This problem is actually quite complex. How do we process the above data, while we don’t know what the properties are that we want to process.

Well, the XPath function is our friend here.

In my flow I will add a manual trigger with a parameter for the AI Builder Data.

Process AI Builder data using xpath in Power Automate Microsoft 365 image 14

Creating valid XML

To Process AI Builder data, we first need to turn the AI Builder data into a valid XML object so that we can use the xml function, to convert the data into XML data.

Process AI Builder data using xpath in Power Automate Microsoft 365 image 15

In the above compose action we use the following code:

json(triggerBody()['text'])

The above action gives us an json object. The get the Xml version of the above data the xml function is used.

Process AI Builder data using xpath in Power Automate Microsoft 365 image 16

The following code will validate that our data can be converted to valid xml.

xml(outputs('Add_Xml_Nodes'))

Get the top level Xml nodes

We will first need to get the names of the fields found in our AI Builder data. Where our xml starts with a single xml node, followed by a number of properties found by our AI Builder Model. In my case 26 properties.

Process AI Builder data using xpath in Power Automate Microsoft 365 image 21

The following xpath expression will get the root of the xml data and select the top nodes. This will give us an array of top level nodes in our model.

xpath(xml(outputs('Xml_Version')), '/xml/*')

Processing the Xml

Stepping through the array that we have just generated, we can run an apply to each action (you might want to se the concurrency to 50 to speed things up a bit)

Process AI Builder data using xpath in Power Automate Microsoft 365 image 17

There are two steps in our Apply to each. The Each Object Compose action displays the data found in each node.

Process AI Builder data using xpath in Power Automate Microsoft 365 image 18

The code in the above step:

base64ToString(items('Apply_to_each')?['$content'])

Creating my result array

Now we want to create an object with just the properties that I want to end up with. A simple Compose action will do the trick again.

Process AI Builder data using xpath in Power Automate Microsoft 365 image 19

You can of course collect other data too. In my case I’m getting the fieldname, the value and the display name of each property. In the above example the following code is used:

xpath(xml(outputs('Each_object')),'name(/*)')
first(xpath(xml(outputs('Each_object')),'/*/value/text()'))
first(xpath(xml(outputs('Each_object')),'/*/displayName/text()'))

Pieter’s method to collect the array

Now we use Pieter’s method to turn the multiple “single objects” found, into an array.

Process AI Builder data using xpath in Power Automate Microsoft 365 image 20

The code used in this last Compsoe action is:

outputs('Single_JSON_object')

The complete flow to process AI Builder data

So we should now end up with the following flow.

The complete flow to process AI Builder data

Further thoughts on how to Process AI Builder data

Some of the XPath processing in this post could be optimized, however the xpath in Power Automate is limited and doesn’t always handle advanced queries very well. However keeping the xpath queries simple also has its advantages.

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Embed Python Visuals in Power BI Desktop – Quick Review

Hi Folks, This post is all about embedding Python visuals in Power BI, you will need to install the respective...

More Articles Like This

- Advertisement -spot_img