cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

PowerQuery - Conditional Column Nested IF AND

Hi.

 

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

For these 2 columns (Customer and Transactions):

 

customer transations.PNG

 

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 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: PowerQuery - Conditional Column Nested IF AND

Hi @barbaracmv ,

 

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 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Community Support
Community Support

Re: PowerQuery - Conditional Column Nested IF AND

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.

View solution in original post

Highlighted
Community Support
Community Support

Re: PowerQuery - Conditional Column Nested IF AND

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.

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: PowerQuery - Conditional Column Nested IF AND

Hi @barbaracmv ,

 

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 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Community Support
Community Support

Re: PowerQuery - Conditional Column Nested IF AND

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.

View solution in original post

Highlighted
Frequent Visitor

Re: PowerQuery - Conditional Column Nested IF AND

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"

Highlighted
Community Support
Community Support

Re: PowerQuery - Conditional Column Nested IF AND

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.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors