Remove duplicates - quite complex ask

[RobinIrving]

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

[DataInsights · Accepted solution]

@RobinIrving,

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.

[RobinIrving]

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

[DataInsights]

@RobinIrving,

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
    RemoveColumn

3. Create table Final Table by using the Append feature to combine Real Rows and Dummy Rows To Keep: