How to join values with delimiter, skip empty

There are two tables having several identical columns. In the first table, the columns have text values, in the second, yes / no values for specifying combinations.
I need to make a formula so that a string is obtained only from the values ​​of the columns of the first table for which it is yes in the second one.
I know how to concatenate the values into a string, but I can’t get rid of the extra delimiters. Please help!
I made a sample that will show you what the problem is.

I did it using the Filter, but maybe there is a better solution?

  IF(thisRow.[Column 2],[Values 2].[Column 2] ,""),
  IF(thisRow.[Column 3],[Values 2].[Column 3] ,""),
  IF(thisRow.[Column 4],[Values 2].[Column 4] ,""),
  IF(thisRow.[Column 5],[Values 2].[Column 5] ,"")).Filter(CurrentValue!=""))
1 Like

Hi @Nurlan_A ,
Happy you found out yourself a solution!

I don’t know if this can fit your use case but I’d model data in a different way so that you don’t have to rely on static formula references.

Have a look a this example:

(Please, don’t consider the button for data generation, they are just a convenient way to fill up tables properly).

By building the tables this way you can “mimic” a matrix (by grouping both horizontally and vertically), but still have the dynamic reference of rows; therefore adding values, will be reflected in the combination generation.

Let me know if this helps.



Hi @Federico.Stefanato,
Thank you for taking your time and suggesting such an interesting solution. Unfortunately, this does not quite suit me because of the design and complexity, but I think that someday it will come in handy.



This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.