Returning data with earliest and latest date based on 2 columns (M/Power Query Editor)

[nfietz]

Hi,

***Update: Thanks to all for the solutions. I'm new to PBI and realized based on the solutions I should have specified that I'm looking to do this via M/Power Query Editor. This will create a table that I can then join to other fact tables.

I'm trying to pare down an employee table that currently has multiple lines per Employee/Sup combination to a single line per Employee/Sup combination. The desired result would have the earliest Effective Start and latest Effective Stop per Employee/Sup combination. Below is an example of the input and desired output.

I'm not exactly sure what terminology to use to search for a solution, so even if you don't have a solution but can provide me with some key search terms I'd appreciate it. 

Thanks.

[v-yadongf-msft]

Hi @nfietz ,

Please try following DAX to create new columns:

Earliest Effective start = MINX(FILTER('table','table'[Supervisor name]=EARLIER('table'[Supervisor name])),'table'[Effective start])

Latest Effective Stop = MAXX(FILTER('table','table'[Supervisor name] = EARLIER('table'[Supervisor name])),'table'[Effective Stop])

Then you can create a table visual and get result you want.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nfietz]

Hi Yadong,

Thanks for providing this solution. Based on your responses and others, I realized that I failed to specify that I'd like to do this within the Power Query editor/M language. The reason is that I actually need to build this table that can be joined to other fact tables. 

[Ashish_Mathur]

Hi,

To your visual, drag Employee Name, Employee ID and Supervisor Name.  Write these measures

Measure1 = min(Data[Effective start])

Measure2 = max(Data[Effective stop])

Hope this helps.

[nfietz]

Hi Ashish,

Thanks for providing this solution. Based on your responses and others, I realized that I failed to specify that I'd like to do this within the Power Query editor/M language. The reason is that I actually need to build this table that can be joined to other fact tables. 

[davehus]

Hi @nfietz ,

If you add the calculated columns below to your table, it should give you what you need.

First Date = CALCULATE(MIN('Table'[Effective Date]), ALLEXCEPT('Table','Table'[Supervisor Name],'Table'[Employee Name]))

Last Date = CALCULATE(MAX('Table'[Effective Stop Date]), ALLEXCEPT('Table','Table'[Supervisor Name],'Table'[Employee Name]))

Hope this helps.

Did I help you today? Please accept my solution and hit the Kudos button.

[nfietz]

Hi Dave,

Thanks for providing this solution. Based on your responses and others, I realized that I failed to specify that I'd like to do this within the Power Query editor/M language. The reason is that I actually need to build this table that can be joined to other fact tables. 

[davehus · Accepted solution]

Hi @nfietz ,

Even easier to do on PQ. Go to transform ribbon>Group by.

Group by your dimsension and use Min/Max aggregation to get your dates.

Did I help you today? Please accept my solution and hit the Kudos button.

[nfietz]

Thank you @davehus!