PowerQuery - Conditional Column Nested IF AND

Hi Anonymous ,

 

Don't really understand what you mean that the condittional formatting can only have one condition since you can use the rules that have more than one option, however making it in M languague you need to add a new column with the following code:

 

 

 

if List.Contains ({ "Customer A", "Customer B", "Customer C"}, [Customer]) then 

if [Transactions] < 15   then "Less than 15 transactions"
else   "Over than 15 transactions"
else 

if [Transactions] < 30  and List.Contains ({ "Customer D", "Customer E"}, [Customer]) then "Less than 30 transactions"

else "Over than 30 transactions"

 

 

Regards,

MFelix 

Hi barbarcmv, 

You could try below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1ScFTSUTI0UIrVQRJzAomZoIo5g8RMUcVcgGImaGKuQDEjS6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Transactions = _t]),
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [Customer] = "Customer D" then 2 else if [Customer] = "Customer E" then 2 else 1, type any),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Transactions", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Custom"}, {{"SUM", each List.Sum([Transactions]), type number}, {"MAX", each List.Max([Transactions]), type number}, {"ALL", each _, type table [Customer=text, Transactions=number, Custom=number]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Customer", "Transactions"}, {"ALL.Customer", "ALL.Transactions"}),
    #"Added Custom" = Table.AddColumn(#"Expanded ALL", "Custom.1", each if [MAX]<15 and [Custom]=1 then "Less than 15 transactions"  else if [SUM]>=15 and [Custom]=1 then "Over than 15 transactions" else if [MAX]<30 and [Custom]=2 then "Less than 30 transactions" else " Over than 30 transactions")
in
    #"Added Custom"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi barbaracmv,

If there is null in numberic column, and when you use < or >(compare value), I think you need to replace null to 0 at  first, then you could try to use above M code

= Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"amount"})

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.