Consolidate & Clean Multiple Excel Sheets in One Pivot Table

Introduction

This tutorial will guide you through the process of consolidating and cleaning data from multiple Excel sheets into a single PivotTable using Power Query. This method is particularly useful for professionals who need to aggregate information from various sources efficiently. By following these steps, you'll be able to create a dynamic report that updates automatically as new data is added.

Step 1: Send Data to Power Query

• Open your Excel workbook containing the multiple sheets.

• Navigate to the Data tab in the Ribbon.

• Click on Get Data and select From Other Sources then choose Blank Query.

• In the Power Query Editor, use the following formula to retrieve data from all sheets:

  let
      Source = Excel.CurrentWorkbook(),
      Sheets = Table.SelectRows(Source, each ([Kind] = "Sheet")),
      Data = Table.AddColumn(Sheets, "Data", each Excel.CurrentWorkbook(){[Name=[Name]]}[Content]),
      CombinedData = Table.Combine(Data[Data])
  in
      CombinedData
  

• This formula pulls data from all sheets in your workbook.

Step 2: Exclude Irrelevant Data

• In the Power Query Editor, filter out any grand totals or irrelevant rows.
• Click on the column header to filter, and select or deselect the unwanted data entries.
• To fill gaps in your datasets, use the Transform tab and select Fill to fill down or up as necessary.

Step 3: Apply Data Transformations

• Ensure your data is formatted correctly:
  • Rename columns if necessary for clarity.
  • Remove any columns that are not needed for your analysis.
• Make sure that all relevant data types are set correctly (e.g., date, text, number) by selecting the column and changing the type from the Transform tab.

Step 4: Create PivotTable on Appended Data

• Once your data is cleaned and transformed, go to the Home tab in Power Query.
• Click on Close & Load To and select PivotTable Report.
• Choose where you want the PivotTable to be placed in your Excel workbook.

Step 5: Exclude End-Report as Data Source

• To prevent the PivotTable from including itself as a data source, ensure that the PivotTable is not linked to the data you are consolidating.
• In Power Query, make sure to exclude any references to the PivotTable's sheet.

Step 6: Refresh Report with New Data

• As new sheets are added to the workbook, simply navigate to your PivotTable.
• Right-click on the PivotTable and select Refresh.
• This will automatically pull in any new data from the sheets that match your criteria.

Conclusion

By following these steps, you can effectively consolidate multiple Excel sheets into one dynamic PivotTable using Power Query. This process not only saves time but also ensures that your reports are always up to date with the latest information. As a next step, consider exploring more advanced Power Query transformations to further enhance your data analysis capabilities.