Power Query for Excel

Power Query for Excel enables extract, transform, and load of data from a variety of sources into an Excel 2013 workbook. An expert user (called an author) selects the data source, which opens a Power Query editor window and displays a sample of the data.

The Power Query editor menu contains various transformation features that allow an author to select columns, filter data, add columns (such as computed columns), create relationships among data sources, and cleanse the data (such as by modifying column names, setting field types, and transposing data).

The transformed data are displayed in the table (left), and Power Query records transformation steps in the “Applied Steps” list (right).

The example provides a list of per-capita income and median household income for the 50 United States. The transformation steps removed a total row, eliminated a key-field column, reordered the columns, and changed the type to numeric for the income columns, which contained currency symbols that resulted in the columns being listed as text fields. The final steps sorted the table by state and renamed the columns to user-friendly terms.