New Column based on values of 2 other columns

[Anonymous]

I have a data set where one of the columns may have either a single number, or two numbers within it (ex: 1, 1, 2, 21, 12, 2.) How can I append a second column that would replace all the 1 with "first", 2 with "second", and combined values as "first and second"? So for 1, 1, 2, 21, 12, I would want the column to show "first, first, second, first and second, first and second."

The actual data will have a lot more numbers, as well as letters mixed in, but the maximum number of characters within a cell would never exceed two.

[Ashish_Mathur]

Hi,

Try this

=IF([column1]="1","First",IF([column1]="2","Second","First and Second"))

Hope this helps.

[Greg_Deckler]

Look at using a SWITCH statement.

[Anonymous]

Any suggestions on how to appreach double digit values? I need 21 and 12 to both return "first and second", in the same order.

I was able to solve everything except for the ordering, so this code won't return the same result for me wheere there's 21 and 12.

Here's what I have:

_IL MAP = SWITCH(
    LEN('open'[WOIssueLevel]), 
    // handle single issue level
    1, LOOKUPVALUE('Issue Level Legend'[Description], 'Issue Level Legend'[Issue Level], 'open'[WOIssueLevel]),
    // handle two issue levels
    2, 
        // first level
        LOOKUPVALUE('Issue Level Legend'[Description], 'Issue Level Legend'[Issue Level], LEFT('open'[WOIssueLevel], 1)) & " + " &
        // second level
        LOOKUPVALUE('Issue Level Legend'[Description], 'Issue Level Legend'[Issue Level], RIGHT('open'[WOIssueLevel], 1))
) 

[v-huizhn-msft]

Hi @Anonymous,

Based on my understanding, the solution @Ashish_Mathur posted is right if you just want to get Colum2 based on Column1. While your formula is complex, maybe my understanding is uncorrect. Please share your sample structure, becuase there are 'Issue Level Legend'[Description], 'Issue Level Legend'[Issue Level], 'open'[WOIssueLevel] in your formula, so that we can post detailed solution which is close to what you want.

Best Regards,
Angelia

[Anonymous]

What he posted wouldn't work with real data. As I mentioned in my first post, there can be many number-to-description rows, so writing a lenghty spagetti IF code for each possible scenario is not practical. My most recent code that I posted just above his suggestion solved my problem, but with one pitfall, I still need to figure out a way to have transposed numbers (ex: 12 and 21) to generate the same description.

[Ashish_Mathur]

Hi @Anonymous,

I do not understand your comment.  From your original post, i understand that there can be only 3 types of numbers in that column - 1,2 and any two digit number.  If that be the case, then my formula will work.  If not, then please share some scenarios where my formula would fail.

[Anonymous]

@ashish_Mathur It can have up to 35^2 - 35 possibilities, so a nested IF statement would not be optimal.

The actual data will have a lot more numbers, as well as letters mixed in, 
but the maximum number of characters within a cell would never exceed two.

@v-huizhn-msft the formula is fine, with the exception of not being able to account for transposed numbers. Here's a sample file: https://1drv.ms/u/s!AkBC9S_qYIPlge9iMkPKLVD6fhNZQQ

[Ashish_Mathur]

Hi,

This calculated column formula will work

=if(LEN([code])=1,LOOKUPVALUE(legend[desc],legend[index],LEFT([code],1)),LOOKUPVALUE(legend[desc],legend[index],LEFT([code],1))&"+"&LOOKUPVALUE(legend[desc],legend[index],RIGHT([code],1)))

Hope this helps.

[Anonymous]

That's a copy of my code with an IF instead of SWITCH. It doesn't solve transposed codes.

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Please add an index column by clicking "add column"->index column, then create a calculated column using the formula below, you will get expected result.

result =
IF (
    RIGHT ( Table1[code], 1 ) & LEFT ( Table1[code], 1 )
        = LOOKUPVALUE ( Table1[code], Table1[Index], Table1[Index] - 1 ),
    LOOKUPVALUE ( Table1[_IL MAP], Table1[Index], Table1[Index] - 1 ),
    Table1[_IL MAP]
)

Best Regards,
Angelia

[v-huizhn-msft]

Hi @Anonymous,

Yes, but I think there is no problem of the formula you posted, could you please share the result you got using the formula? For your real data, 

Best Regards,
Angelia