Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIf it performs better I will use it in my other premium Workspace, than you for the help
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting