Answered

Compose issue

(1) Share

Report

Posted on by Ciaran

263

I have a flow that issues Word documents as attachments. Each word document contains home addresses of each individual who receives the document / attachment. The home address is made up of the fields i.e. Street and House Number, Second Address Line, District and Postal Code. The data for the addresses is derived from an Excel spreadsheet.

Some of the 4 address field data columns may, on occasion, contain null values causing gaps in the address fields in the word document when the flow runs.

I have created a compose action with the following expression:-

concat(
    items('For_Each')?['Street and House Number'],
    '\n',
    items('For_Each')?['Second Address Line'],
    '\n',
    items('For_Each')?['District'],
    '\n',
    items('For_Each')?['Postal Code']
)
 
This removes the gaps in the Word document but the address appears as Street and House Number Second Address Line District Postal Code however
it needs to be like this
 
Street and House Number
Second Address Line
District
Postal Code 
 
As in the above expression I have tried numerous CGPT and Co Pilot suggestions such as '\n' '\r\n' '<br>' '||' at the end of each of the
expression rows above but they are all appearing as text in the Word document when the merge flow runs.
 
I would love to be able to solve how to get the flow to return the address fields as above and which can handle removing any gaps when the data 
is null.
 
My flow looks like this but I would happily take other suggestions / improvements to achieve the same outcome.
 
Many thanks for any help experts can provide.

Test Data.xlsx

Categories:

General topics

All responses (12)

Answers (1)

Sort by

Suggested answer

SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Try the coalesce function.

trim( concat( if(not(empty(items('For_Each')?['Street and House Number'])), items('For_Each')?['Street and House Number'], ''), if(and(not(empty(items('For_Each')?['Street and House Number'])), not(empty(items('For_Each')?['Second Address Line']))), '\n', ''), if(not(empty(items('For_Each')?['Second Address Line'])), items('For_Each')?['Second Address Line'], ''), if(and(or(not(empty(items('For_Each')?['Street and House Number'])), not(empty(items('For_Each')?['Second Address Line']))), not(empty(items('For_Each')?['District']))), '\n', ''), if(not(empty(items('For_Each')?['District'])), items('For_Each')?['District'], ''), if(and(or(not(empty(items('For_Each')?['Street and House Number'])), not(empty(items('For_Each')?['Second Address Line'])), not(empty(items('For_Each')?['District']))), not(empty(items('For_Each')?['Postal Code']))), '\n', ''), if(not(empty(items('For_Each')?['Postal Code'])), items('For_Each')?['Postal Code'], '') ) )
Ciaran 263 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi

Thank you for taking the time to help me but it hasn't worked.

When I use

trim(

concat(

if(not(empty(items('For_Each')?['Street and House Number'])), items('For_Each')?['Street and House
Number'], ''),

if(and(not(empty(items('For_Each')?['Street and House Number'])), not(empty(items('For_Each')?['Second
Address Line']))), '\n', ''),

if(not(empty(items('For_Each')?['Second Address Line'])), items('For_Each')?['Second Address Line'], ''
),

if(and(or(not(empty(items('For_Each')?['Street and House Number'])), not(empty(items('For_Each')?
['Second Address Line']))), not(empty(items('For_Each')?['District']))), '\n', ''),

if(not(empty(items('For_Each')?['District'])), items('For_Each')?['District'], ''),

if(and(or(not(empty(items('For_Each')?['Street and House Number'])), not(empty(items('For_Each')?
['Second Address Line'])), not(empty(items('For_Each')?['District']))), not(empty(items('For_Each')?
['Postal Code']))), '\n', ''),

if(not(empty(items('For_Each')?['Postal Code'])), items('For_Each')?['Postal Code'], '')

)

)

the address appears like this

10 Any Street\nLondon\nLW14 8LW

It is picking up the \n code as text in the merged document. Do you know if there is another means to achieve this.

Many thanks.
SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

@Ciaran Can you please provide your excel file? I can try at my end.
Ciaran 263 on at

Like
a
(1)

Report
Copy link

Link copied!

Yes of course. I have just attached a sample spreadsheet to the original post.

Many thanks for your help Sudeep
Suggested answer

SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!

Try

trim(

concat(

if(not(empty(items('For_Each')?['Street and House Number'])), items('For_Each')?['Street and House Number'], ''),

if(and(not(empty(items('For_Each')?['Street and House Number'])), not(empty(items('For_Each')?['Second Address Line']))), decodeUriComponent('%0A'), ''),

if(not(empty(items('For_Each')?['Second Address Line'])), items('For_Each')?['Second Address Line'], ''),

if(and(or(not(empty(items('For_Each')?['Street and House Number'])), not(empty(items('For_Each')?['Second Address Line']))), not(empty(items('For_Each')?['District']))), decodeUriComponent('%0A'), ''),

if(not(empty(items('For_Each')?['District'])), items('For_Each')?['District'], ''),

if(and(or(not(empty(items('For_Each')?['Street and House Number'])), not(empty(items('For_Each')?['Second Address Line'])), not(empty(items('For_Each')?['District']))), not(empty(items('For_Each')?['Postal Code']))), decodeUriComponent('%0A'), ''),

if(not(empty(items('For_Each')?['Postal Code'])), items('For_Each')?['Postal Code'], '')

)

)
Ciaran 263 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi Sudeep

The Compose action is bringing the address out in the correct format however in the populate Word action when I add the Compose Output it isnt merging correctly. The address lines appear consecutively

Eg 10 Any Street London LW14 8LW not

10 Any Street

London

LW14 8LW
Michael E. Gernaey 53,970 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

Hello

Can you please respond to the folks sending you answers. They took a lot of time, please take a little time to pick an answer or explain why its not what you need.
Ciaran 263 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Mike I have only just seen Alex's answer and will respond now to thank him for his clear and concise response. However, I wish to also advise you that I had responded to each answer / request from Sudeep who had also very kindly assisted me. Kind regards.
Ciaran 263 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi Alex

Thank you very kindly for taking the time out to assist me. I really appreciate it. Kind regards.
Ciaran 263 on at

Like
a
(0)

Report
Copy link

Link copied!

Unfortunately I don't have access to the Encodian solution as it requires an API key. I need a solution for the Word (Business Online) connector. Any help would be greatly appreciate.