none
Getting the minimum value for a data set with multiples for a specific key RRS feed

  • Question

  • I have an Excel spreadsheet that has multiple rows for a specific customer.  I want to only keep the row for each customer that has the earliest shipping date.  Can I do that with Power Query or Power Pivot.  I'm actually going to them want to jointhe results from this spreadsheet with another spreadsheet that has one row for each customer.

    Wednesday, March 19, 2014 6:54 PM

Answers

  • If you want to keep the entire row of data then you can do it by.

    1. In the Group builder, instead of picking "Min", pick "All Rows" and click OK

    2. This will produce a new column with all the rows embedded inside. You now need to update the formula so that it only keeps the single row with the minimum date value.

    3. You need to change the formula like this:

    Table.Group(previousStep, {"Name"}, {{"NewColumn", each Table.Min(_,"Date"), type record}})

    *here previousStep is the previous task generated by Power Query, you do not need to edit this.

    4. This should give you a nested record under the new column, now simply click the icon on the column header and expand the entire row to the top level.

    does this work?


    Zafar Abbas

    • Proposed as answer by Zafar Abbas Saturday, March 22, 2014 6:13 PM
    • Marked as answer by RACAND Tuesday, March 25, 2014 5:17 PM
    Saturday, March 22, 2014 6:13 PM

All replies

  • You can simply do that by Doing a Group on the customer name and then pick "Min" as the grouping operation over the column which contains the date values. here are the steps.

    Assuming, you have  table with two columns, Name and Date. There are multiple rows in the table for the same Name value but different values for Date for the same Name.

    1. Right click on column header for Name and select Group

    2. In the Group builder, pick "Min" from the drop down of operations and pick the column Date as the column to apply this grouping over.

    3. The resulting table should have exactly one row per Name and the minimum date value.

    Hope this helps.


    Zafar Abbas

    • Proposed as answer by Zafar Abbas Wednesday, March 19, 2014 10:47 PM
    • Unproposed as answer by Zafar Abbas Saturday, March 22, 2014 6:13 PM
    Wednesday, March 19, 2014 10:46 PM
  • Actually the table has other data such as shipping address, order_amt, date the order was placed. I want to be able to bring back all that data for the one with the minimum date.
    Thursday, March 20, 2014 4:25 PM
  • If you want to keep the entire row of data then you can do it by.

    1. In the Group builder, instead of picking "Min", pick "All Rows" and click OK

    2. This will produce a new column with all the rows embedded inside. You now need to update the formula so that it only keeps the single row with the minimum date value.

    3. You need to change the formula like this:

    Table.Group(previousStep, {"Name"}, {{"NewColumn", each Table.Min(_,"Date"), type record}})

    *here previousStep is the previous task generated by Power Query, you do not need to edit this.

    4. This should give you a nested record under the new column, now simply click the icon on the column header and expand the entire row to the top level.

    does this work?


    Zafar Abbas

    • Proposed as answer by Zafar Abbas Saturday, March 22, 2014 6:13 PM
    • Marked as answer by RACAND Tuesday, March 25, 2014 5:17 PM
    Saturday, March 22, 2014 6:13 PM
  • This worked great!  Thanks very much.

    Tuesday, March 25, 2014 5:18 PM