Extracting certain items from an array

[New-AzGuru]

I have a large list or array that comes in and it's in plain text format.  I need to extract a small portion of the list which is all of the "Payments" entries, but not "Withdrawals" or anything after that.  (A couple of examples of the relevant portion of the lists are shown at the end.)  And as you see in the lists, there can be different numbers of payment items, but it's typically around 3-5 items.

I found a way to search and return the list index values of "Payments" and "Withdrawals", which are different each time I receive a new list.  Like in Example 1, Payments might be at index 13, and Withdrawals would be 28.  My search returns the 2 indexes as variables, so I was trying to figure out some way to extract the items in the array between the 2 numbers.  But I just can't quite get my head around this one.

Ideally, I'd like to extract them in a table format so I was thinking I needed to do an "apply to each" action, and then somehow add a condition that every 3 item starts a new row in the table.  So for Example 1 it'd ultimately it'd look like this:

Payments	Amount	Number
Credit Card	110.48	32
Venmo	52.75	4
PayPal	272.52	11
Cash	100.00	1

Any thoughts or nudges to get me going in the right direction on this one?  Appreciate the help in advance.

LIST EXAMPLE 1

"Payments",
"Amount",
"Number",
"Credit Card",
"110.48",
"32",
"Venmo",
"52.75",
"4",
"PayPal",
"272.52",
"11",
"Cash",
"100.00",
"1",
"Withdrawals",


LIST EXAMPLE 2

"Payments",
"Amount",
"Number",
"Credit Card",
"110.48",
"32",
"Venmo",
"52.75",
"4",
"ApplePay",
"419.02",
"17",
"Withdrawals",

[v-mengmli-msft]

Hi @New-AzGuru ,

Take "List Example 2" as an example, if this is the outputs of flow.

Do you want this?

Please try this:

Key expression:

Please do not forget to replace action name with yours.

split(replace(replace(outputs('Compose'),outputs('Compose_3'),''),'""',' '),' ')

or(contains(item(),'Payments'),contains(item(),'Amount'),contains(item(),'Number'))

Best regards,

Rimmon

[lbendlin]

I mean, it's _possible_  to do that but it's a lot of effort.  This is stuff that's better done outside of Power Automate.

{
  "type": "Scope",
  "actions": {
    "Initialize_variable_2": {
      "type": "InitializeVariable",
      "inputs": {
        "variables": [
          {
            "name": "dt",
            "type": "string",
            "value": "\"Payments\",\"Amount\",\"Number\",\"Credit Card\",\"110.48\",\"32\",\"Venmo\",\"52.75\",\"4\",\"PayPal\",\"272.52\",\"11\",\"Cash\",\"100.00\",\"1\",\"Withdrawals\","
          }
        ]
      }
    },
    "Initialize_variable_3": {
      "type": "InitializeVariable",
      "inputs": {
        "variables": [
          {
            "name": "ar",
            "type": "array",
            "value": "@split(variables('dt'),',')"
          }
        ]
      },
      "runAfter": {
        "Initialize_variable_2": [
          "SUCCEEDED"
        ]
      }
    },
    "Initialize_variable_4": {
      "type": "InitializeVariable",
      "inputs": {
        "variables": [
          {
            "name": "Row",
            "type": "integer",
            "value": 0
          }
        ]
      },
      "runAfter": {
        "Initialize_variable_3": [
          "SUCCEEDED"
        ]
      }
    },
    "Do_until": {
      "type": "Until",
      "expression": "@greater(variables('Row'),9999)",
      "limit": {
        "count": 60,
        "timeout": "PT1H"
      },
      "actions": {
        "Compose": {
          "type": "Compose",
          "inputs": "@concat(variables('ar')[add(mul(variables('Row'),3),0)],',',variables('ar')[add(mul(variables('Row'),3),1)],',',variables('ar')[add(mul(variables('Row'),3),2)])"
        },
        "Increment_variable": {
          "type": "IncrementVariable",
          "inputs": {
            "name": "Row",
            "value": 1
          },
          "runAfter": {
            "Compose": [
              "Succeeded"
            ]
          }
        },
        "Condition": {
          "type": "If",
          "expression": {
            "and": [
              {
                "equals": [
                  "@variables('ar')[mul(variables('Row'),3)]",
                  "@'\"Withdrawals\"'"
                ]
              }
            ]
          },
          "actions": {
            "Set_variable": {
              "type": "SetVariable",
              "inputs": {
                "name": "Row",
                "value": 10000
              }
            }
          },
          "else": {
            "actions": {}
          },
          "runAfter": {
            "Increment_variable": [
              "Succeeded"
            ]
          }
        }
      },
      "runAfter": {
        "Initialize_variable_4": [
          "SUCCEEDED"
        ]
      }
    }
  },
  "runAfter": {}
}

[grantjenkins]

Hopefully this is what you're after.

See full flow below. I'll go into each of the actions.

Initialize variable is an array called items that contains a sample of the data you provided.

Filter array uses the following expressions to remove the Withdrawals items from the array.

