Custom Colum with condition

[Anonymous]

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"

[v-qiuyu-msft · Accepted solution]

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

[v-qiuyu-msft · Accepted solution]

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 · Accepted solution]

DT_LAN I select by a filter thatI put in the project

look