Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional and/or List. Contain more than 2 variables

In in my dataset I have 5 columns that contain values between 0-9 that I need to recode into one column. 

 

What I need to do:

IF (V1 or V2 or V3 or V4)  have one of these values (2,3,4) then NewVar = X,

IF (V1-V4) has one of these values (5,6,7) then NewVar = Y, else NewVar = W. 

 

I was able to use list.contain below 

Table.AddColumn(table, "NewVar", each
if List.Contains ({2,3,4}, [V1], [V2])
then "X"
else
if List.Contains ({5,6,7}, [V3], [V4])
then "Y"

 

But if I try to have more than 2 varibales if List.Contains ({5,6,7}, [V1], [V2] [V3], [V4]), I get error that the argument can only have two functions. 

 

 

Is there a way to use list.contain with more than 2 variables? Is there a simple way to recode these columns, thanks!

General Comment
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI Anonymous,

    I think List.Intersect function should be more suitable for your scenario, you can try to use following code if helps:

        #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Custom",
        each
            if List.IsEmpty(List.Intersect({{2, 3, 4}, {[V1], [V2], [V3], [V4]}})) = false then
                "X"
            else if List.IsEmpty(List.Intersect({{5, 6, 7}, {[V1], [V2], [V3], [V4]}})) = false then
                "Y"
            else
                "W"
    )

    List.Intersect - PowerQuery M | Microsoft Learn

    Full query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XVZLssMwCLuL113Ef3OWTu9/jSYGgZxFZho9jCUQ5H2/KadPkvsp99Pvp6bf57vfhiHF0bp/KTr2uQdt969mcZrpQXESWTQWb90yaey0k8uzPOjzNg3V50GfuGx5lcuD5ovI6p83jMhhufS+XOxV7JSmztXVguWG2yEDxcjdrhLTnxXGVcNoW/Qk1RK5IRkkVXoWUrg8d7ksevnVG86UVyK6UFubqyzVDoOkMimNpEQTSqe8WrYNDyKYA56WIPq8YfCFUmMixgL1NpddxGJ5LytUTme0YdhuHUwqegl3GtxeBrQrYR2waQpDIZgo7zrJJ9lVVhgVlVSzVfGrOHe7qLDTVTZ0piSuYIOvzwq2SlcOV9niVf9kSTolkWAClTCEjTUOoxXqqrZSTEL2CjZJYfns4vvlJeJx7YBgf4suFB1Lo6MF86h3Z49Qbti9Jh7ADnJosZaqTypT8zZ0FBUktd4dvcxel73V0MvlJDcMKSiVRUMlHs090AIU2KJ5nMLIA73ENGgvB+byLOwI0bx3ByoniVfBgJSY+72lLzJxrJl5bmOHCyVoTnCiK+Ow5mzp8/qQbBi2iRMbxuHsSjc8U+wpyr0oOj5mE4OHnmqSdVEfuxtixVWozYYLXVe9l6tS3rDmaiSlu5zV7bp5JhnUmea819uxWu/FvYxv2jrrAfsIviT9iBY4CtZU8RJbh9eMYC5jKjYMYudmk/dcGjxeSbQNwl+SsI/wPwVhNoHKejDJF2QON8vv9wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, V1 = _t, V2 = _t, V3 = _t, V4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"V1", Int64.Type}, {"V2", Int64.Type}, {"V3", Int64.Type}, {"V4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Custom",
        each
            if List.IsEmpty(List.Intersect({{2, 3, 4}, {[V1], [V2], [V3], [V4]}})) = false then
                "X"
            else if List.IsEmpty(List.Intersect({{5, 6, 7}, {[V1], [V2], [V3], [V4]}})) = false then
                "Y"
            else
                "W"
    )
in
    #"Added Custom"

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI Anonymous,

    I think List.Intersect function should be more suitable for your scenario, you can try to use following code if helps:

        #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Custom",
        each
            if List.IsEmpty(List.Intersect({{2, 3, 4}, {[V1], [V2], [V3], [V4]}})) = false then
                "X"
            else if List.IsEmpty(List.Intersect({{5, 6, 7}, {[V1], [V2], [V3], [V4]}})) = false then
                "Y"
            else
                "W"
    )

    List.Intersect - PowerQuery M | Microsoft Learn

    Full query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XVZLssMwCLuL113Ef3OWTu9/jSYGgZxFZho9jCUQ5H2/KadPkvsp99Pvp6bf57vfhiHF0bp/KTr2uQdt969mcZrpQXESWTQWb90yaey0k8uzPOjzNg3V50GfuGx5lcuD5ovI6p83jMhhufS+XOxV7JSmztXVguWG2yEDxcjdrhLTnxXGVcNoW/Qk1RK5IRkkVXoWUrg8d7ksevnVG86UVyK6UFubqyzVDoOkMimNpEQTSqe8WrYNDyKYA56WIPq8YfCFUmMixgL1NpddxGJ5LytUTme0YdhuHUwqegl3GtxeBrQrYR2waQpDIZgo7zrJJ9lVVhgVlVSzVfGrOHe7qLDTVTZ0piSuYIOvzwq2SlcOV9niVf9kSTolkWAClTCEjTUOoxXqqrZSTEL2CjZJYfns4vvlJeJx7YBgf4suFB1Lo6MF86h3Z49Qbti9Jh7ADnJosZaqTypT8zZ0FBUktd4dvcxel73V0MvlJDcMKSiVRUMlHs090AIU2KJ5nMLIA73ENGgvB+byLOwI0bx3ByoniVfBgJSY+72lLzJxrJl5bmOHCyVoTnCiK+Ow5mzp8/qQbBi2iRMbxuHsSjc8U+wpyr0oOj5mE4OHnmqSdVEfuxtixVWozYYLXVe9l6tS3rDmaiSlu5zV7bp5JhnUmea819uxWu/FvYxv2jrrAfsIviT9iBY4CtZU8RJbh9eMYC5jKjYMYudmk/dcGjxeSbQNwl+SsI/wPwVhNoHKejDJF2QON8vv9wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, V1 = _t, V2 = _t, V3 = _t, V4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"V1", Int64.Type}, {"V2", Int64.Type}, {"V3", Int64.Type}, {"V4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Custom",
        each
            if List.IsEmpty(List.Intersect({{2, 3, 4}, {[V1], [V2], [V3], [V4]}})) = false then
                "X"
            else if List.IsEmpty(List.Intersect({{5, 6, 7}, {[V1], [V2], [V3], [V4]}})) = false then
                "Y"
            else
                "W"
    )
in
    #"Added Custom"

    Regards,

    Xiaoxin Sheng