Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have employee data from a time management system with clock in and out per day laid out in a table like the below;
DATE | USERID | CLOCK |
25/02/2020 | 1234 | 08:00:00 |
25/02/2020 | 1234 | 17:00:00 |
There is a Department and CostCode table but that only holds the current Department and CostCode, nothing historic. There is an audit table laid out like the below.
CHANGEDATE | USERID | CHANGETYPE | NEWVALUE |
01/01/2019 | 1234 | DEPARTMENT | Warehouse |
01/05/2019 | 1234 | DEPARTMENT | Stores |
01/05/2019 | 1234 | COSTCODE | Store1 |
25/02/2020 | 1234 | DEPARTMENT | Shipping |
25/02/2020 | 1234 | COSTCODE | Dock1 |
What I need to be able to do is assign the cost code of the time to the clock in/out data, like the below.
DATE | USERID | CLOCK | DEPARTMENT | COSTCODE |
24/02/2020 | 1234 | 09:00:00 | Stores | Store1 |
24/02/2020 | 1234 | 18:00:00 | Stores | Store1 |
25/02/2020 | 1234 | 08:00:00 | Shipping | Dock1 |
25/02/2020 | 1234 | 17:00:00 | Shipping | Dock1 |
Any thoughts?
Thanks in advance!
Solved! Go to Solution.
Hi, @pedanticpad
Based on your data, you may create two calculated columns in 'Clock' table as follows.
COSTCODE =
CALCULATE (
MAX ( 'Audit'[NewValue] ),
FILTER (
'Audit',
'Audit'[UserID] = 'Clock'[UserID]
&& 'Audit'[ChangeType] = "COSTCODE"
&& 'Audit'[ChangeDate]
= CALCULATE (
MAX ( 'Audit'[ChangeDate] ),
FILTER (
'Audit',
'Audit'[ChangeDate] <= 'Clock'[Date]
&& 'Audit'[UserID] = 'Clock'[UserID]
)
)
)
)
DEPARTMENT =
CALCULATE (
MAX ( 'Audit'[NewValue] ),
FILTER (
'Audit',
'Audit'[UserID] = 'Clock'[UserID]
&& 'Audit'[ChangeType] = "DEPARTMENT"
&& 'Audit'[ChangeDate]
= CALCULATE (
MAX ( 'Audit'[ChangeDate] ),
FILTER (
'Audit',
'Audit'[ChangeDate] <= 'Clock'[Date]
&& 'Audit'[UserID] = 'Clock'[UserID]
)
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I did not get your logic to get dates in last table. But 2nd table can be tranposed like this
Table = SUMMARIZE(Sheet1,Sheet1[CHANGEDATE],Sheet1[USERID],"Department",maxx(FILTER(Sheet1,Sheet1[CHANGETYPE]="DEPARTMENT"),Sheet1[NEWVALUE]),"Cost Code",maxx(FILTER(Sheet1,Sheet1[CHANGETYPE]="COSTCODE"),Sheet1[NEWVALUE]))
Hi @amitchandak / @v-alq-msft,
Sorry, I must not be explaining myself properly.
See below tables (note only 1 UserID used as an example, but there are actually about 300 employees). The Audit Table only records the date a change is made, but I need to assign the Department and CostCode to every clock record per the Desired Result Table below.
Clock Table | ||
DATE | USERID | CLOCK |
01/02/2020 | 1234 | 09:00:00 |
01/02/2020 | 1234 | 17:00:00 |
02/02/2020 | 1234 | 09:00:00 |
02/02/2020 | 1234 | 17:00:00 |
03/02/2020 | 1234 | 09:00:00 |
03/02/2020 | 1234 | 17:00:00 |
04/02/2020 | 1234 | 09:00:00 |
04/02/2020 | 1234 | 17:00:00 |
05/02/2020 | 1234 | 09:00:00 |
05/02/2020 | 1234 | 17:00:00 |
06/02/2020 | 1234 | 09:00:00 |
06/02/2020 | 1234 | 17:00:00 |
07/02/2020 | 1234 | 09:00:00 |
07/02/2020 | 1234 | 17:00:00 |
08/02/2020 | 1234 | 09:00:00 |
08/02/2020 | 1234 | 17:00:00 |
Audit Table | |||
CHANGEDATE | USERID | CHANGETYPE | NEWVALUE |
01/02/2020 | 1234 | DEPARTMENT | Stores |
01/02/2020 | 1234 | COSTCODE | Store1 |
06/02/2020 | 1234 | DEPARTMENT | Shipping |
06/02/2020 | 1234 | COSTCODE | Dock1 |
Desired Result Table | ||||
DATE | USERID | CLOCK | DEPARTMENT | COSTCODE |
01/02/2020 | 1234 | 09:00:00 | Stores | Store1 |
01/02/2020 | 1234 | 17:00:00 | Stores | Store1 |
02/02/2020 | 1234 | 09:00:00 | Stores | Store1 |
02/02/2020 | 1234 | 17:00:00 | Stores | Store1 |
03/02/2020 | 1234 | 09:00:00 | Stores | Store1 |
03/02/2020 | 1234 | 17:00:00 | Stores | Store1 |
04/02/2020 | 1234 | 09:00:00 | Stores | Store1 |
04/02/2020 | 1234 | 17:00:00 | Stores | Store1 |
05/02/2020 | 1234 | 09:00:00 | Stores | Store1 |
05/02/2020 | 1234 | 17:00:00 | Stores | Store1 |
06/02/2020 | 1234 | 09:00:00 | Shipping | Dock1 |
06/02/2020 | 1234 | 17:00:00 | Shipping | Dock1 |
07/02/2020 | 1234 | 09:00:00 | Shipping | Dock1 |
07/02/2020 | 1234 | 17:00:00 | Shipping | Dock1 |
08/02/2020 | 1234 | 09:00:00 | Shipping | Dock1 |
08/02/2020 | 1234 | 17:00:00 | Shipping | Dock1 |
Hopefully that makes more sense?
Hi, @pedanticpad
Based on your data, you may create two calculated columns in 'Clock' table as follows.
COSTCODE =
CALCULATE (
MAX ( 'Audit'[NewValue] ),
FILTER (
'Audit',
'Audit'[UserID] = 'Clock'[UserID]
&& 'Audit'[ChangeType] = "COSTCODE"
&& 'Audit'[ChangeDate]
= CALCULATE (
MAX ( 'Audit'[ChangeDate] ),
FILTER (
'Audit',
'Audit'[ChangeDate] <= 'Clock'[Date]
&& 'Audit'[UserID] = 'Clock'[UserID]
)
)
)
)
DEPARTMENT =
CALCULATE (
MAX ( 'Audit'[NewValue] ),
FILTER (
'Audit',
'Audit'[UserID] = 'Clock'[UserID]
&& 'Audit'[ChangeType] = "DEPARTMENT"
&& 'Audit'[ChangeDate]
= CALCULATE (
MAX ( 'Audit'[ChangeDate] ),
FILTER (
'Audit',
'Audit'[ChangeDate] <= 'Clock'[Date]
&& 'Audit'[UserID] = 'Clock'[UserID]
)
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @pedanticpad
Based on your description, I assumed that two more rows for 5/1/2019 are needed and I created data as follows.
Employee data:
Department and CostCode:
You may duplicate query 'Employee data' and rename it as 'ResultTable'. Then you can go to 'Home' ribbon, click 'Merge Queries', configure as follows.
Then you need to expand the last column, remove the unrelated columns and rename other columns.
Finally you may make 'ChangeType' column selected, go to 'Transform' ribbon, click 'Pivot Column' and configure as below. You will get the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-alq-msft,
When I perform the pivot I get the below error;
There are alot of NULL values in the CHANGETYPE and NEWVALUE fields. I think the merge is only pulling in the data for an exact match and the other dates for each user is left as NULL.
I need to apply the department and cost code to every clock date so that for every clock before 05/01/2019 Department = Stores and CostCode = Store1, then for every clock from 02/25/2020 Department =Shipping and CostCode = Dock1. Any advice?
Thanks!
Hi, @pedanticpad
I wonder the result of the last step before pivotting. Here is my result before pivotting.
If you have null in one column, you can click 'more options', choose what you want to filter.
Best Regards
Allan
Hi @v-alq-msft ,
See below step before the pivot;
I tried to filter out the null values but I got the below after pivoting;
Error;
Is the issue maybe that I am trying to do this against all employees not just one like in the example I gave?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |