cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lfk73
Helper I
Helper I

Removing junk

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)

 

lfk73_0-1666607306783.png

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?

1 ACCEPTED SOLUTION

Accepted Solutions
CraigStewart
Super User
Super User

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

View solution in original post

8 REPLIES 8
CraigStewart
Super User
Super User

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

 

CraigStewart_0-1666613883874.png

 

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
Super User
Super User

This will still work however many emails there are.

 

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

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?

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
Super User
Super User

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

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

VictorIvanidze
Community Champion
Community Champion

A different way to do the same:

VictorIvanidze_1-1671891759789.png

 

 

 

--------------------------------------------------------------------------------------
Contact me if you are interested in custom Power Automate development.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,385)