“How do I access the SharePoint columns to add to the HTML table, I see only two outputs from the ‘Filter array’ Power Automate action.”
Depending on the input of the ‘Filter array’ action it’ll either return all the values as dynamic contents, or not. If applied on SharePoint items it’s the “or not” situation. The only available dynamic contents are Item and Body, but none of the columns. Yet filtering SharePoint items is such a common need, there must be some way to access the values!
Extract it from the JSON
The output from ‘Filter array’ will be the items in a JSON. Long time ago I recommended using ‘Parse JSON’ to get the dynamic contents back, but there’s a cleaner way.
The ‘Filter array’ action will always return an array. You input an array, filter it, and return all the results in another array.
And since the returned array is a JSON, you can navigate through it as in any other JSON. Start with handling the array, and then pick the specific properties.
Use it in an HTML / CSV table
It gets much easier though if you process the whole output at once, e.g. to create an HTML table or CSV table. The table actions will handle the array for you, just use the ‘Filter array’ output ‘Body’ in the From field. All the properties can then be accessed using the expression below.
item()?['columnInternalName']Note: you can get the column internal name from the column settings or from the JSON.
If it’s a simple column type, e.g. single line of text (Title), use just one property in the expression.
item()?['Title']If it’s a complex column type, e.g. a choice or a people picker, you’ll have to dig deeper to the actual value, e.g.
item()?['Person']?['Email']
or
item()?['Choice']?['Value']
Define the headers for each column, use the item()?[…] expression to access the value, and create the table.
Summary
You can access the SharePoint columns after using the Power Automate ‘Filter array’ action, but you’ll have to use an expression. Either process the items one by one, navigating the output JSON, or process them at once. Let it be an HTML table with items overview, or an export to CSV table, with the item()?[…] expression you can access any available value.
Can we access the data in a subsequent action AFTER creating the HTML table?
I tried to use the same method for populating the HTML table in an email action (to send email with HTML table) and I get this error:
Unable to process template language expressions in action ‘Send_an_email_(V2)’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘item()?[‘Person’]?[‘Email’]’ cannot be evaluated because property ‘Person’ cannot be selected. Property selection is not supported on values of type ‘String’.
Your tutorials are the best, thank you!
Hello Weylin,
based on the description your ‘Person’ column is not a people picker column but rather single line of text column? In that case it’s just item()?[‘Person’].
Hi Tom,
After reading through the various links and such, I’m good up to the Filter array and HTML table creation, but I cannot figure out what expression to use in the To:Sender field of the “Send an email” step. Everything I have tried has created an error so far. It’d be helpful if you could show the build out of the email To: line as well. I was using the JSON parse for this notification for single select people picker column so I could go that route, just not sure where to put it in the flow (what order, etc.)
Hello Paquita,
it uses the approach described in this article: https://tomriha.com/how-to-get-a-specific-value-from-a-json-object-in-power-automate/. Check the output of the ‘Filter array’ and navigate to the specific values you need as explained in the article.