Forum Discussion
Remove duplicates - quite complex ask
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
RemoveColumn
3. Create table Final Table by using the Append feature to combine Real Rows and Dummy Rows To Keep: