Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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.
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 @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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"
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
79 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |