Remove commas on concatenated columns

[av9]

I have three calculated columns, Customers, Accounts and Staff I want to concatenate into one ccolumn. I created this calculated column, but I notice the commas still appear if one of the three tables is blank.

Attendees = Appointments[Customers]  & ", "  &  Appointments[Accounts] & ", " & Appointments[Staff]

So my column looks like this if there is only a customer field available.

Attendees

John Smith, ,

 

 how do I remove commas when not required?

[amitchandak · Accepted solution]

@av9 , you can use if to control

Attendees = Appointments[Customers] & if(isblank(Appointments[Accounts]),"" , ", " & Appointments[Accounts] )& if(isblank(Appointments[Staff]) , "" ,", " & Appointments[Staff])

[PhilipTreacy · Accepted solution]

Hi @av9 

Try this

Attendees = Appointments[Customers]   &  if(len(Appointments[Accounts])>0,", " &  Appointments[Accounts] , "") & if(len(Appointments[Staff])>0, ", " & Appointments[Staff], "")

Phil

[NamishB]

Hi @av9 ,

Can you try this

Text.Combine(List.Select({Appointmnets[Customers],Appointmnets[Accounts],Appointmnets[Staff]}, each _<> "" and _ <> null)," , ")

Hope this helps.

Cheers,

-Namish B

[amitchandak · Accepted solution]

@av9 , you can use if to control

Attendees = Appointments[Customers] & if(isblank(Appointments[Accounts]),"" , ", " & Appointments[Accounts] )& if(isblank(Appointments[Staff]) , "" ,", " & Appointments[Staff])