cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lucjan_Szulik Frequent Visitor
Frequent Visitor

Earliest date for employee - Power Query Editor

Hi Guys,

 

I am sure that an identical question was already raised, but I am quite new to Power Query Editor, hence I don't really know how to search for the answer. I have tried few solutions, but nothing seem to work (or I cannot really do it).

 

Anyway: data form SQL server, after some modeling we have:

 

Column on the left: employee ID (multiple for the same ID)

Column on the right: date (different for each line per ID)

 

What I would need to have in the 3rd column is minimum date value that appears for the given ID.

 

Capture.JPG

 

Thanks,

Lucjan

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Earliest date for employee - Power Query Editor

Hi @Lucjan_Szulik,

 

Don't know what you want to calculate but believe that the best way is to use measures because calculated columns have additional space to your model and complexity.

But the options you have for creating the column are DAX and M language see below both options.

 

DAX Calculated column:

Minimum Date Dax = 
CALCULATE (
    MIN ( Dates_DAX[Date] );
    FILTER ( Dates_DAX; Dates_DAX[ID] = EARLIER ( Dates_DAX[ID] ) )
)

 

M Language:

 

  • On query editor select group by
    • Group by ID
    • Operation Min - Date
    • Operation - All Rows

group.png

  • Expande table values

expand.png

 

See below result for both columns and attach PBIX file.

 

Untitled.png

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Super User
Super User

Re: Earliest date for employee - Power Query Editor

Hi @Lucjan_Szulik,

 

Why fo you need to create a 3rd column?

 

If you only want to know what is the minimum date for a particular ID when you add it to a visual you can select the sumarization as MINIMUM, other way is to create a measure:

 

Minimum Date = MIN(Table[begin_valid_date])

And add it to your visuals then based on context if you add the ID's it will return the minimum value of the context given.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Lucjan_Szulik Frequent Visitor
Frequent Visitor

Re: Earliest date for employee - Power Query Editor

Hi,

 

Need it for calculating absenteeism per seniority. 

I have another table with scheduled hours and all the present/absenteeism. I need to add seniority of every agent on the given day and base on that build some visualizations.

 

Thanks,

Lucjan

Super User
Super User

Re: Earliest date for employee - Power Query Editor

Hi @Lucjan_Szulik,

 

Don't know what you want to calculate but believe that the best way is to use measures because calculated columns have additional space to your model and complexity.

But the options you have for creating the column are DAX and M language see below both options.

 

DAX Calculated column:

Minimum Date Dax = 
CALCULATE (
    MIN ( Dates_DAX[Date] );
    FILTER ( Dates_DAX; Dates_DAX[ID] = EARLIER ( Dates_DAX[ID] ) )
)

 

M Language:

 

  • On query editor select group by
    • Group by ID
    • Operation Min - Date
    • Operation - All Rows

group.png

  • Expande table values

expand.png

 

See below result for both columns and attach PBIX file.

 

Untitled.png

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Community Support Team
Community Support Team

Re: Earliest date for employee - Power Query Editor

Hi @Lucjan_Szulik,

 


Can you mark the proper answer as a solution please?

 

Best Regards,

Dale

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