Well, start by sharing some data (remove anything confidential). Perhaps it just a small issue that can be resolved easily.

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. 

Here is the attached exel file

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.

I will review the spreadsheet.  Thank you so very much.  

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. 

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.

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.   😉

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.

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? 

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.

Riny, thank you.  I made a few attempts and then was able to do it.  I believe that finishes this task.  On to another.  Plus finish going through all my coursework. 🙂

Great! Good luck with learning more.