Forum Discussion

eeviste's avatar
eeviste
New Member
3 years ago
Solved

Compiling a new column value from multiple columns if only one of them has a value per row?

Hi all!   I am trying to create a new text column in Power BI desktop from four separate text columns within the same table. For each data row, only one of these four columns will have a text valu...
  • amitchandak's avatar
    amitchandak
    3 years ago

    eeviste , Try one of the two way to create a new columns

     

    coalease([AgeGroup1],[UnknownAge],[UnknownAdult],[UnknownChild])

     

     

    Switch(True() ,
    not(isblank([AgeGroup1])) && [AgeGroup1]<>"", [AgeGroup1],
    not(isblank([UnknownAge])) && [UnknownAge]<>"", [UnknownAge],
    not(isblank([UnknownAdult])) && [UnknownAdult]<>"", [UnknownAdult],
    not(isblank([UnknownChild])) && [UnknownChild]<>"", [UnknownChild])

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

eeviste , Try one of the two way to create a new columns

 

coalease([AgeGroup1],[UnknownAge],[UnknownAdult],[UnknownChild])

 

 

Switch(True() ,
not(isblank([AgeGroup1])) && [AgeGroup1]<>"", [AgeGroup1],
not(isblank([UnknownAge])) && [UnknownAge]<>"", [UnknownAge],
not(isblank([UnknownAdult])) && [UnknownAdult]<>"", [UnknownAdult],
not(isblank([UnknownChild])) && [UnknownChild]<>"", [UnknownChild])

  • eeviste's avatar
    eeviste
    New Member
    3 years ago

    Thanks heaps for your help, amitchandak ! 👍

     

    COALESCE did not work for me, but I got the SWITCH combo to work, thank you. In it though, for reasons I don't fully understand, I needed to swap around the variables and have AgeGroup1 last... Not sure why, but it works like this, so I am happy! 😄

     

    This is my code in the end, with a little addition (that I didn't include in the original request for help, for clarity) where I wanted to limit the rows that get the combined value to humans (another variable in the same table), and others would be "not human". 

     

    AgeGroup7 = IF( data[Subject] = "Human",
    SWITCH(
    TRUE(),
    NOT(ISBLANK(data[UnknownAge])) && data[UnknownAge] <> "", data[UnknownAge],
    NOT(ISBLANK(data[UnknownAdult])) && data[UnknownAdult] <> "", data[UnknownAdult],
    NOT(ISBLANK(data[UnknownChild])) && data[UnknownChild] <> "", data[UnknownChild],
    NOT(ISBLANK(data[AgeGroup1])) && data[AgeGroup1] <> "", data[AgeGroup1],
    "not human"
    ), "not human")

     

    I think I will mark yours as the solution, even though there were some tweaks that were needed. It still cracked it for me. Thanks heaps again for your help! 👍