Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Custom Colum with condition

I have to create a custom column with this condition 

 

SELECT SUM(VALOR_OBJ) FROM PROJ_DOC_CUSTO WHERE DT_LAN LIKE '%201709%' AND (TEXTO NOT LIKE '%ORD 9%' AND TEXTO NOT LIKE '%ORD 6%' AND TEXTO NOT LIKE '%Reclassificação%' AND (TEXTO <> '' OR (TEXTO_CAB LIKE '%tarifa%')) AND TEXTO NOT LIKE '%TRANSITORIA%')

 

I try it, but did not work, my logic must be wrong

 

if
List.AllTrue({
not Text.Contains([TEXTO], "ORD 9"),
not Text.Contains([TEXTO], "ORD 6"),
not Text.Contains([TEXTO], "Reclassificação"),
not Text.Contains([TEXTO], " "),
List.AnyTrue({
[TEXTO] <> "",
Text.Contains([TEXTO_CAB], "tarifa")
}),
not Text.Contains([TEXTO], "TRANSITORIA")
})
then "True"
else "False"

  • Hi Anonymous,

     

    You miss the condition for DT_LAN column, please modify the column below: 

     

    if
    List.AllTrue({ Text.Contains([DT_LAN],"201709"),
    not Text.Contains([TEXTO], "ORD 9"),
    not Text.Contains([TEXTO], "ORD 6"),
    not Text.Contains([TEXTO], "Reclassificação"),
    not Text.Contains([TEXTO], " "),
    List.AnyTrue({
    [TEXTO] <> "",
    Text.Contains([TEXTO_CAB], "tarifa")
    }),
    not Text.Contains([TEXTO], "TRANSITORIA")
    })
    then "True"
    else "False"

     

     

    Best Regards,
    Qiuyun Yu

  • Anonymous's avatar
    Anonymous
    8 years ago

    DT_LAN I select by a filter thatI put in the project

     

    look

     

2 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support

    Hi Anonymous,

     

    You miss the condition for DT_LAN column, please modify the column below: 

     

    if
    List.AllTrue({ Text.Contains([DT_LAN],"201709"),
    not Text.Contains([TEXTO], "ORD 9"),
    not Text.Contains([TEXTO], "ORD 6"),
    not Text.Contains([TEXTO], "Reclassificação"),
    not Text.Contains([TEXTO], " "),
    List.AnyTrue({
    [TEXTO] <> "",
    Text.Contains([TEXTO_CAB], "tarifa")
    }),
    not Text.Contains([TEXTO], "TRANSITORIA")
    })
    then "True"
    else "False"

     

     

    Best Regards,
    Qiuyun Yu

    • Anonymous's avatar
      Anonymous
      Not applicable

      DT_LAN I select by a filter thatI put in the project

       

      look