Forum Discussion
Remove duplicates - quite complex ask
Hello,
Tried copilot but cant quite get it right unfortunately. And read a few complex duplicate removeal threads on here but still no luck.
Ive created dummy rows for my data as I need at least one for each month of the year.
So I have the real row data for each resource in question - and appended this with my dummy rows. Thats all fine. Some of the real rows have two or three for one month. Others have one or none.
I need to do the following.
If any of the dummy rows already have any 'real' row in place for the same month - I want to remove them. If any do not have a real row in place for the same month - I want to keep them. FTE column shows the hours I am interested in. All the real rows have an actual number in them - not 0.00. All the dummy rows have 0.00.
Columns in question i need to use [There are other ones I will keep but they are not used in this calculation]
User:
MergedMonthNames:
FTE:
Co-Pilot gave M code for grouping rows and then working from that - but this isnt working.
Table example below of what I have now and what I want to to result in. For the table there will be multiple resources that this should work for. Any help would be amazing !! Dummy rows are in red.
| User | MergedMonthNames | FTE |
| David | January | 0.5 |
| David | January | 0.5 |
| David | February | 1 |
| David | March | 1 |
| David | April | 1 |
| David | January | 0 |
| David | February | 0 |
| David | March | 0 |
| David | April | 0 |
| David | May | 0 |
| David | June | 0 |
| David | July | 0 |
| David | August | 0 |
| David | September | 0 |
| David | October | 0 |
| David | November | 0 |
| David | December | 0 |
| User | MergedMonthNames | FTE |
| David | January | 0.5 |
| David | January | 0.5 |
| David | February | 1 |
| David | March | 1 |
| David | April | 1 |
| David | May | 0 |
| David | June | 0 |
| David | July | 0 |
| David | August | 0 |
| David | September | 0 |
| David | October | 0 |
| David | November | 0 |
| David | December | 0 |
This solution uses a Power Query aggregation. The concept is a dummy row gets combined with an actual row without impacting the FTE column since adding zero doesn't change actual FTE amounts.
3 Replies
This solution uses a Power Query aggregation. The concept is a dummy row gets combined with an actual row without impacting the FTE column since adding zero doesn't change actual FTE amounts.
Thanks for that DataInsights . I think this will work.
It will leave me with a table with only a few columns of what I require to do the calculations. However I think I can refer from my previous complete table to create this new table that has total FTE for each month for each resource.
If I then link these both my the unique resource I should be able to get my calculations to work. Will try tomorrow and hopefully sorts it out ! Cheers
Here's a different approach that preserves all your columns.
1. Create table Real Rows (by reference to Original Table) and filter FTE <> 0:
2. Create table Dummy Rows To Keep (by reference to Original Table), filter FTE = 0, and use the Merge feature (left anti-join). Use Ctrl-Click to select multiple columns:
let Source = #"Original Table", FilterRows = Table.SelectRows(Source, each ([FTE] = 0)), MergeQueries = Table.NestedJoin(FilterRows, {"User", "MergedMonthNames"}, #"Real Rows", {"User", "MergedMonthNames"}, "Table", JoinKind.LeftAnti), RemoveColumn = Table.RemoveColumns(MergeQueries,{"Table"}) in RemoveColumn3. Create table Final Table by using the Append feature to combine Real Rows and Dummy Rows To Keep:
