Forum Discussion
Need M code for below Excel Query
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"))
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.pbixfile 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.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
7 Replies
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 | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated
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 adviseHi 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.pbixfile 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.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
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
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.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.
