Forum Discussion

dhannaa's avatar
dhannaa
Icon for Helper IV rankHelper 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.

 

Thanks in advance for any ideas,

Jenni

Need Help
  • 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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Two inputs required.

     

    1) If there are two entries with the same date and same id, what should be the result?

    2) If there is more than one entry for an id with different dates, after removing the duplicate rows, what is the date you want to show in the date field of the result table? First date or last date?

    • dhannaa's avatar
      dhannaa
      Icon for Helper IV rankHelper IV
      7 years ago

      1) If there are two entries with the same date and same id, what should be the result?

      -> duplicate (the latter one on the list) can be removed. This is very rare possibility and thus it really doesn't make a huge difference whichever it is.

       

      2) If there is more than one entry for an id with different dates, after removing the duplicate rows, what is the date you want to show in the date field of the result table? First date or last date?

      -> Last date. Date has not got too much value after this step.

      • Anonymous's avatar
        Anonymous
        Not applicable
        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