Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.