How to Activate Power Query in Excel 2016

PDF download Download Article
PDF download Download Article

This wikiHow teaches you how to use the Power Query feature in Excel 2016 on a Windows and Mac computer. Power Query is an Excel function that allows you to import data from a variety of sources (Excel tables, CSV files, online database, etc.), and easily reconfigure the data into a pivot table in a spreadsheet. [1] Microsoft released Power Query and Power Pivot as Excel Add-ons in 2010 but these features are now standard in Excel 2016 under the "Get and Transform" function. [2]

Steps

PDF download Download Article
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/7\/73\/Activate-Power-Query-in-Excel-2016-Step-1-Version-2.jpg\/v4-460px-Activate-Power-Query-in-Excel-2016-Step-1-Version-2.jpg","bigUrl":"\/images\/thumb\/7\/73\/Activate-Power-Query-in-Excel-2016-Step-1-Version-2.jpg\/aid10111052-v4-728px-Activate-Power-Query-in-Excel-2016-Step-1-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    It's the app icon that resembles a green spreadsheet with a white "X" on the cover.
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/3\/37\/Activate-Power-Query-in-Excel-2016-Step-2-Version-2.jpg\/v4-460px-Activate-Power-Query-in-Excel-2016-Step-2-Version-2.jpg","bigUrl":"\/images\/thumb\/3\/37\/Activate-Power-Query-in-Excel-2016-Step-2-Version-2.jpg\/aid10111052-v4-728px-Activate-Power-Query-in-Excel-2016-Step-2-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    To open a new document, either click "New" in the green sidebar of the opening screen—or if you have an existing document open—click "File" and then click "New."
    Advertisement
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/b\/b9\/Activate-Power-Query-in-Excel-2016-Step-3-Version-2.jpg\/v4-460px-Activate-Power-Query-in-Excel-2016-Step-3-Version-2.jpg","bigUrl":"\/images\/thumb\/b\/b9\/Activate-Power-Query-in-Excel-2016-Step-3-Version-2.jpg\/aid10111052-v4-728px-Activate-Power-Query-in-Excel-2016-Step-3-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    It's in the menu bar at the top of Excel 2016.
  4. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/e\/e7\/Activate-Power-Query-in-Excel-2016-Step-4-Version-2.jpg\/v4-460px-Activate-Power-Query-in-Excel-2016-Step-4-Version-2.jpg","bigUrl":"\/images\/thumb\/e\/e7\/Activate-Power-Query-in-Excel-2016-Step-4-Version-2.jpg\/aid10111052-v4-728px-Activate-Power-Query-in-Excel-2016-Step-4-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    It's next to an icon that resembles a table in front of a battery in the "Get and Transform" box. This displays a drop-down menu with a variety of source types you can import data from.
    • On Mac, click either "From HTML", "From Text", or click "New Database Query."
  5. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/0\/06\/Activate-Power-Query-in-Excel-2016-Step-5-Version-2.jpg\/v4-460px-Activate-Power-Query-in-Excel-2016-Step-5-Version-2.jpg","bigUrl":"\/images\/thumb\/0\/06\/Activate-Power-Query-in-Excel-2016-Step-5-Version-2.jpg\/aid10111052-v4-728px-Activate-Power-Query-in-Excel-2016-Step-5-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    The "New Query" drop-down menu contains a variety of sub-menus that allow you to import data from a variety of data source types. This opens a browser menu to browse source files. Click a file to select it, and then click "Import". The source type menus include:
    • From File: This menu allows you to import data from another Excel worksheet, a CSV file, XML file, JSON, and more.
    • From Database: This menu allows you to import data from a database such as MySQL or Oracle.
    • From Online Service: This menu allows you to import data from online sources, such as SharePoint, Microsoft Exchange, Salesforce, and Facebook.
    • From Other Sources: This menu allows you to import data from other sources, such as the web, Active Directory, OData, Hadoop files, ODBC, OLEDB, and a blank query.
  6. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/2\/2a\/Activate-Power-Query-in-Excel-2016-Step-6-Version-2.jpg\/v4-460px-Activate-Power-Query-in-Excel-2016-Step-6-Version-2.jpg","bigUrl":"\/images\/thumb\/2\/2a\/Activate-Power-Query-in-Excel-2016-Step-6-Version-2.jpg\/aid10111052-v4-728px-Activate-Power-Query-in-Excel-2016-Step-6-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    When you import data from an external file or source, a window pops up that allows you to select specific data from your source. Click the data type you want to load in the sidebar to the right. Then click "Load" in the lower-right hand corner of the window. This loads the data and creates a connection to the data source.
  7. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/c\/c5\/Activate-Power-Query-in-Excel-2016-Step-7-Version-2.jpg\/v4-460px-Activate-Power-Query-in-Excel-2016-Step-7-Version-2.jpg","bigUrl":"\/images\/thumb\/c\/c5\/Activate-Power-Query-in-Excel-2016-Step-7-Version-2.jpg\/aid10111052-v4-728px-Activate-Power-Query-in-Excel-2016-Step-7-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    All queries are listed under the "Workbook Queries" sidebar to the right. Double-clicking a query opens the query editor.
    • If the "Workbook Queries" sidebar isn't showing on-screen, click the "Data" tab and then click "Queries & Connections" in the "Get and Transform" section. On Mac, click the "Connections" button to list the current connections.
  8. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/c\/c9\/Activate-Power-Query-in-Excel-2016-Step-8-Version-2.jpg\/v4-460px-Activate-Power-Query-in-Excel-2016-Step-8-Version-2.jpg","bigUrl":"\/images\/thumb\/c\/c9\/Activate-Power-Query-in-Excel-2016-Step-8-Version-2.jpg\/aid10111052-v4-728px-Activate-Power-Query-in-Excel-2016-Step-8-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    The query editor has a ton of tools you can use to edit and transform your data. You can add or remove columns and rows, merge or append queries, and transform data in various ways.
    • If you are merging two data queries together, click "Merge Queries". Then you will need to select a common data field that the two queries will be merged together by. Then use the "Join Kind" drop-down menu to select what information will be included after they are merged.
  9. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/2\/20\/Activate-Power-Query-in-Excel-2016-Step-9-Version-2.jpg\/v4-460px-Activate-Power-Query-in-Excel-2016-Step-9-Version-2.jpg","bigUrl":"\/images\/thumb\/2\/20\/Activate-Power-Query-in-Excel-2016-Step-9-Version-2.jpg\/aid10111052-v4-728px-Activate-Power-Query-in-Excel-2016-Step-9-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    It's the button that has an icon of a purple disk in front of a sheet of paper. It's on the far-left under the "Home" tab. This exports the data from the query editor into the Excel worksheet.
    10. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      How to
      Recover a Corrupt Excel File
      How to Merge Cells in Microsoft Excel: A Quick Guide
      Easily Create a Drop-Down List in Microsoft Excel: Setup & Customization
      How to Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Unprotect an Excel Sheet
      How to Create a Graph in Excel
      How to Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      How to
      Link Sheets in Excel
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Make a Spreadsheet in Excel
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to Unhide Multiple Rows in Microsoft Excel
      How to Create an Inventory List in Microsoft Excel: Step-by-Step Guide
      How to Password Protect a Microsoft Excel Spreadsheet
      Advertisement

      References

      1. ↑ https://www.excelcampus.com/powerquery/power-query-overview/
      2. ↑ https://www.youtube.com/watch?v=Mpa5zMj_vNo

      About This Article

      Article Summary X

      1. Click the Data tab.
      2. Click Get Data .
      3. Select a data source and select your data to import.
      4. Double-click a query to edit it.
      5. Click Close and Load .

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 54,322 times.

      Is this article up to date?

      Advertisement