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

DoubleJ's avatar
DoubleJ
Solution Supplier
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

  • tanyad's avatar
    tanyad
    Advocate III
    9 years ago

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

    • DoubleJ's avatar
      DoubleJ
      Solution Supplier
      9 years ago

      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