Combine multiple columns - number to string

[tanyad]

Hi,  I am trying to combine multiple number columns (with IF statements).

Example

IF ([Apples]=1,"Apples","")

Customer	Apples	Grapes	Oranges	Required Results Column
A	1	1	0	Apples | Grapes
B	1	1	1	Apples | Grapes |Oranges
C	0	1	1	Grapes|Oranges

I am using the existing fields elsewhere so don't want to convert them to strings so require them to stay as number columns.

I have tried CONCATENATEX but failed.

Any help would VERY much be appreciated!

[Vvelarde]

@tanyad

Hi, this a solution but the problem is the code growth depends or yor number of columns

FruitsByCustomer =
IF ( Customers[Apples] = 1, "Apples" )
    & IF ( Customers[Grapes] = 1, IF ( Customers[Apples] = 1; "|Grapes","Grapes" ) )
    & IF (
        Customers[Oranges] = 1,
        IF (
            OR ( Customers[Apples] = 1; Customers[Grapes] = 1 ),
            "|Oranges",
            "Oranges"
        )
    )

[DoubleJ · Accepted solution]

Hi

You could use thise formula in a new column

Result = IF(Customers[Apples] = 1; "Apples |";"") & IF(Customers[Grapes] =1;" Grapes |";"")  &IF(Customers[Oranges] = 1; " Oranges |";"") 

This is not a handy solution if you have more column values to check.  Would it be a possible to unpivot the table in Power Query giving it a structure like this?

Customer   |   Fruit
============

Cust. A  |  Apples

Cust. A  |  Grapes

Cust. B  |  Apples

Cust. B  |  Grapes

Cust. B  |  Oranges

Cust. C  |  Grapes

Cust. C  |  Oranges

I hope this helps!

JJ

[tanyad]

@DoubleJBingo!, thank you so much.  I was nearly there with that, but the brain just went cold.  Thank you very much

[DoubleJ]

@tanyad you are welcome

by the way, to remove the trailing "|" you could add another column with this code:

Fruits = LEFT(Customers[Result];LEN(Customers[Result])-1)

JJ