//From
split(first(split(join(variables('items'), '||'), 'Withdrawals')), '||')

//Expression
trim(item())

Select maps out the data into an array of objects using the following expressions.

//From (skip the headers and chunk the items into batches of 3)
chunk(skip(body('Filter_array'), 3), 3)

//Payments
item()?[0]

//Amount (converted to float)
float(item()?[1])

//Number (converted to int)
int(item()?[2])

After running the flow we would end up with the following output.

[
  {
    "Payments": "Credit Card",
    "Amount": 110.48,
    "Number": 32
  },
  {
    "Payments": "Venmo",
    "Amount": 52.75,
    "Number": 4
  },
  {
    "Payments": "ApplePay",
    "Amount": 419.02,
    "Number": 17
  }
]

[New-AzGuru]

Hi everyone,

Thank you for the replies.  I'll be testing each of these to see if I can get it to work.  And sorry I wasn't clear enough, but the sample lists I provided were just a snippet of the overall, larger list I receive.  That's why I was trying to pull the data out between the 2 index values, since Payments is always first, followed by 3-5 entries, then it is always followed by the Withdrawals section.  A more accurate list type that I receive is below.

So maybe I could somehow just pull the relevant info out into a new list, then your solutions would work?

LIST EXAMPLE 1

"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Payments",                <------- Index 13 (not always the same)
"Amount",
"Number",
"Credit Card",
"110.48",
"32",
"Venmo",
"52.75",
"4",
"PayPal",
"272.52",
"11",
"Cash",
"100.00",
"1",
"Withdrawals",             <------- Index 28 (not always the same)
"100.00",
"100.00",
"100.00",
"100.00",
"100.00",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",
"Extra info and sections",

[lbendlin]

If you want the stuff between "Payments" and "Withdrawals" then you can split the array twice (once by each term), and then mentally add the "Payments"  element back in.

[grantjenkins · Accepted solution]

To handle that (lines before the Payment) you can use my previous example with the following changes.

For the Filter array, change the From expression to the following.

split(first(split(last(split(join(variables('items'), '||'), 'Payments')), 'Withdrawals')), '||')

And for the Select, change the From expression to the following.

chunk(skip(body('Filter_array'), 2), 3)

[New-AzGuru]

Thank you, should have a chance to test this tomorrow or next week and I'll report back.

[Chriddle]

And another proposal 😉:

List (Compose) is an example list of type array

Spoiler

[
"Not needed",
"Payments",
"Amount",
"Number",
"Credit Card",
"110.48",
"32",
"Venmo",
"52.75",
"4",
"PayPal",
"272.52",
"11",
"Cash",
"100.00",
"1",
"Withdrawals",
"Not needed"
]

Portion (Compose)

extracts the relevant part of the list by converting the list to a string and extract the substring with slice(). Then converting it back to an array.

json(
	concat(
		'[',
		slice(
			string(outputs('Data')),
			indexOf(
				string(outputs('Data')),
				'"Payments"'
			),
			indexOf(
				string(outputs('Data')),
				concat(',"Withdrawals"')
			)
		),
		']'
	)
)

HtmlTableBody (Select)

From:

chunks the list into collections of three and skips the first one [the table headings]

skip(
	chunk(outputs('Portion'), 3),
	1
)

Map

adds the HTML for the table row

concat(
	'<tr>',
	'<td>',
	item()[0],
	'</td>',
	'<td>',
	item()[1],
	'</td>',	
	'<td>',
	item()[2],
	'</td>',	
	'</tr>'
)

HtmlTable (Compose)

creates the HTML table

concat(
	'<table>',
	'<tr>',
	'<th>',
	outputs('Portion')[0],
	'</th>',
	'<th>',
	outputs('Portion')[1],
	'</th>',
	'<th>',
	outputs('Portion')[2],
	'</th>',
	join(body('HtmlTableBody'), decodeUriComponent('%0A')),
	'</tr>',
	'</table>'
)

The resulting HTML

<table><tr><th>Payments</th><th>Amount</th><th>Number</th><tr><td>Credit Card</td><td>110.48</td><td>32</td></tr>
<tr><td>Venmo</td><td>52.75</td><td>4</td></tr>
<tr><td>PayPal</td><td>272.52</td><td>11</td></tr>
<tr><td>Cash</td><td>100.00</td><td>1</td></tr></tr></table>

looks like this

[lbendlin]

This is a great thread. Full of "oh, I didn't know there was a function for that" moments. When you stop "fighting the API" and start embracing what's available. Cheers!

[New-AzGuru]

Sorry for the delay responding - just took me a while to finally get this tested.  But this indeed worked using your first post and the changes from the second one.  Very much appreciate your help Grant - thank you and everyone else that contributed to this.  Happy Holidays all!

[New-AzGuru]

Chriddle - thanks for this as well - I plugged all this in and it also worked!  I'm not very familiar with JSON so I'll have to play around with this and try to learn it more so I can figure out how to change it up to incorporate into other solutions.  I appreciate your detailed and thorough method!