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.
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!
Do this in Power Query as you are importing the data.
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. 🙂
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
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.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |