Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Conditionally remove duplicates

Hello Community,   Is there a way in PowerQuery or Excel to conditionally remove duplicates?  Very quick context there are employee id numbers, their planned hours for the year, the financial ye...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous here is the example power query script which you can use to achieve it.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABFA7BZpaKYUq4MkaoRP1BxV1BhZFKTECCRqgmwCXNQUqyh2E/Cai12tBVa1lljdABSNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Emp = _t, Planned = _t, Hours = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp", Int64.Type}, {"Planned", Int64.Type}, {"Hours", Int64.Type}, {"Year", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Emp", "Year"}, {{"Rank", each Table.AddIndexColumn(_, "Rank", 1)}}),
    #"Expanded Rank" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Hours", "Planned", "Rank"}, {"Hours", "Planned", "Rank.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Rank", "New Planned", each (if [Rank.1] = 1 then [Planned] else null), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Planned", "Rank.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Hours", Int64.Type}})
in
    #"Changed Type1"

     

  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago
    Hi Anonymous ,
     
    First, you can select "pers.no" and "FY" columns in the edit query, then click "Remove rows"→" Remove duplicate".
     
    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
parry2k's avatar
parry2k
Super User
6 years ago

Anonymous here is the example power query script which you can use to achieve it.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABFA7BZpaKYUq4MkaoRP1BxV1BhZFKTECCRqgmwCXNQUqyh2E/Cai12tBVa1lljdABSNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Emp = _t, Planned = _t, Hours = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp", Int64.Type}, {"Planned", Int64.Type}, {"Hours", Int64.Type}, {"Year", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Emp", "Year"}, {{"Rank", each Table.AddIndexColumn(_, "Rank", 1)}}),
    #"Expanded Rank" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Hours", "Planned", "Rank"}, {"Hours", "Planned", "Rank.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Rank", "New Planned", each (if [Rank.1] = 1 then [Planned] else null), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Planned", "Rank.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Hours", Int64.Type}})
in
    #"Changed Type1"