Forum Discussion

tanyad's avatar
tanyad
Advocate III
9 years ago
Solved

Combine multiple columns - number to string

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 ...
Need Help
  • DoubleJ's avatar
    DoubleJ
    9 years ago

    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

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

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"
        )
    )