Combining Data from Multiple Sheets

[xygrax]

Hello,

I badly needed some help with the file im creating and i know that all people here have a big heart to help.

I am trying to consolidate the data from different excel sheets so i can create a dashboard. My challenge is, whenever i try to create a relationship, it keeps on saying that there are duplicate values and cannot create the relationship between tables. I tried using power query but it's just adding another column for the tables.

What i really wanted to do is to combine all sheets into one sheet so i can create a pivot table containing all the data from different sheets. I need all data to be associated with the same employee on the same date.

I have attached a sample file here.


Thanks in advance.... Hoping you could help me please...... please... please...


Thanks!
Ae

[alansidman]

This can be done with PQ. You will need to load each table into PQ. For each table you will need to keep the Date, EE ID, Name and any other columns you wish to have. Remove all others. Reformat the Date column from Date/Time to Date.

For the Voice and Email, do the above. Make sure that the column names for each of these two tables are the same. You will then need to append one to the other.

Now starting with the first table that includes only the three items above, merge each of the tables to each other except for voice and email in which case you should use the appended table. When you merge each to the other, create a new query (merge as new). Use a left inner join. Expand each table for the fields you wish to see.

Move the columns as required.

Because you have lots of data (rows and columns), be very careful in your selection process. This is not a difficult problem to solve, but will take careful and meticulous planning and execution.

You will end up with a long series of Mcodes, but this is a doable exercise.

[xygrax]

Hello Alan,

Thank you so much for your Reply. I will try this and see the result. I appreciate your help.

[AliGW]

@ rebecacamp

Five pointless posts from you so far - I have my eye on you, as your behaviour is suspicious. If you have a question, start a thread, but don't add pointless posts to multiple threads. Thank you.

[xygrax]

hello @alansidman,

I was able to rearrange the tables and tried merging the sheets. i now ran into some issues.

I've noticed that the first few sheets that i have merged are duplicated. whenever I am adding up new merge, it duplicates the previous merged queries
Attachment 684339

I have attached another sample file. hoping you can take a look pls.


Thank you in advance.

[alansidman]

1. Your Attachment (as a picture) is not visible
2. Because all of your queries are connections only, they are not available to me. They are available only to you or others on your network.

[xygrax]

hello @alansidman,

I may have missed that part. anyway, i tried recreating a raw sample as well as the result of merge attached herewith. hope u can take a look

[alansidman]

Still cannot see your queries. You will need to load each one into your workbook instead of connections. In this manner, I will be able to review your steps and Mcode. I suspect that the issue is how you have joined your tables. Left Inner? Full Outer? Etc. I am thinking you will need to have a Left Inner but really wont be able to make sure until you provide viewable queries.

Right click on each query. Click on Load To. Change the Connection to Table and Close and Load. Then I should be able to see each query.