Log In
Register
Home
Dashboards
Power Query
June 8, 2024
Offline
I've watched all the videos and I'm stuck. I have 3 tables that I made into queries. The 3 tables are 1. Personnel with employee id and details of the employee 2. A timekeeping file with employee id and hours and pay codes 3. A pay code file. I would like have all the paycodes as columns , employee information as the rows, and the timekeeping hours as the data.
I'd be willing to fast track by hiring help rather than continue struggling by trying different things repeatedly. I can do all the things I want to do in MS access but I know it can be done in excel I just can't figure out how. I've spent about a week struggling. I can marry the Personnel information with the timekeeping. What I can't do is add all the pay codes to the data. I only see timekeeping data with the paycodes that are used on timesheets. Can anyone here help by providing coaching to help me?
Moderators
January 31, 2022
Offline
Well, start by sharing some data (remove anything confidential). Perhaps it just a small issue that can be resolved easily.
Dashboards
Power Query
June 8, 2024
Offline
I attached a sample file with notes what my problem is. I'm can do a conference call with screen sharing as well.
1. Pay Codes query data
2. Personnel query data
3. Real timekeeping query data
Result that I need is a pivot table
I start with the timekeeping data and retrieve employee first name , last name, and title from the personnel query. I use a pivot to create a pivot table that uses the timekeeping data with Pay codes as columns, employee id and name as rows, and hours summed by Pay code for the data. I need all the pay codes to be listed in the columns even if there is no employee that charged a particular pay code. This data is then used by our payroll company to create the payroll. Hence the columns have to always line up the same way every payroll.
Dashboards
Power Query
June 8, 2024
Offline
Here is the attached exel file
Moderators
January 31, 2022
Offline
It wasn't all that difficult, though I needed to create some sample data tables that weren't linked to your system. I limited the example to Worker ID 100001 through 100011 and hope I understood the structure of your data correctly.
In summary, all you need is create an empty table with the column headers needed in the output sorted in the correct order.
Then, you do a little bit more cleanup to get rid of columns from the Personnel table and rename some in the PayrollExport table. Then you are all set to merge the two based on Worker ID and pivot on the Pay Component column.
Lastly, append the table from the previous step to the empty table with the column headers and you are done.
Note that all was done by clicking in the UI. No manipulation of the M-code needed.
See attached. Come back here if you get stuck.
Dashboards
Power Query
June 8, 2024
Offline
I will review the spreadsheet. Thank you so very much.
Dashboards
Power Query
June 8, 2024
Offline
Riny,
With regards to, "Then, you do a little bit more cleanup to get rid of columns from the Personnel table and rename some in the PayrollExport table. Then you are all set to merge the two based on Worker ID and pivot on the Pay Component column."
I was able to merge the two tables. Here's the question: When you say pivot on the Pay component column, is that done within Query Editor or do I Save & Load and do the pivot table in excel? When I attempted to do it in Query Editor I received an error that it didn't know what to do with null values.
Creating the same output query with no data was easy to do. I had not thought of that. Because I didn't know how to recreate how you did the pivot I got stuck.
Many thanks for walking me through the steps.
Moderators
January 31, 2022
Offline
Okay, let's have a closer look at the pivoting step. In the Personnel query. After merging and expanding the Payroll data, the pivoting is done within PQ. Select the "Pay Component" column, then on the Transfer tab, Pivot Column. Now select 'Hours' as the value column and press OK.
If this doesn't work on your side your real data may be structured differently causing the error message you encounter. However, I can't tell without seeing the data. Perhaps my example was over-simplified.
Dashboards
Power Query
June 8, 2024
Offline
thank you Riny for the help. I haven't finished watching the power pivot video training to see how it integrates with query editor. I do not see the transfer tab. I think it's not that complicated I just have a lot to learn. 😉
Moderators
January 31, 2022
Offline
I meant to write Transform tab but somehow typed Transfer. Sorry about that.
Not sure what you mean by "how Power Pivot integrates with the query editor". Power Query (PQ) is used to extract, transform and load data. Loading can be to an Excel table, a pivot table or, for instance, a load to the Data Model., without loading the result back to Excel. That's where you get into Power Pivot and writing DAX measures.
But within PQ you also have the option to unpivot and pivot data. In my example I pivoted data within PQ.
Dashboards
Power Query
June 8, 2024
Offline
Thank you Thank you!!!! Yay, that worked perfectly.
I have one more problem to add complexity to this model but I'm almost completed the task I've set out to automate. I need to If it sees SCA and the hours equal one (1) then I need it to get the value out of the rate column and put the numbers in for the hours. Should that be done in excel or in power query?
Moderators
January 31, 2022
Offline
I believe all can be done in PQ, but if you send me some anonymous data (like I had in my file) I can have a look. Important that you indicate in that file what the end result should be.
1 Guest(s)
