Need M code for below Excel Query

[KrishnaKawade]

Hi Community,

Will be a great help if someone will help on below Excel Query converting to M code.
I tried multiple ways but getting Cyclic Reference error. 

Need to get duplicates and show Yes/No if it is > 1

=IF([@[Business Type]]="Tech", "-", IF(COUNTIF([Laptops], [@[Laptops]])>1, "Yes", "No"))

[v-priyankata · Accepted solution]

Hi @KrishnaKawade 
Thank you for reaching out to the Microsoft fabric community forum. thanks @pankajnamekar25 for your inputs.
 
I have reproduced your scenario in Power BI. Please find attached the .pbix file for your reference.


If this post helps, then please give us ‘Kudos’ and consider "Accept it as a solution" to help the other members find it more quickly. 
 
Thanks.

[v-priyankata]

Hi @KrishnaKawade 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @KrishnaKawade 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[ryan_mayu · Accepted solution]

@KrishnaKawade 

you can try this 

= Table.AddColumn(Source, "Custom", each if [Business Type] = "Tech" then "-" else if Table.RowCount(Table.SelectRows(Source,(x)=>x[SN]=[SN]))>1 then "Y" else "N")

if this does not work, pls provide some sample data

[v-priyankata · Accepted solution]

Hi @KrishnaKawade 
Thank you for reaching out to the Microsoft fabric community forum. thanks @pankajnamekar25 for your inputs.
 
I have reproduced your scenario in Power BI. Please find attached the .pbix file for your reference.


If this post helps, then please give us ‘Kudos’ and consider "Accept it as a solution" to help the other members find it more quickly. 
 
Thanks.

[v-priyankata]

Hi @KrishnaKawade 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you

[KrishnaKawade]

Thank you @pankajnamekar25. 
Changed List.Count to Table.RowCount, but the result is showing "Yes" for the non-duplicated records as well. 

e.g. Column Laptop having Serial numbers, if this number gets duplicated or show more than 1, then should show "Yes" else "No". Even if it is occuring only 1, still giving me "Yes" as a result.

Please advise

[pankajnamekar25]

Hello @KrishnaKawade 

You can create a cutome column using below M code

New Column =if [Business Type] = "Tech" then "-"

else if List.Count(Table.SelectRows(#"Previous Step", each [Laptops] = [Laptops])) > 1 then "Yes"

else "No"

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated