Hi
Below is a short sample of a large dataset and what I am trying to achieve.
Thanks for any help on this.
| Dataset 1 | ||||||||||||
| Period Id Name Gross Pay Overtime | ||||||||||||
| 1 1 John 1,000 100.00 | ||||||||||||
| 1 2 Patrick 3,000 50.00 | ||||||||||||
| Dataset 2 | ||||||||||||
| Period Id Name Gross Pay Overtime | ||||||||||||
| 2 1 John 2,000 100.00 | ||||||||||||
| 2 2 Patrick 4,500 50.00 | ||||||||||||
| Aim is to bring these two periods together on Power Query and obtain the otpout below (I can do this in Crystal Reports easily). | ||||||||||||
| Period Id Name Gross Pay Overtime | ||||||||||||
| 1 1 John 1,000 100.00 | ||||||||||||
| 2 1 John 2,000 100.00 | ||||||||||||
| Totas 3,000 200.00 | ||||||||||||
| Period Id Name Gross Pay Overtime | ||||||||||||
| 1 2 Patrick 2,000 50.00 | ||||||||||||
| 2 2 Patrick 4,500 50.00 | ||||||||||||
| Totals 6,500 100.00 | ||||||||||||
Hi and welcome to our forum!
It's always best if you can attach an Excel file so we can give you a file back with a completed solution as we don't have time to recreate the file for you.
You can certainly combine the two datasets and then the correct way to achieve the end result is to use a PivotTable to extract the data into reports for each person.
It's not clear if the two data sets are in separate files or the same file so I've listed two tutorials below:
- Combine datasets from different sheets in the same file with Power Query
- Combine datasets from separate files with Power Query
If you'd like to learn Power Query, please consider my Power Query course.
Mynda
Mynda, thank you for your reply.
Please see sample file attached with expected outcome. Please open the second file, I can't figure out how to delete the first file - incomplete.
As I said, easily done on Crystal Reports. I though I could do the same on Power Query without too much fuss.
Looks like this question isn't about combining datasets as the sample file already has the dataset combined. Rather, it's about extracting data into a report format.
In the attached file you'll see two options:
1. In cell H1 is a PivotTable that calculates the difference from one period to the next using 'Show Values as' > Difference From > Pay Period, Previous.
2. In cell H24 is a PivotTable that calculates the variance using calculated items. The limitation of this is you can't include the ID number in the PivotTable without it affecting the layout.
Please follow the links for more instructions on how to build these solutions.
Mynda
