Countif - Mcode

Power Query
1

Dear DNA Team,

I have a quick question, I tried to search in google but could’t find anything.

Are you aware of any m-code pattern for coutnif statement?

For example,

In column A you have multiple and unique transactions I would like to count if the transaction per row repeats using the m code . The countif I need to wrap up in the if statement if counit is greater than 2 then give me 100 else 0 .

This is simple in the excel:

=IF(COUNTIF($A$3:$A$11,A3)>2,100,0)
image

I will be grateful for any help or advice.

Thank you,

Matty

2

Hi @Matty,

Give this a go, just paste it into a new blank query,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0MFCK1cHNTgGyIawkLKxkJJVY2bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    AddCountIf = Table.AddColumn(Source, "CountIf",  (OT)=> if List.Count( List.Select( Source[Column1], each Text.Contains( _, OT[Column1] ))) >2 then 100 else 0 )
in
    AddCountIf

I hope this is helpful

2 Likes
3

Hi @Matty, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

4

Hi @Matty, we’ve noticed that no response has been received from you since July 3. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @Matty, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

6

Thank you @Melissa for your help!

Apologies for coming soo late but I was without internet :smiley:

1 Like