PowerQuery - Conditional Column Nested IF AND

[Anonymous]

Hi.

Looking for some guidance in writing this M language in Power Query.

For these 2 columns (Customer and Transactions):

This is the context:

If customer A and customer B and customer C have less than 15 transactions, create a text saying, "Less than 15 transactions" but if customer A + Customer B + customer C have more than 15 transactions, create a text saying "Over than 15 transactions"

if customer D and customer E have less than 30 transactions, create a text saying, "Less than 30 transactions" but if customer D + Customer E have more than 30 transactions, create a text saying "Over than 30 transactions

I know this can be created in conditional formatting, but only for one condition so I need to write it in M and I am not able to.

Hope I am clear enough.

Thanks 🙂

Barbara 

[MFelix · Accepted solution]

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 

[dax · Accepted solution]

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.

[dax · Accepted solution]

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.

[MFelix · Accepted solution]

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 

[Anonymous]

Hi @MFelix,

Thanks so much for your response.

It worked perfectly!

However after running it, I saw I have other customer grouping (Customer F and Customer G) and other scenario that appears are "null" values in product and transactions I would need to add a text as "null".

Can you help me?

I try to create as this but it didn't work:

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"

else
if [Transactions] < 50 and List.Contains ({ "Customer F", "Customer G"}, [Customer]) then "Less than 50 transactions"
else "Over than 50 transactions"

else

if [Transactions] = null then "Exclude from List" else "NA"

[dax · Accepted solution]

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.