Power Query

• Enable you to consolidate data from multiple spreadsheets into one table
• Record all steps so that you can apply them repeatedly
• Available in Excel 2010 and up
• Named the range in each table which make Power Query easy to find


Using Power Query

• Power Query can be accessed via
◇ Excel 2016: Data > Get Data > From Other Sources > Blank Query
◇ Excel 2010/2013: Power Query tab. If you can't see it, download Power Query as a free add-in


Open the Source

• In the formula bar of the Power Query Editor, enter the following:

# List tables and named ranges


Filter and Expand Contents

• You can filter the tables or named ranges used in the query by clicking the icon next to column “Name”
◇ Select either Expand / Aggregate
◇ Uncheck: “Use original column name as prefix”
• Power Query grab the data from the sheets based on the above filter
◇ Column “Name” represents the table names


Removed Columns

# Shortcut key
DELETE key - remove column


Set Data Type

• You need to set data type in Power Query. You can set the data type for each column by clicking the icon at the top left of the column
• Name the query


Generate Output

• Home > Close and Load To
◇ Pivot Chart
◇ Pivot Table Report
◇ Table (select this option)
• Options:
Add this data to the Data Model (used by Power Pivot)
◇ Output to existing worksheet / new worksheet
• The table generated will be in different color than the source data
• You can proceed with creating a Pivot Table



• If the source data is updated / changed, you need to refresh the Power Query output by clicking the refresh icon at the panel on the right