September 14, 2021
I would like to solicited the incredible minds on the forum to help automate a current process.
I have 3 data sheets in which I create 3 separate pivot tables.
Note: Each of the data sheets contain different headers and the columns are NOT laid out the same.
After the pivot tables are created, I'm currently copy/pasting the pivot tables to remove the pivot table formulas and then combining the data sets together to make one table.
I wanted to see if there's an automated solution for this.
What I've tried:
1. Added each of the worksheets as a connection only into PowerQuery and added them to the data model.
2. Standardized the header names for the columns that I need.
3. Attempted to create a pivot table from the data model.
This does not work.
Not sure if I should create a consolidated table in PowerQuery with just the fields needed and then pivot off that, or if there's a better way.
Attached is the workbook for a better understanding.
July 16, 2010
You should definitely be using Power Query to create your consolidated table before trying to load to Power Pivot or a create a PivotTable.
There's no workbook attached. Be sure to click the 'Start Upload' button after selecting the file and then wait for the grey check mark beside the file size before clicking 'submit reply'. Have a go at appending/merging the tables with Power Query before replying. If you get stuck, come back and share the file including your attempt and desired result.