Forum Discussion
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 | 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!
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
4 Replies
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
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" ) )
