Forum Discussion

dhannaa's avatar
dhannaa
Helper IV
7 years ago
Solved

Power Query : removing duplicates and merging duplicate rows selectively into one

Hi all!   What would be the smart way to remove duplicates here?   I  need the first value from the column b and last value from column c. There can be even more than two rows for the same ID.  ...
  • Anonymous's avatar
    Anonymous
    7 years ago

    For the sake of easy understanding of the code, I have written the power query step by step. But you can combine several steps and make the query much denser.

     

    let Source = Table.FromRecords(
            {
                [id = 1, date = #date(2018,2,3), b = 513, c = 423],
                [id = 2, date = #date(2018,5,1), b = 534, c = 432],
                [id = 2, date = #date(2019,1,18), b = 345, c = 5434],
                [id = 3, date = #date(2019,2,2), b = 354, c = 4534],
                [id = 4, date = #date(2019,4,6), b = 543, c = 544],
                [id = 5, date = #date(2019,5,5), b = 654, c = 432]
            }
        ),
        
        ChangeColumnTypes = Table.TransformColumnTypes(Source,
            {
                {"date", type date}, 
                {"id", Int64.Type}, 
                {"b", Int64.Type}, 
                {"c", Int64.Type}
            }
        ),
    
    TempTable = ChangeColumnTypes,
    ResultTable = ChangeColumnTypes, 
    
    MinDate = (x as table, y as number ) as date => List.Min(
        Table.Column(
            Table.SelectRows(x,each Record.Field(_,"id")=y),
        "date")
    ),
    
    MaxDate = (x as table, y as number ) as date => List.Max(
        Table.Column(
            Table.SelectRows(x,each Record.Field(_,"id")=y),
        "date")
    ),
    
    AddIsFirstDateColumn = Table.AddColumn(ResultTable,
            "IsFirstDate",
            each Record.Field(_,"date") = MinDate(TempTable,Record.Field(_,"id"))
    ),
    
    AddIsLastDateColumn =Table.AddColumn(AddIsFirstDateColumn,
            "IsLastDate",
            each Record.Field(_,"date") = MaxDate(TempTable,Record.Field(_,"id"))
    ),
    
    getFirstValue = (x as table,y as number) as number => List.Min(
        Table.Column(Table.SelectRows(
            x, each Record.Field(_,"id")=y and Record.Field(_,"IsFirstDate") = true
        ),"b")
    ),
    
    FirstValue = Table.AddColumn(
        AddIsLastDateColumn,"FirstValue",
        each getFirstValue(AddIsLastDateColumn,Record.Field(_,"id"))
    ),
    
    getLastValue = (x as table,y as number) as number => List.Min(
        Table.Column(Table.SelectRows(
            x, each Record.Field(_,"id")=y and Record.Field(_,"IsLastDate") = true
        ),"c")
    ),
    
    LastValue = Table.AddColumn(
        FirstValue,"LastValue",
        each getLastValue(AddIsLastDateColumn,Record.Field(_,"id"))
    ),
    
    RemoveDuplicates = Table.SelectRows(LastValue,each [IsLastDate] = true),
    RemoveColumns = Table.RemoveColumns(RemoveDuplicates,{"IsLastDate","IsFirstDate","b","c"})
    in
        RemoveColumns