Formula creates a comma when using arrayjoin

[Nadav_Ben_Gal]

Hi community!
I have a very strange issue here…
I have a lot of duplicates within the same field, a lookup field (I’m not so sure why, but that’s for a different question)
In order to try to create a field with no duplicates, I created another field with the formula:
(ARRAYJOIN({Most recent First Name :zap: },", "))
It works fine for most of the records, but in some it creates a a comma in front of the name, which I couldn’t get rid of. It happens only in some of the records, and only those that are not duplicated to begin with
For the full picture, the next step is a field that takes the string that I created and find the name to the left of the comma which gives me just the name.
Any idea what is it that I’m doing wrong?
Thanks!

[Nadav_Ben_Gal]

Here is a screenshot of the base

[kuovonne · Accepted solution]

Welcome to the Airtable community!

Could there be a linked record that has no value in the looked-up field?

Try using a rollup field (instead of the lookup & formula field) based on the same field as the lookup, with the ARRAYCOMPACT function

ARRAYJOIN(ARRAYCOMPACT(values),", ")

[Nadav_Ben_Gal]

I don’t know what magic you did here, but it worked!
Thanks so so much!