Filtering Table

[Spencer]

Hi

I have imported in a table a list of payroll transactions. However I'm only interested in what department each employee was in at the end of each month. So my relevant columns are employee_no, ypt_pay_date and ypt_department.

An issue is that the same employee_no may have multiple ypt_pay_date within the same month.

What would be the most efficient way to create maybe a new table that lists the employee_no, ypt_pay_date and ypt_department, without having the same employee_no appear twice in the same month.

Thanks

Payroll Transactions Table

[sguenther · Accepted solution]

Hi @Spencer,

so in my opinion the first thing you have to do is to create a new "Month" column.

Then you can group your original table (here: "Table_all") by employee_no and month like this:

Create a new table via:

Table_new = 
GROUPBY(
	Table_all,
		[Month],
		[employee_no],
			"ypt_pay_date", MAXX(CURRENTGROUP(), Table_all[ypt_pay_date])
)

Thereby you add another column, which will specify your department. If I understood you correctly, you want to get the department at the end of the month; meaning the department which the last payment was from in the list in each month. The ypt_pay_date column in the new table gives you the last date for each employee and each month. You can use it to look up the specific department via adding a new column and using the following call:

ypt_department = 
LOOKUPVALUE(Table_all[ypt_department], 
	Table_all[employee_no], 
		Table_new[employee_no], 
	Table_all[ypt_pay_date], 
		Table_new[ypt_pay_date]
)

Hope this helps,

best,

Sebastian

[CahabaData]

@Spencer   I noted your reply and am wondering if your suggestion can be applied to this situation:

http://community.powerbi.com/t5/Desktop/by-ID-amp-Last-Date/m-p/62931#U62931

....it seems close and I've experimented with variations of your example but can't seem to make it work.....

[Spencer]

Hi @sguenther, thanks very much for your response and yes you did understand me correctly, apoligies for not being clearer.

The first part of your solution worked perfectly for creating the new table. It's just when I try to add the ypt_department column to Table_new I get an error message which you'll be able to see in my screenshot below. 

Thanks again

Table_new - ypt_department column error

[sguenther]

@Spencer this means that the Lookup function returned multiple values although only one can be returned, meaning that for at least one person, there is a double entry in the original table for the last date in at least one month. Meaning for example:

employee_no | ypt_pay_date | ypt_department

1                     | 26/01/2016   | department1

1                     | 26/01/2016   | department2

And then the Lookup function doen't know which of the two departments to choose because both fit the criteria.

You need to specify your department selection criteria or make sure there are no double entries.

[Spencer]

OK thanks no worries I'll get to finding those discrepancies.

Thanks for all your help its much appreciated.

[ankitpatira]

@Spencer Go to power bi desktop query editor,

1. remove all columns that are not required (right click -> remove)

2. highlight all remaining columns

3. Under reduce rows ribbon select Remove duplicates as shown below.

This will remove duplicates where same employee number has same date and department but you can also only select employee number and remove duplicates.