Removing rows where there are duplicate fields

[83dons]

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 the same and there is a Current and Left (with Left being removed). Not sure how to do this most easily.

StaffID	ROLEnumber	Status
12345	0001	Current
12345	0001	Left
23456	0006	Current
23456	0007	Current
34765	0009	Left
45673	0015	Left
45673	0015	Current
65523	0021	Current
65523	0021	Current

Once the filter is applied the table should then show:

StaffID	ROLEnumber	Status
12345	0001	Current
23456	0006	Current
23456	0007	Current
34765	0009	Left
45673	0015	Current
65523	0021	Current
65523	0021	Current

[v-ssriganesh · Accepted solution]

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.

[83dons]

Hi @v-ssriganesh this appears to work as expected, although when I expand all the rows in the table in the last step is has changed all the data types to ABC123 from what they were originally. How do I avoid this happening and retain the same data types that were in the existing columns? It also adds 'FilteredRows.' before each column name which I am going to have to change back now is there a way of avoiding this also or quick way to remove it from all columns at once?

[v-ssriganesh]

Hi @83dons,
Thank you for your follow up.

Yes, both the change in data types and the "FilteredRows." prefix are expected behaviors when expanding nested tables in Power Query, but you can handle them easily. Here's how:

• After expanding the nested table, Power Query resets column types to Any. You can fix this by selecting the columns and clicking Transform → Detect Data Type, or manually set types as needed.

To quickly remove the prefix:

• Select all affected columns
• Go to Transform → Replace Values
• Replace "FilteredRows." with a blank

If this information resolves your issue, please “Accept as Solution” and give a "Kudos" to help other community members.
Thank you.

[83dons]

@Akash_Varuna any ideas?

[pankajnamekar25]

Hello @83dons 

You can try this M Code

let

    Source = YourSourceStep,

    Grouped = Table.Group(Source, {"StaffID", "ROLEnumber"}, {{"GroupedData", each _, type table [StaffID=number, ROLEnumber=text, Status=text]}}),

    AddHasCurrent = Table.AddColumn(Grouped, "HasCurrent", each List.Contains([GroupedData][Status], "Current")),

    Expanded = Table.ExpandTableColumn(AddHasCurrent, "GroupedData", {"StaffID", "ROLEnumber", "Status"}),

    Filtered = Table.SelectRows(Expanded, each ([Status] = "Current" or [HasCurrent] = false)),

    RemovedHasCurrent = Table.RemoveColumns(Filtered,{"HasCurrent"})

in

    RemovedHasCurrent

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Akash_Varuna]

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]

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.