Trying to Creat a Custom Column in M

[FarmaVip]

Hi Guys,

I want to creat a custom column in power query similar to this DAX formula, but using M:

CALCULATE(   DISTINCTCOUNT([CODIGO]),    ALLEXCEPT(Table,[CNPEM])

Data info:
CODIGO is like a product ID
CNPEM is a Group of products.  
4 different CODIGO values can belong to the same CNPEM

I already try this to use this M formula:

Table.AddColumn(#"Inserted Range", "Personalizado", each
                           List.Distinct(
                                    Table.SelectRows(#"Inserted Range", each [CNPEM] = _ [CNPEM])[CODIGO]))

But this M formula creates a custom column with a list of all CODIGO values (even those who don't belong to the same CNPEM) in the table for each CNPEM, my final goal is to get all CODIGO values that belong to each CNPEM as a list. 

Hope you can help me 🙂

[FarmaVip]

If it performs better I will use it in my other premium Workspace, than you for the help

[edhans · Accepted solution]

I think you might want to look at doing a conditional merge. I have written a blog about that here. Conditional Merge in Power Query — ehansalytics

[FarmaVip]

I wanted to avoid because is a pro workspace I did not wanted to used merge tables.

Creating a copy of my query and then grouping (CNPEM) then merge with my original table is the only way to do that?

[edhans]

I am not sure what a pro workspace has to do with merging queries. 

I didn't say a conditional merge was the only way to do it, but it will work, and I think will perform better than a Table.SelectRows calcualted at every record wrapped with a List.Distinct.

Either way, if you want real help, you need to provide data. I am thinking you will need a varialbe to trap the value you want to be part of the condition, but I need to see data to do code.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.