• 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 =Excel.CurrentWorkbook()
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Updating
• 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
Index