Unanswered

Get rid of unnecessary commas when sending data to excel

(0) Share

Report

Posted on by CharlesBrewer

Sorry on the subject I wasn't sure how to work it. Basically I have a flow pulling data from a form and dumping it into Online Excel. The problem is there are several fields, like for parts numbers, where they might put anywhere between 1-5, and they are different fields. So the "add a row into a table" looks like this :

But if there is only say, one part number, the excel will look like : 40303, , , ,

Which looks kind of stupid. But since it's 5 separate fields I don't know what step(s) I can do to remove the excess commas?

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

David_MA 12,966 Super User 2025 Season 2 on at

Like (0)

Report

Since I don't fully understand if the image you posted is part of your process or part of your current flow, it makes it challenging to offer advice.

If the image is how the data gets generated, you can use the replace expression to replace the commas with something else when you populate Excel. You don't specify what you want to use instead, so that is up to you. If you wanted to replace the comma with a hyphen you would use this expression: replace(triggerOutputs()?['body/PartNumber'], ',', '-')

The highlighted portion you would replace with the field from your dynamic content. However, I may be misinterpreting what you posted, so it would be better to post a screen show of your entire flow to avoid confusion.

If the image is how you populate the Excel spreadsheet, then use if statements to see if each of those fields is empty and if they are not, return the value and add a comma afterward. Then an expression like this could be used for each of the five values: if(empty(triggerOutputs()?['body/PartNumber']), null, concat(triggerOutputs()?['body/PartNumber'], ','))

It checks to see if the field is empty. If not, it returns the value followed by a comma. If it is empty, nothing is returned. Unfortunately, there is no ends with expression, so this will result in the field ending with a comma. There are other methods that are more complex to achieve this so it does not end with a comma.

One other solution, depending on what the image represents, add five columns to your spreadsheet and populate each value in its own column (Part 1, Part 2, Part 3, Part 4, Part 5) instead of combining them into one. This would make filtering the spreadsheet by part number easier.

Was this reply helpful? Yes No
CharlesBrewer 674 on at

Like (0)

Report

@David_MA

I think, from reading your response you are under the impression that my screenshot is 1 field. That's the difficult part, it's 5 different fields from a form, and I don't know how many of them are blank and I don't want all the misc commas.

Was this reply helpful? Yes No
Suggested answer

Michael E. Gernaey 53,329 Super User 2025 Season 2 on at

Like (1)

Report

Hi @CharlesBrewer

OMG firstly I have a family member with the same name.. i am sure lol there are more than 1 of you but :-)

You would want to use a condition

Essentially you would say

Concat(

And then use separate coalesce statements between

);

Concat(coalesce(myvariable, concat(myvarible,','), ''),

Concat(coalesce(myvariable, concat(myvarible,','), ''),

Concat(coalesce(myvariable, concat(myvarible,','), ''),

Concat(coalesce(myvariable, concat(myvarible,','), ''),

Concat(coalesce(myvariable, concat(myvarible,','), '')

Now ths is my 337 Am brain working here and I could write it cleaner, but that is what you want, you want to NOT put a comma if the value is blank, you just want empty string ''

Was this reply helpful? Yes No
CharlesBrewer 674 on at

Like (0)

Report

@Michael E. Gernaey

That's maybe what I want but I don't get the syntax? I tried writing an expansion for it but I get an 'invalid expression,' here was my interpretation of what you're saying :

Concat(
concat(coalesce(triggerOutputs()?['body/PART1'],concat(triggerOutputs()?['body/PART1'],','),)''),
concat(coalesce(triggerOutputs()?['body/PART2'],concat(triggerOutputs()?['body/PART2'],','),)''),
concat(coalesce(triggerOutputs()?['body/PART3'],concat(triggerOutputs()?['body/PART3'],','),)''),
concat(coalesce(triggerOutputs()?['body/PART4'],concat(triggerOutputs()?['body/PART4'],','),)''),
concat(coalesce(triggerOutputs()?['body/PART5'],concat(triggerOutputs()?['body/PART5'],','),)''));

Was this reply helpful? Yes No
CharlesBrewer 674 on at

Like (0)

Report

Hey there @Michael E. Gernaey or anyone,

Any help with this syntax? Much thanks.

Was this reply helpful? Yes No

MarkRahn

1,229 Super User 2025 Season 2 on at

Like (1)

Report

Hi @CharlesBrewer

I did a test with a simple flow with 5 Optional Inputs. I used a Compose Action to generate the output you are looking for.

@{if(equals(triggerOutputs()?['body/PART1'], null), '', if(greater(length(triggerOutputs()?['body/PART1']), 0), triggerOutputs()?['body/PART1'], ''))}
@{if(equals(triggerOutputs()?['body/PART2'], null), '', if(greater(length(triggerOutputs()?['body/PART2']), 0), concat(', ',triggerOutputs()?['body/PART2']), ''))}
@{if(equals(triggerOutputs()?['body/PART3'], null), '', if(greater(length(triggerOutputs()?['body/PART3']), 0), concat(', ',triggerOutputs()?['body/PART3']), ''))}
@{if(equals(triggerOutputs()?['body/PART4'], null), '', if(greater(length(triggerOutputs()?['body/PART4']), 0), concat(', ',triggerOutputs()?['body/PART4']), ''))}
@{if(equals(triggerOutputs()?['body/PART5'], null), '', if(greater(length(triggerOutputs()?['body/PART5']), 0), concat(', ',triggerOutputs()?['body/PART5']), ''))}

If checks the inputs for nulls and then checks if they have any length. For Inputs 2 thru 5, a comma is added in front of the item.

If you are able to get Coalesce to work in this instance, please post what you find.

Coalesce evaluates its arguments in order and returns the first value that isn't blank or an empty string. You would still need to check what is returned to determine if it was an empty string I believe.

Keep in mind that '' (two single quotes) is an empty string. For Inputs 2 thru 5, a concat is done with ', ' (a string with a comma and space) and the Input value.

If this had come in as an Array, you could have just used a JOIN(<array>,','). I have used this when putting together email addresses but with a ';'

This community is supported by individuals freely devoting their time to answer questions and provide support. They do it to let you know you are not alone. This is a community.

If someone has been able to answer your questions or solve your problem, please click Does this answer your question. This will help others who have the same question find a solution quickly via the forum search.

If someone was able to provide you with more information that moved you closer to a solution, throw them a Like. It might make their day. 😊

Thanks
-Mark

Was this reply helpful? Yes No

CharlesBrewer 674 on at

Like (1)

Report
@MarkRahn

So I made a compose and I tried to copy/paste what you had, and it kept saying incorrect syntax. So for funsies I ran it through Copilot, and it gave me this :

concat( if(equals(triggerOutputs()?['body/PART1'], null), '', triggerOutputs()?['body/PART1']), if(and(not(equals(triggerOutputs()?['body/PART1'], null)), not(empty(triggerOutputs()?['body/PART2']))), ', ', ''), if(equals(triggerOutputs()?['body/PART2'], null), '', triggerOutputs()?['body/PART2']), if(and(not(equals(triggerOutputs()?['body/PART2'], null)), not(empty(triggerOutputs()?['body/PART3']))), ', ', ''), if(equals(triggerOutputs()?['body/PART3'], null), '', triggerOutputs()?['body/PART3']), if(and(not(equals(triggerOutputs()?['body/PART3'], null)), not(empty(triggerOutputs()?['body/PART4']))), ', ', ''), if(equals(triggerOutputs()?['body/PART4'], null), '', triggerOutputs()?['body/PART4']), if(and(not(equals(triggerOutputs()?['body/PART4'], null)), not(empty(triggerOutputs()?['body/PART5']))), ', ', ''), if(equals(triggerOutputs()?['body/PART5'], null), '', triggerOutputs()?['body/PART5']) )

This seems to be working? Here is a picture of 2 before, and 2 after as a test :

So, yay I guess? Unless you see any problems?

Was this reply helpful? Yes No
MarkRahn 1,229 Super User 2025 Season 2 on at

Like (0)

Report

Hi @CharlesBrewer

That looks good.

For the Formulas you use to be able to cut and paste where it would recognize the "@{ }" as containing a formula. That might be different in the new UI.

If you want to give me credit for the Answer, that would be great. But if you want to mark your last Reply as the Answer I am ok with that too.

This community is supported by individuals freely devoting their time to answer questions and provide support. They do it to let you know you are not alone. This is a community.

If someone has been able to answer your questions or solve your problem, please click Does this answer your question. This will help others who have the same question find a solution quickly via the forum search.

If someone was able to provide you with more information that moved you closer to a solution, throw them a Like. It might make their day. 😊

Thanks
-Mark

Was this reply helpful? Yes No
CharlesBrewer 674 on at

Like (0)

Report

TBH I don't have the check box to mark an answer. For some reason this thread doesn't show up in my activity either, not sure what's going on there.

Was this reply helpful? Yes No