How to join non-primitive array values using expression

(0) Share

Report

Posted on by elshadlive

Hi,

An "Employees" field from Sharepoint list returns an array of JSON datatype, as follows:

[{
 "DisplayName": "John Doe",
 "Department": null,
 "JobTitle": null
}, {
 "DisplayName": "Mary Jane",
 "Department": null,
 "JobTitle": null
}]

In the next step, this field value inserted in Excel.

What I need is to join in one string only DisplayName values separated by a semicolon, like: "John Doe, Mary Jane"

I am able to get DisplayName value using index numbers:

triggerBody()?['Employees'][0]?['DisplayName']
// returns: John Doe

I can also concatenate several array indexes, but I don't know in advance how many elements will be in an array.

How can I join values from an array using expressions (formulas) only? Maybe some additions to join function will help? I tried the next one, but it didn't work

join(triggerBody()?['Employees']?['DisplayName'], ';')

All responses (4)

Answers (1)

elshadlive 15 on at

Like (1)

Report

Re: How to join non-primitive array values using expression

@v-bacao-msft thanks for the reply. I'll dig deeper and look for a maximum minimalistic solution. I'll post here if any success.
v-bacao-msft on at

Like (1)

Report

Re: How to join non-primitive array values using expression

Hi @elshadlive ,

Unfortunately, as far as I know, the WDL function does not contain a function can be used to iterate items in an array.

So we only can use Apply to each to iterate items in Power Automate.

Best Regards,
elshadlive 15 on at

Like (0)

Report
Re: How to join non-primitive array values using expression

@v-bacao-msft thank you for your reply.
Can I do the same without using Activities, but using WDL inline expressions instead? I am not familiar with WDL and not sure if there is some iteration operator.
I have about 30 columns from Sharepoint list, which must be read and then mapped (written) to Excel sheet. The field in a subject is only one of multiple array fields. Creating multiple Activities for each of those fields will be a nightmare.
So, I am looking for a solution of converting multi-valued arrays to strings on mapping level for each field.
Assuming that I have to make different conversions and calculations on most of these fields I'd be grateful if you could suggest "coding way" of manipulating data instead of using Activities (of course within possibilities of the platform).
On top of my mind I am thinking about something like:
join(createArray(triggerBody()?['Employees'](ApplyForEachOperator)['DisplayName']), ';') or triggerBody()?['Employees'](SelectFunction)['DisplayName'] // triggerBody()?['Employees'] is my array field
where ApplyForEachOperator and SelectFunction keywords are iteration or selection operators
Any suggestions?
Verified answer

v-bacao-msft on at

Like (1)

Report

Re: How to join non-primitive array values using expression

Hi @elshadlive ,

Please try the following configuration:

Best Regards,