Forum Discussion
Removing rows where there are duplicate fields
- 1 year ago
Hi 83dons,
Thank you for reaching out to the Microsoft fabric community forum.I have reproduced your scenario in Power BI Desktop and achieved the expected output as per your requirement:
- Only keep the "Current" status row when both "Current" and "Left" exist for the same StaffID and ROLEnumber.
- Keep "Left" status rows only if there's no corresponding "Current" status for the same combination.
Expected Output (Achieved):
I used Power Query (Transform Data) in Power BI and applied logic to:
- Group by StaffID and ROLEnumber
- Check if "Current" exists
- If yes, keep only "Current"; else, keep all
For your reference, I’ve attached the .pbix file so you can explore the full solution and steps applied.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
Hi 83dons,
Thank you for reaching out to the Microsoft fabric community forum.
I have reproduced your scenario in Power BI Desktop and achieved the expected output as per your requirement:
- Only keep the "Current" status row when both "Current" and "Left" exist for the same StaffID and ROLEnumber.
- Keep "Left" status rows only if there's no corresponding "Current" status for the same combination.
Expected Output (Achieved):
I used Power Query (Transform Data) in Power BI and applied logic to:
- Group by StaffID and ROLEnumber
- Check if "Current" exists
- If yes, keep only "Current"; else, keep all
For your reference, I’ve attached the .pbix file so you can explore the full solution and steps applied.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.