Forum Discussion

sammyboi's avatar
sammyboi
New Member
3 years ago

Creating a conditional column with null value

Hi there!


I am trying to create a conditional column to return values based on the following logic:

 

if column a is null, then column a should equal to column b

if column a and column b are both null, then column a should equal column c

all else should equal to column a

 

Could you please advise how I should phrase this using the Conditonal Column function?

Need Help

7 Replies

  • grantsamborn's avatar
    grantsamborn
    Solution Sage
    3 years ago

    Based on your description:
    if column a is null, then column a should equal to column b
    if column a and column b are both null, then column a should equal column c
    all else should equal to column a

     

    I'm guessing that you mean something like this. If not, I don't understand.
    if column a is null, then RESULT should equal column b
    if column a and column b are both null, then RESULT should equal column c
    else RESULT should equal to column a

     

    If I do understand, is this what you are looking for?

     

    z=
IF(
	ISBLANK( [ColumnA] ),
	IF(
		ISBLANK( [ColumnB] ),
		[ColumnC],
		[ColumnB]
	),
	[ColumnA]
)

     

     

    (If the difference is between NULLs and BLANKs, maybe PowerQuery would be the place to make the substitution.)

  • MahyarTF's avatar
    MahyarTF
    Memorable Member
    3 years ago

    Hi,

    In addition to the grantsamborn Post, you could create the below column and use it in your visual :

    ResultCol =
                SWITCH(Sheet186[Column],
                       BLANK(), SWITCH(Sheet186[A],
                                       BLANK(), SWITCH(Sheet186[B],
                                                       BLANK(), Sheet186[C], Sheet186[B]),
                                       Sheet186[A]),
                      Sheet186[Column])
    Appreciate your Kudos
    • grantsamborn's avatar
      grantsamborn
      Solution Sage
      3 years ago

      A couple questions/comments:

      1 - When creating a calculated column, the table name isn't necessary.
      2 - I'm not sure where you came up with Sheet186[Column].
      3 - While I realize SWITCH has its places, please explain how in a simple case like this it easier to read than using IF.

       

      grantsamborn =
IF(
    ISBLANK( [ColumnA] ),
    IF(
        ISBLANK( [ColumnB] ),
        [ColumnC],
        [ColumnB]
    ),
    [ColumnA]
)


MahyarTF =
SWITCH(
    Sheet186[Column],
    BLANK(),
        SWITCH(
            Sheet186[A],
            BLANK(),
                SWITCH(
                    Sheet186[B],
                    BLANK(), Sheet186[C],
                    Sheet186[B]
                ),
            Sheet186[A]
        ),
    Sheet186[Column]
)
      • MahyarTF's avatar
        MahyarTF
        Memorable Member
        3 years ago

        Hi,

        1- Yes, You are right, for the calc column not need to bring the table name (as in the sample file, I have a multi same columns, I wrote the table name and just copy the code)

        2- my Table structure is like below (I bring one more column ) :

        3- As you know there is more than one solution. I mentioned that your answer I good, but sometimes the codes' performance should be considered.

        As you see in below image I run the codes for comparing the performance and it should be not same (should check in real data by user to find the best solution :

         

        Thanks for letting me know