Forum Discussion
New Table with Distinct and Min values
Hello,
I'm just getting familiar with Power BI and could really use some help from the community.
I'm trying to clean up some inaccuracies in a source file so that I can model my data correctly.
I've imported my source file into Power BI and I'm seeing the following scenario:
Source:
| Part | Quantity |
| A | 1 |
| A | 3 |
| B | 2 |
| C | 5 |
I'm trying to create a new table in Power BI that would return distinct [Part] and Min [Quantity].
Can someone help me with the DAX for this?
I thought it would be:
NewTable = DISTINCT([Part]), MIN[Quantity])
The output should be as follows:
| Part | Quantity |
| A | 1 |
| B | 2 |
| C | 5 |
Any help is appreciated!
3 Replies
Do this in Power Query as you are importing the data.
- Select the [Part] field then Group By on the Home tab.
- for the New Column section, call it Quantity, then Min for the operation, and select the Quantity column.
99.9 times out of 100, Power Query is the place to do any data modeling, which includes cleanup of source data. Maybe 99.99 times out of 100. :-)
Group ByEnd result
edhans Your solution did help me create a table that displays the values that I was looking for. Thank you for your help!
However, when I go to join my now clean table with only unique values to a another table that I has duplicates by design Power BI is only allowing me to use a Many to Many relationship. This is preventing me from creating a valid model. Any idea why its forcing this Many to Many relationship?
I believe my problem is with null values. There are situations where my clean duplicate free table doesn't have a record thats in my other table that does have multiple examples of part. I'm inferring that if there are more than 1 of these situations Power BI is trying to join multiple instances of null to my other table, therefore, creating a many to many scenario.
Can someone confirm my understanding is correct? Also does someone know a way to prevent this from happening?
Does your table with unique values have a blank? You should filter that out in Power Query to exclude null as the final step. You can create a one-to-many if the many has nulls. That is very common.
