I have a time clock program and a payroll program that do not talk to one another.
I can export the time in a csv file, but it's not in the format that the payroll program can accept.
We end up hand typing it all for over 50 employees. This is not ideal.
What I want:
Each row is hourly pay type for each employee.
How it is:
How it is.png
How it needs to be:
How it needs to be.png
A very easy job with PowerQuery (Get & Transform) in Office 365 Excel:
1. Import your .csv file into PowerQuery (Data ribbon > From Text/CSV) - when the dialog appears, click on Transform Data at the bottom right.
2. When the data appears in the PQ window, select the first three columns by shift clicking on the column headers, then with these selected, go to the Transform ribbon - Unpivot Columns drop-down - Unpivot Other Columns.
3. Now go to the Home ribbon and choose Close & Load To from the Close & Load drop-down - choose where to load your data.
All done.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
THANK YOU!
I'm on a Mac so I had to remote into one of our Windows computers to use PowerQuery, but it WORKS!
Glad to have helped. It might be worth adding for Mac to your user profile here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks