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
Table of Contents
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:
- Flexible fieldnames (no hardcoding of those internal fieldnames)
- Display the values and the display name as configured within AI Builder.
- 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.
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.
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.
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.
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)
There are two steps in our Apply to each. The Each Object Compose action displays the data found in each node.
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.
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.
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.
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.