Power Query: Min Date of first invoices per customer

[fazza1991]

Hi ,

I am trying to work out the 'POWER QUERY' formula for obtaining the 'First Invoice' date of each customer.

Date	Account_Id	Revenue
30/04/2021	10	15
30/03/2021	10	155
30/04/2021	11	14
30/04/2021	12	16
29/02/2020	11	122

My Table is pretty basic only contains date of invoice, account_id and revenue.

I would expect the results to show as :

Date	Account_Id	Revenue	First Invoice
30/04/2021	10	15	30/03/2021
30/03/2021	10	155	30/03/2021
30/04/2021	11	14	29/02/2020
30/04/2021	12	16	30/04/2021
29/02/2020	11	122	29/02/2020

I have the DAX formula - but for what i am trying to accomplish i require the Query equivalent.

FirstInvoiceDate = CALCULATE(MIN([Date]),ALLEXCEPT([Account_Id]))

[fazza1991]

Thank you - this worked.

Out of curiousity what other ways could this be done in PowerQueryEditor?

[HotChilli · Accepted solution]

Lots of ways to do it.

Using the interface:

Select the Account Id column.

Do a 'Group By' (which will be on Account Id) and choose Advanced with an aggregation of Min on Date and then another on All Rows.

Then Expand the column header from all to return Date and Revenue