Forum Discussion
Removing rows where there are duplicate fields
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.
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?
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.
