Removing junk

[lfk73]

Long story short I am brining a number of values from PowerBi into Power Automate to process.  One value is an email address.

I am using Select action in Compose to create a table of just the email addresses (below)

I then duplicate them as there are usually many duplicates

I then want to join them using a semicolon and put them into the To field of an email however the output from the Select action contains extra garbage (see example below).

[{"Email":"peter@mydomain.com"},{"Email":"john@mydomain.com"},{"Email":"mary@mydomain.com"},{"Email":"joe@mydomain.com"},{"Email":"sam@mydomain.com"}{"Email":""}]

How can I clean this up so I just get the email addresses or is there a better way to parse out the email address data being imported from PowerBi?

[CraigStewart]

Assuming your output is always like this you could use the replace function to clean it up

replace(replace(replace(replace(replace(replace(replace(string(outputs('Compose')),'"Email":',''),'{',''),'}',''),']',''),'[',''),'""',''),'"',''),',',';')

This will take your input 

[{"Email":"peter@mydomain.com"},{"Email":"john@mydomain.com"},{"Email":"mary@mydomain.com"},{"Email":"joe@mydomain.com"},{"Email":"sam@mydomain.com"}{"Email":""}]

and give you and output of

peter@mydomain.com;john@mydomain.com;mary@mydomain.com;joe@mydomain.com;sam@mydomain.com

[lfk73]

Thanks that worked in this case but as you noted only if the output is always the same which it is not.  It could be anywhere from one email to 100 or more it's always dynamic.

[CraigStewart]

This will still work however many emails there are.

As long as your data from BI is formatted the same [{"Email":"email@address"}]

[lfk73]

I'm trying this but getting the error "The expression is invalid."  I noted there are 9 x ( and 10 x )

I'm not a expression expert so not sure if that got anything to do with the error.  Thoughts?

[lfk73]

So i added another replace on the front and it worked.

Went from:

replace(replace(replace(replace(replace(replace(replace(string(outputs('Compose')),'"Email":',''),'{',''),'}',''),']',''),'[',''),'""',''),'"',''),',',';')

To:

replace(replace(replace(replace(replace(replace(replace(replace(string(outputs('Compose')),'"Email":',''),'{',''),'}',''),']',''),'[',''),'""',''),'"',''),',',';')

[CraigStewart · Accepted solution]

Need one more replace(  at the front of the expression

replace(replace(replace(replace(replace(replace(replace(replace(string(outputs('Compose')),'"Email":',''),'{',''),'}',''),']',''),'[',''),'""',''),'"',''),',',';')

8 replace (

1 string(

1 outputs (

8 replace )

1 string)

1 outputs)

Should be good

[lfk73]

Yep thats what I guessed so tried it an it worked.  Thanks man.

[VictorIvanidze]

A different way to do the same: