Forum Discussion

83dons's avatar
83dons
Icon for Helper III rankHelper 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.

Akash_Varuna's avatar
Akash_Varuna
Icon for Super User rankSuper User
1 year ago

Hi 83dons  In Power Query, group the data by StaffID and ROLEnumber, and keep all rows in a new column. Then, use a custom column to filter groups:

if List.Contains([Statuses][Status], "Current") then Table.SelectRows([Statuses], each [Status] = "Current") else [Statuses]

Expand the filtered rows and remove intermediate columns. 

83dons's avatar
83dons
Icon for Helper III rankHelper III
1 year ago

Hi Akash_Varuna I prefer the graphical approach so will have a go at your solution. What next? Not sure what to populate the query with.