Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi there
I have data in the format given below.
Sample ID | Model | S | K |
BH00345 | mm23 | 0.029 | 0.443 |
BH00345 | mm15 | 0.022 | 0.018 |
BH00345 | mm14 | 0.047 | 1.228 |
BH00345 | gm23 | 0.04 | 0.669 |
BH00345 | gm15 | 0.057 | 2.709 |
BH00345 | gm14 | 0.042 | 0.883 |
BH00746 | mm24 | 0.113 | 0.32 |
BH00746 | mm23 | 0.046 | 0.11 |
BH00746 | mm15 | 0.019 | 0.658 |
BH00746 | gm24 | 0.023 | 0.129 |
BH00746 | gm23 | 0.019 | 0.127 |
BH00746 | gm15 | 0.144 | 0.031 |
BH00964 | mm24 | 0.175 | 0.519 |
BH00964 | mm23 | 0.141 | 0.513 |
BH00964 | gm24 | 0.171 | 0.403 |
BH00964 | gm23 | 0.167 | 0.031 |
BH00583 | mm23 | 0.039 | 0.029 |
BH00583 | gm23 | 0.064 | 0.115 |
Samples have data for both mm and gm model types and for several iterations of each model as indicated by the model suffix number.
Newer samples do not contain data for all older models.
Older samples do not contain data for all newer models.
I want to condense the table so that the Sample ID column contains unique values, and each row contains data from the most recent mm model.
eg. for the above table, I would like the output to give
Sample ID | Model | S | K |
BH00345 | mm23 | 0.029 | 0.443 |
BH00746 | mm24 | 0.113 | 0.32 |
BH00964 | mm24 | 0.175 | 0.519 |
BH00583 | mm23 | 0.039 | 0.029 |
Full data set contains 3554 rows and 747 unique sample ID codes
Thanks for your help
<edit. sorry for the horrendous table!! I cant seem to change the width of the columns>
Solved! Go to Solution.
Hi @mef47 ,
We can use the Power Query Editor to meet your requirement.
1. Filter the Model column, delete the rows that contain “gm”.
2. We sort the sample ID Ascending, and sort the Model Descending.
3. Then we need to Group the Sample ID column.
4. And we can add a custom column to add an index column. Each group has an index column.
5. Delete the first two columns. And expand the last column.
6. At last we need to filter the Rank column equal 1, then delete the rank column.
The result table just has four rows.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @mef47 ,
We can use the Power Query Editor to meet your requirement.
1. Filter the Model column, delete the rows that contain “gm”.
2. We sort the sample ID Ascending, and sort the Model Descending.
3. Then we need to Group the Sample ID column.
4. And we can add a custom column to add an index column. Each group has an index column.
5. Delete the first two columns. And expand the last column.
6. At last we need to filter the Rank column equal 1, then delete the rank column.
The result table just has four rows.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @mef47 -
Presuming that the "latest" model will be the MM with the highest number, we can create the following 3 measures:
Latest Model = CALCULATE(MAX(Samples[Model]))
Latest K =
VAR __model = [Latest Model]
VAR __sample =
SELECTEDVALUE ( Samples[Sample ID] )
RETURN
LOOKUPVALUE (
Samples[K],
Samples[Model], __model,
Samples[Sample ID], __sample
)
Latest S =
VAR __model = [Latest Model]
VAR __sample =
SELECTEDVALUE ( Samples[Sample ID] )
RETURN
LOOKUPVALUE (
Samples[S],
Samples[Model], __model,
Samples[Sample ID], __sample
)
Create a table visual, put Sample ID as the first value, then the 3 measures:
Hope this helps,
David
Hi David
Not quite what I was after but helpful enough to get me to where I wanted to go.
The ALLEXCEPT function what what I was after
I ended up going with:
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
69 | |
57 | |
37 | |
36 |
User | Count |
---|---|
83 | |
67 | |
62 | |
46 | |
45 |