Forum Discussion

83dons's avatar
83dons
Helper III
1 year ago
Solved

Removing rows where there are duplicate fields

Hi    I have a table as per below (simplified for the purposes here). You can see it does leave the rows in if both are Current. I am just trying to get rid of first the rows where ROLEnumber is th...
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    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.

v-ssriganesh's avatar
v-ssriganesh
Community Support
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.