Return text for Logical Test for more than 3 arguments

[mrhuguru]

Hi everyone,

I work in a company that rents photo boxes along with other marketing services.Here is my problem :

We have about 6 or 7 box types, that we rent for events.Each row in my data table corresponds to an event, and for each event I have the number of boxes rented expressed in columns such as :

Event number n Box1 nBox2 nBox3 nBox4 ....
1 3 0 0 0
2 0 5 0 0

I am trying to do a "Box type" column, in order to be able to filter my results per box type.I tried using the IF function as following :

IF([n Box1]>0,"Box1",IF([n Box2)>0,"Box2" .... and so on but as the number of arguments is limited to 3 for the IF function I am stucked.Does anyone has a clue on how to do it ? Thank you,Hugo

 

[Matt Allington]

You should use power query to unpivot the data into a single value column and a box Id column.

This should help. http://youtu.be/y0goNLt7GKY

 

[mrhuguru]

Hey Matt, it worked ! thank you so much,
Hugo