Connect to a data source (Power Query)

Use Excel's Get & Transform (Power Query) experience to connect to and import data from a wide variety of sources.

Starting in Excel 2016, you use Get & Transform to connect to external data and perform advanced queries. It works mostly the same as Power Query, but it's not an add-in - it comes installed, and you'll find it on the Data tab of the ribbon. The following sections provide steps for connecting to your data sources - web pages, text files, databases, online services, and Excel files, tables, and ranges.

In Excel 365:

  1. Click the Data tab, then Get Data > From File > From JSON.

  2. Browse to your JSON file location, select it, and click Open.

  3. Once the Query Editor has loaded your data, click Convert > Into Table, then Close & Load.

In Excel 2016:

  1. On the Data tab, click New Query > From Other Sources > Blank Query.

In the Query Editor click Advanced Editor.

Input your query string as follows, replacing "C:\Users\Name\Desktop\JSONTest.json" with the path to your JSON file.

let

    Source = Json.Document(File.Contents("C:\Users\Name\Desktop\JSONTest.json")),
    #"Converted to Table" = Record.ToTable(Source)

in

    #"Converted to Table"

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Use Excel's Get & Transform experience to connect to a web page and import information from different tables.

  1. Click the Data tab, then New Query > From Other Sources > From Web.

    Note: If you don't see the New Query button, click the Data tab, then From Web.

  2. In the From Web dialog box, enter a web page URL, then OK.

    Power Query > From Web > Input URL dialog

    In this case, we're using: http://en.wikipedia.org/wiki/UEFA_European_Football_Championship.

    If the web page requires user credentials:

    • In the Access Web dialog box, click a credentials option, and provide authentication values.

    • Click Save.

  3. Click OK.

  4. Power Query will analyze the web page, and load the Navigator pane in Table View.

    If you know which table you want to connect to, then click it from the list. For this example, we chose the Results table.

    Power Query > From Web > Navigator Table View

    Otherwise, you can switch to the Web View and pick the appropriate table manually. In this case, we've selected the Results table.

    Power Query > From Web > Navigator > Web View
  5. Click Load, and Power Query will load the web data you selected into Excel.

  1. Click the Data tab, then Get Data > From Database > From Microsoft Access Database. If you don't see the Get Data button, click New Query > From Database > From Access.

  2. In the Import Data dialog box, browse for or type a file URL to import or link to a file.

  3. Follow the steps in the Navigator dialog to connect to the table or query of your choice.

  4. Click Load or Edit.

  1. Click on the Data tab, then Get Data > From Database > From SQL Server Database. If you don't see the Get Data button, click New Query > From Database > From SQL Server Database.

  2. In the Microsoft SQL Database dialog box, specify the SQL Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

    Power Query SQL Server Database connection dialog
  4. Select OK.

  5. Select the authentication mode to connect to the SQL Server database.

    Power Query SQL Server connection login credentials
    1. Windows: This is the default selection. Select this if you want to connect using Windows authentication.

    2. Database: Select this if you want to connect using SQL Server authentication. After you select this, specify a user name and password to connect to your SQL Server instance.

  6. By default, the Encrypt connection check box is selected to signify that Power Query connects to your database using an encrypted connection. If you do not want to connect using an encrypted connection, clear this check box, and then click Connect.

    If a connection to your SQL Server is not established using an encrypted connection, Power Query prompts you to connect using an unencrypted connection. Click OK in the message to connect using an unencrypted connection.

Formula Example

You can also use the Query Editor to write formulas for Power Query.

= Sql.Databases(".")
= Sql.Database(".","Contoso")

  1. Click on the Data tab, then Get Data > From File > From XML. If you don't see the Get Data button, click New Query > From File > From XML.

  2. In the From XML Browse dialog box, browse for or type a file URL to import or link to a file.

  3. Click Open.

    After the connection succeeds, you will be able to use the Navigator pane to browse and preview the collections of items in the XML file in a tabular form.

You can also use the Query Editor to write formulas for Power Query. For example:

= Xml.Tables(File.Contents("C:\Downloads\XML Example.xml"))

Note: The Query Editor only appears when you load, edit, or create a new query. To view the Query Editor without loading or editing an existing workbook query, on the Get & Transform ribbon tab, click Get Data > Launch Power Query Editor.

  1. Click the Data tab, then Get Data > From Database > From MySQL Database. If you don't see the Get Data button, then click New Query > From Database > From MySQL Database

  2. Click the Data tab, then Get Data > From Database > From MySQL Database. If you don't see the Get Data button, then click New Query > From Database > From MySQL Database

  3. In the MySQL Database dialog box, in Server Name specify the MySQL Database Server to connect to.

  4. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  5. Click OK.

  6. If the MySQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. Select any cell within your data range.

  2. Click the Data tab, then > From Table/Range.

  3. If prompted, in the From Table dialog box, you can click the Range Selection button to select a specific range to use as a data source.

    From Table dialog

  4. If the table or range of data has column headers, you can check My table has headers. The header cells are used to define the column names for the query.

  5. In the Query Editor, click Close & Load.

Note: If your data range has been defined as a named range, or is in an Excel table, Excel will automatically sense the entire range and load it into the Query Editor for you. Plain data will automatically be converted to a table when it is loaded into the Query Editor.

You can use the Query Editor to write formulas for your query.

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Query Editor Formula Sample

Note: The Query Editor only appears when you load, edit, or create a new query. To view the Query Editor without loading or editing an existing workbook query, on the Get & Transform ribbon tab, click Get Data > Launch Power Query Editor.

  1. Click on the Data tab, then Get Data > From File > select From Workbook. If you don't see the Get Data button, click on New Query > From File > select From Workbook.

    Note: You can also restore the Legacy Connectors to mimic earlier behavior. See the section on "How do I restore the legacy Get External Data experience?" in the following article: Unified Get & Transform.

  2. In the Excel Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

    If your source workbook has named ranges, the name of the range will be available as a data set.

You can also use the Query Editor to write formulas for your query. For example:

= Excel.Workbook
 (File.Contents("C:\Example\Products and Orders.xlsx"))

Note: The Query Editor only appears when you load, edit, or create a new query. To view the Query Editor without loading or editing an existing workbook query, on the Get & Transform ribbon tab, click Get Data > Launch Power Query Editor.

  1. Click on the Data tab, then Get Data > From File > select From Text/CSV. If you don't see the Get Data button, click on New Query > From File > select From CSV, or From Text.

    Note: You can also restore the Legacy Connectors to mimic earlier behavior. See the section on "How do I restore the legacy Get External Data experience?" in the following article: Unified Get & Transform.

  2. In the Comma-Separated Values Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

Note: If you are importing data from a CSV file, Power Query will automatically detect column delimiters including column names and types. For example, if you imported the example CSV file below, Power Query automatically uses the first row as the column names and changes each column data type.

Example CSV file

Image of a CSV file

Power Query automatically changes each column data type:

  • Order ID changes to number

  • Order Date changes to date

  • Category remains text (the default column type)

  • Product Name remains text (the default column type)

  • Sales changes to number

In the Query Editor, Power Query automatically applies a FirstRowAsHeader step and ChangeType step. These automatic actions are equivalent to manually promoting a row and manually changing each column type.

After Power Query auto detects columns, you can also use the Query Editor to write formulas for Power Query. For example:

= Csv.Document(File.Contents("C:\Examples\Products Categories and Orders.csv"),null,",",null,1252)

= Table.PromoteHeaders(Source)

= Table.TransformColumnTypes(FirstRowAsHeader,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})

Note: The Query Editor only appears when you load, edit, or create a new query. To view the Query Editor without loading or editing an existing workbook query, on the Get & Transform ribbon tab, click Get Data > Launch Power Query Editor.

  1. Click on the Data tab, then Get Data > From Other Sources > From Sharepoint List. If you don't see the Get Data button, then click New Query > From Other Sources > From Sharepoint List.

  2. In the Microsoft SharePoint Lists dialog box that appears, enter the URL for a SharePoint site.

    Note: When connecting to a SharePoint list, enter the site URL instead of the list URL. In the Access SharePoint dialog box, select the most general URL to authenticate against the site correctly. By default, the most general URL is selected.

  3. Select OK to continue.

  4. In the Access SharePoint dialog box that appears next, select a credentials option:

    1. Select Anonymous if the SharePoint Server does not require any credentials.

    2. Select Windows if the SharePoint Server requires your Windows credentials.

    3. Select Organizational account if the SharePoint Server requires organizational account credentials.

  5. Select Connect.

    Excel Power Query connect to a Sharepoint List Connect dialog

Note: Before you can connect to an Oracle database using Power Query, you need the Oracle client software v8.1.7 or greater on your computer. To install the Oracle client software, go to 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio (12.1.0.2.4) to install the 32-bit Oracle client, or to 64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64 to install the 64-bit Oracle client.

  1. Click on the Data tab, then Get Data > From Database > From Oracle Database. If you don't see the Get Data button, then click New Query > From Database > From Oracle Database.

  2. In the Oracle Database dialog box, in Server Name specify the Oracle Server to connect to. If a SID is required, this can be specified in the form of “ServerName/SID”.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the Oracle server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

Note: Before you can connect to a PostgreSQL database in Power Query, you need the Ngpsql data provider for PostgreSQL installed on your computer. Select the driver that matches your Office version (32-bit or 64-bit). See: Which version of Office am I using? for more information. Also make sure you have the provider registered in the machine configuration that matches the most recent .NET version on your device.

  1. Click the Data tab, then Get Data > From Database > From PostgreSQL Database. If you don't see the Get Data button, then click on New Query > From Database > From PostgreSQL Database.

  2. In the PostgreSQL Database dialog box, specify the PostgreSQL Database Server you want to connect to in the Server Name section.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the PostgreSQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Select Connect.

Notes: 

  • This feature only available in Excel for Windows if you have Office 2019 or an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.

  • Before you can connect to an SAP HANA database using Power Query, you need the SAP HANA ODBC Driver on your computer. Select the driver that matches your Power Query installation (32-bit or 64-bit).

  • You will need an SAP account to login to the website and download the drivers. If you are unsure, contact the SAP administrator in your organization.

To connect to an SAP HANA database:

  1. Click Data > New Query > From Database > From SAP HANA Database.

    SAP HANA database option in the Data tab
  2. In the SAP HANA Database dialog box, specify the server you want to connect to. The server name should follow the format ServerName:Port.

    SAP HANA Database dialog box
  3. Optionally, if you want to import data using native database query, click Advanced options and in the SQL Statement box enter the query.

  4. Click OK.

  5. If the SAP HANA server requires database user credentials, then in the Access an SAP HANA database dialog box, do the following:

    1. Click the Database tab, and enter your username and password.

    2. Click Connect.

  1. Click on the Data tab, then Get Data > From Other Sources > From Active Directory. If you don't see the Get Data button, then click on New Query > From Other Sources > Active Directory.

  2. Enter your domain in the Active Directory dialog box.

  3. In the Active Directory Domain dialog box for your domain, click Use my current credentials, or Use alternate credentials. For Use alternate credenitals authentication, enter your Username and Password.

  4. Click Connect.

  5. After the connection succeeds, you can use the Navigator pane to browse all the domains available within your Active Directory, and drill down into Active Directory information including Users, Accounts, and Computers.

Note:  If this is the first time you've connected to Facebook, you will be asked to provide credentials. Sign in using your Facebook account, and allow access to the Power Query application. You can turn off future prompts by clicking the Don't warn me again for this connector option.

  1. On the Data tab, click Get Data > From Online Services > From Facebook. If you don't see the Get Data button, click New Query > From Other Sources > From Facebook.

  2. In the Facebook dialog box, connect to Facebook using “Me”, your Username, or Object ID.

    Note:  Your Facebook username is different from your login email.

  3. Select a category to connect to from the Connection drop-down list. For example, select Friends to give you access to all information available in your Facebook Friends category.

  4. Click OK.

  5. If necessary, click Sign in from the Access Facebook dialog, then enter your Facebook email or phone number, and password. You can check the option to remain logged in. Once signed in, click Connect.

  6. After the connection succeeds, you will be able to preview a table containing information about the selected category. For instance, if you select the Friends category, Power Query renders a table containing your Facebook friends by name.

  7. Click Load or Edit.

You can use the Query Editor to write formulas for Power Query.

= Facebook.Graph("https://graph.facebook.com/v2.8/me/friends")

Power Query Editor with Facebook formula

Note: The Query Editor only appears when you load, edit, or create a new query. To view the Query Editor without loading or editing an existing workbook query, on the Get & Transform ribbon tab, click Get Data > Launch Power Query Editor.

Warning: 

  • Before you can connect to an IBM DB2 database, you need the IBM DB2 Data Server Driver installed on your computer (minimum requirement is the IBM Data Server Driver Package (DS Driver)). Select the driver that matches your Power Query installation (32-bit or 64-bit).

  • There are known issues reported by IBM installing the IBM DB2 Data Server Driver on Windows 8. If you are using Windows 8 and want to connect to IBM DB2 using Power Query, you need to follow additional installation steps. Find more information about the IBM DB2 Data Server Driver on Windows 8..

  1. Click on the Data tab, then Get Data > From Database > From IBM DB2 Database. If you don't see the Get Data button, then click New Query > From Database > From IBM DB2 Database.

  2. In the IBM DB2 Database dialog box, in Server Name specify the IBM DB2 Database Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the IBM DB2 server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. Click on the Data tab, then Get Data > From Other Sources > From OData Feed. If you don't see the Get Data button, then click New Query > From Other Sources > From OData Feed.

  2. In the OData Feed dialog box, enter the URL for an OData feed.

  3. Select OK.

  4. If the OData feed requires user credentials, in the Access an OData feed dialog box:

    1. Select Windows if the OData feed requires Windows Authentication.

    2. Select Basic if the OData feed requires your username and password.

    3. Select Marketplace key if the OData feed requires a Marketplace account key. You can select the Get your Marketplace Account Key to subscribe to Microsoft Azure marketplace OData feeds. You can also Sign up for Microsoft Azure Marketplace from the Access an OData Feed dialog box.

    4. Click Organizational account if the OData feed requires federated access credentials. For Windows Live ID, log into your account.

    5. Select Save.

Note:  Connect to an OData feed supports the JSON light data service format.

  1. Navigate to https://web.powerapps.com/

  2. Select the Environment you want to connect to.

  3. In the menu, select the Settings icon > Advanced customizations > Developer resources.

  4. Copy the Instance Web API value. 

    Notes: 

    • The url format will be something like https://<tenant>.crm.dynamics.com/api/data/v9.0.

    • The exact format of the URL you will use to connect depends on your region and the version of CDS for Apps you're using. For more information see: Web API URL and versions.

  5. Select the Data tab, then Get & Transform Data > Get Data > From Online Services > From Dynamics 365 (online).

  6. In the dialog box, with the Basic option selected, enter the Web API URL for your CDS for Apps connection, and click OK.

    • If you select the Advanced option, you can append certain additional parameters to the query to control what data is returned. For more information see: Query Data using the Web API

  7. Select Organization account.

    • If you aren't signed in using the Microsoft Work or School account you use to access CDS for Apps, click Sign in and enter the account username and password.

  8. Click Connect.

  9. Within the Navigator dialog, select the data you want to retrieve.

  10. If the data is good to be imported as is, then select the Load option, otherwise choose the Edit option to open the Power Query Editor.

    Note: The Power Query Editor gives you multiple options to modify the data returned. For instance, you might want to import fewer columns than your source data contains. In that case, go to the Home tab > Manage Columns > Choose Columns, select the columns you want to keep, then click OK. When you're ready, click Close & Load to return the modified data to Excel.

Note: Before you can connect to a Teradata database, you need the .NET Data Provider for Teradata on your computer.

  1. Click the Data tab, then Get & Transform Data > Get Data > From Database > From Teradata Database.

  2. In the Teradata Database dialog box, in Server Name specify the Teradata Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the Teradata server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Save.

  1. Click the Data tab, then Get Data > From Other Sources > From Microsoft Exchange. If you don't see the Get Data button, the click New Query > From Other Sources > From Microsoft Exchange.

  2. In the Access an Exchange Server dialog box, specify your Email Address and Password.

  3. Click Save.

  4. In the Microsoft Exchange Autodiscover Service dialog box, select Allow to allow the Exchange Service to trust your credentials.

Notes: 

  • Before you can connect to an SAP SQL Anywhere database, you need the  SAP SQL Anywhere driver installed on your computer. Select the driver that matches your Excel installation (32-bit or 64-bit).

  1. Click the Data tab, then Get Data > From Database > From Sybase Database. If you don't see the Get Data button, click New Query > From Database > From Sybase Database.

  2. In the Sybase Database dialog box, specify the Sybase Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. By default, the Encrypt connection check box is selected so that Power Query connects to your database using a simple encrypted connection.

  6. Click Connect.

Microsoft Azure Blob Storage is a service for storing large amounts of unstructured data, like images, videos, audio, and documents, which can be accessed from anywhere in the world via HTTP or HTTPS. For more information about Azure Blob Storage service, see How to Use Blob Storage.

  1. Click the Data tab, then Get & Transform Data > Get Data > From Azure > From Azure Blob Storage. If you don't see the Get Data button, then click New Query > From Azure > From Microsoft Azure Blob Storage.

  2. In the Microsoft Azure Blob Storage dialog box, enter your Microsoft Azure storage account name or URL, and then click OK.

  3. If you are connecting to the Blob storage service for the first time, you will be prompted to enter and save the storage access key. In the Access Microsoft Azure Blob Storage dialog box, enter your storage access key in the Account Key box, and click Save.

    Note:  If you need to retrieve your storage access key, browse to the Microsoft Azure Portal, select your storage account, and then click on the Manage Access Key icon on the bottom of the page. Click on the copy icon to the right of the primary key, and then paste the value in the Account Key box.

  4. The Query Editor lists all the available containers in your Microsoft Azure Blob Storage. In the Navigator, select a container from where you want to import data, and then click Apply & Close.

  1. Go to Data > Get External Data > From Database > From Analysis Services. If you don't see the Get Data button, click Get External Data > From Other Sources > From Analysis Services.

    The Data Connection Wizard is displayed. This wizard has three panes.

    • Connect to Database Server

    • Select Database and Table

    • Save Data Connection File and Finish

  2. In the Connect to Database Server pane, in the Server name box, type the name of the OLAP database server.

    Tip: If you know the name of the offline cube file that you want to connect to, you can type the complete file path, file name, and extension.

  3. Under Log on credentials, do one of the following, then click Next:

    • To use your current Windows user name and password, click Use Windows Authentication.

    • To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password boxes.

  4. In the Select the database that contains the data you want pane, select a database, then click Next.

    To connect to a specific cube in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.

  5. In the Save Data Connection File and Finish pane, in the File Name box, revise the default file name as needed (optional).

  6. Click Browse to change the default file location of My Data Sources, or check for existing file names.

  7. In the Description, Friendly Name, and Search Keywords boxes, type a description of the file, a friendly name, and common search words (all are optional).

  8. To ensure that the connection file is used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data.

    Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.

  9. Click Finish to close the Data Connection Wizard.

  10. In the Import Data dialog box, under Select how you want to view this data in your workbook, do one of the following:

    • To create just a PivotTable report, click PivotTable Report.

    • To create a PivotTable report and a PivotChart report, click PivotChart and PivotTable Report.

    • To store the selected connection in the workbook for later use, click Only Create Connection. This check box ensures that the connection is used by formulas that contain Cube functions that you create and that you don't want to create a PivotTable report.

  11. Under Where do you want to put the data, do one of the following:

    • To place the PivotTable report in an existing worksheet, select Existing worksheet, and then type the cell reference of the first cell in the range of cells where you want to locate the PivotTable report.

      You can also click Collapse Dialog Button image to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog Button image .

    • To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.

    • To verify or change connection properties, click Properties, make the necessary changes in the Connection Properties dialog box, and then click OK.

  1. Click the Data tab, then Get Data > From Other Sources > From ODBC. If you don't see the Get Data button, go to New Query > From Other Sources > From ODBC.

  2. In the From ODBC dialog, if displayed, select your Data Source Name (DSN).

  3. Enter your connection string, then press OK.

  4. In the next dialog box, select from Default or Custom, Windows, or Database connection options, enter your credentials, then press Connect.

  5. In the Navigator pane, select the tables or queries that you want to connect to, then press Load or Edit.

You can use the Power Query add-in to connect to external data sources and perform advanced data analyses. The following sections provide steps for connecting to your data sources - web pages, text files, databases, online services, and Excel files, tables, and ranges.

Important: Before you can use Power Query in Excel 2013, you must activate it: click File > Options > Add-Ins. In the Manage section at the bottom, choose the COM Add-ins option from the drop-down list, then click Go. Click the Power Query check box, then OK. The Power Query ribbon should appear automatically, but if it doesn't, close and restart Excel.

You use the Query Editor to create formulas to connect to JSON files. In the Power Query ribbon tab, click From Other Sources > Blank Query, then go to Advanced Editor and input your custom query string.

let

    Source = Json.Document(File.Contents("C:\Users\Name\Desktop\JSONTest.json")),
    #"Converted to Table" = Record.ToTable(Source)

in

    #"Converted to Table"

  1. Click the Power Query ribbon, then From Web.

  2. In the From Web dialog box, enter a web page URL, then OK.

    Power Query > From Web > Input URL dialog

    In this case, we're using: http://en.wikipedia.org/wiki/UEFA_European_Football_Championship.

    If the web page requires user credentials:

    • In the Access Web dialog box, click a credentials option, and provide authentication values.

    • Click Save.

  3. Click OK.

  4. Power Query will analyze the web page, and load the Navigator pane in Table View.

    If you know which table you want to connect to, then click it from the list. For this example, we chose the Results table.

    Power Query > From Web > Navigator Table View

    Otherwise, you can switch to the Web View and pick the appropriate table manually. In this case, we've selected the Results table.

    Power Query > From Web > Navigator > Web View
  5. Click Load, and Power Query will load the web data you selected into Excel.

  1. In the Power Query ribbon tab, select From Database > From Access Database.

    Get data From Database dialog

  2. In the Browse dialog box, browse for or type a file URL to import or link to a file.

  3. Follow the steps in the Navigator dialog to connect to the table or query of your choice.

  4. Click Load or Edit.

  1. In the Power Query ribbon tab, click From Database > From SQL Server Database.

    Power Query From Database options
  2. In the Microsoft SQL Database dialog box, specify the SQL Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

    Power Query SQL Server Database connection dialog
  4. Select OK.

  5. Select the authentication mode to connect to the SQL Server database.

    Power Query SQL Server connection login credentials
    1. Windows: This is the default selection. Select this if you want to connect using Windows authentication.

    2. Database: Select this if you want to connect using SQL Server authentication. After you select this, specify a user name and password to connect to your SQL Server instance.

  6. By default, the Encrypt connection check box is selected to signify that Power Query connects to your database using an encrypted connection. If you do not want to connect using an encrypted connection, clear this check box, and then click Connect.

    If a connection to your SQL Server is not established using an encrypted connection, Power Query prompts you to connect using an unencrypted connection. Click OK in the message to connect using an unencrypted connection.

Formula Example

You can also use the Query Editor to write formulas for Power Query.

= Sql.Databases(".")
= Sql.Database(".","Contoso")

  1. In the Power Query ribbon tab, click From File > From XML.

    Power Query From File dialog
  2. In the From XML Browse dialog box, browse for or type a file URL to import or link to a file.

  3. Click Open.

    After the connection succeeds, you will be able to use the Navigator pane to browse and preview the collections of items in the XML file in a tabular form.

You can also use the Query Editor to write formulas for Power Query. For example:

= Xml.Tables(File.Contents("C:\Downloads\XML Example.xml"))

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

  1. In the Power Query ribbon tab, click From Database > From MySQL Database.

    Power Query From Database options
  2. In the MySQL Database dialog box, in Server Name specify the MySQL Database Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the MySQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. Select any cell within your data range.

  2. In the Power Query ribbon tab, click From Table.

    Connect to an Excel Data Table

  3. If prompted, in the From Table dialog box, you can click the Range Selection button to select a specific range to use as a data source.

    From Table dialog

  4. If the range of data has column headers, you can check My table has headers. The range header cells are used to set the column names for the query.

  5. In the Query Editor, click Close & Load.

Note: If your data range has been defined as a named range, or is in an Excel table, then Power Query will automatically sense the entire range and load it into the Query Editor for you. Plain data will automatically be converted to a table when it is loaded into the Query Editor.

You can use the Query Editor to write formulas for Power Query.

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Query Editor Formula Sample

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

  1. In the Power Query ribbon tab, click From File > From Excel.

  2. In the Excel Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

    If your source workbook has named ranges, the name of the range will be available as a data set.

You can also use the Query Editor to write formulas for Power Query. For example:

= Excel.Workbook
 (File.Contents("C:\Example\Products and Orders.xlsx"))

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window in Excel 2013 appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

Note: While trying to import data from a legacy Excel file or an Access database in certain setups, you may encounter an error that the Microsoft Access Database Engine (Microsoft.ACE.OLEDB.12.0 provider) is not registered on the local machine. The error occurs on systems with only Office 2013 installed. To resolve this error, download the following resources to ensure that you can proceed with the data sources you are trying to access.

  1. In the Power Query ribbon tab, click From File > From CSV, or From Text.

  2. In the Comma-Separated Values Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

Note: If you are importing data from a CSV file, Power Query will automatically detect column delimiters including column names and types. For example, if you imported the example CSV file below, Power Query automatically uses the first row as the column names and changes each column data type.

Example CSV file

Image of a CSV file

Power Query automatically changes each column data type:

  • Order ID changes to number

  • Order Date changes to date

  • Category remains text (the default column type)

  • Product Name remains text (the default column type)

  • Sales changes to number

In the Query Editor, Power Query automatically applies a FirstRowAsHeader step and ChangeType step. These automatic actions are equivalent to manually promoting a row and manually changing each column type.

After Power Query auto detects columns, you can also use the Query Editor to write formulas for Power Query. For example:

= Csv.Document(File.Contents("C:\Examples\Products Categories and Orders.csv"),null,",",null,1252)

= Table.PromoteHeaders(Source)

= Table.TransformColumnTypes(FirstRowAsHeader,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window in Excel 2013 appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

  1. In the Power Query ribbon tab, select From Other Sources > From SharePoint List.

    Power Query get data from Other Sources dialog
  2. In the Microsoft SharePoint Lists dialog box that appears, enter the URL for a SharePoint site.

    Note: When connecting to a SharePoint list, enter the site URL instead of the list URL. In the Access SharePoint dialog box, select the most general URL to authenticate against the site correctly. By default, the most general URL is selected.

  3. Select OK to continue.

  4. In the Access SharePoint dialog box that appears next, select a credentials option:

    1. Select Anonymous if the SharePoint Server does not require any credentials.

    2. Select Windows if the SharePoint Server requires your Windows credentials.

    3. Select Organizational account if the SharePoint Server requires organizational account credentials.

  5. Select Connect.

    Excel Power Query connect to a Sharepoint List Connect dialog

Note: Before you can connect to an Oracle database using Power Query, you need the Oracle client software v8.1.7 or greater on your computer. To install the Oracle client software, go to 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio (12.1.0.2.4) to install the 32-bit Oracle client, or to 64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64 to install the 64-bit Oracle client.

  1. In the Power Query ribbon tab, click From Database > From Oracle Database.

    Power Query From Database options
  2. In the Oracle Database dialog box, in Server Name specify the Oracle Server to connect to. If a SID is required, this can be specified in the form of “ServerName/SID”.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the Oracle server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

Note: Before you can connect to a PostgreSQL database in Power Query, you need the Ngpsql data provider for PostgreSQL installed on your computer. Select the driver that matches your Office version (32-bit or 64-bit). See: Which version of Office am I using? for more information. Also make sure you have the provider registered in the machine configuration that matches the most recent .NET version on your device.

  1. In the Power Query ribbon tab, select From Database > From PostgreSQL Database.

    Power Query From Database options
  2. In the PostgreSQL Database dialog box, specify the PostgreSQL Database Server you want to connect to in the Server Name section.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the PostgreSQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Select Connect.

Important: Make sure you have downloaded and installed the Power Query Add-In.

  1. In the Power Query ribbon tab, click From Other Sources > From Active Directory.

    Power Query get data from Other Sources dialog

  2. Enter your domain in the Active Directory dialog box.

  3. In the Active Directory Domain dialog box for your domain, click Use my current credentials, or Use alternate credentials. For Use alternate credenitals authentication, enter your Username and Password.

  4. Click Connect.

  5. After the connection succeeds, you can use the Navigator pane to browse all the domains available within your Active Directory, and drill down into Active Directory information including Users, Accounts, and Computers.

Note:  If this is the first time you've connected to Facebook, you will be asked to provide credentials. Sign in using your Facebook account, and allow access to the Power Query application. You can turn off future prompts by clicking the Don't warn me again for this connector option.

  1. In the Power Query ribbon tab, click From Other Sources > From Facebook.

  2. In the Facebook dialog box, connect to Facebook using “Me”, your Username, or Object ID.

    Note:  Your Facebook username is different from your login email.

  3. Select a category to connect to from the Connection drop-down list. For example, select Friends to give you access to all information available in your Facebook Friends category.

  4. Click OK.

  5. If necessary, click Sign in from the Access Facebook dialog, then enter your Facebook email or phone number, and password. You can check the option to remain logged in. Once signed in, click Connect.

  6. After the connection succeeds, you will be able to preview a table containing information about the selected category. For instance, if you select the Friends category, Power Query renders a table containing your Facebook friends by name.

  7. Click Load or Edit.

You can use the Query Editor to write formulas for Power Query.

= Facebook.Graph("https://graph.facebook.com/v2.8/me/friends")

Power Query Editor with Facebook formula

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

Warning: 

  • Before you can connect to an IBM DB2 database, you need the IBM DB2 Data Server Driver installed on your computer (minimum requirement is the IBM Data Server Driver Package (DS Driver)). Select the driver that matches your Power Query installation (32-bit or 64-bit).

  • There are known issues reported by IBM installing the IBM DB2 Data Server Driver on Windows 8. If you are using Windows 8 and want to connect to IBM DB2 using Power Query, you need to follow additional installation steps. Find more information about the IBM DB2 Data Server Driver on Windows 8..

  1. In the Power Query ribbon tab, click From Database > From IBM DB2 Database.

    Power Query From Database options
  2. In the IBM DB2 Database dialog box, in Server Name specify the IBM DB2 Database Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the IBM DB2 server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. In the Power Query ribbon tab, select From Other Source > From OData Feed.

    Power Query get data from Other Sources dialog
  2. In the OData Feed dialog box, enter the URL for an OData feed.

  3. Select OK.

  4. If the OData feed requires user credentials, in the Access an OData feed dialog box:

    1. Select Windows if the OData feed requires Windows Authentication.

    2. Select Basic if the OData feed requires your username and password.

    3. Select Marketplace key if the OData feed requires a Marketplace account key. You can select the Get your Marketplace Account Key to subscribe to Microsoft Azure marketplace OData feeds. You can also Sign up for Microsoft Azure Marketplace from the Access an OData Feed dialog box.

    4. Click Organizational account if the OData feed requires federated access credentials. For Windows Live ID, log into your account.

    5. Select Save.

Note:  Connect to an OData feed supports the JSON light data service format.

  1. Navigate to https://web.powerapps.com/

  2. Select the Environment you want to connect to.

  3. In the menu, select the Settings icon > Advanced customizations > Developer resources.

  4. Copy the Instance Web API value. 

    Notes: 

    • The url format will be something like https://<tenant>.crm.dynamics.com/api/data/v9.0.

    • The exact format of the URL you will use to connect depends on your region and the version of CDS for Apps you're using. For more information see: Web API URL and versions.

  5. Select the Data tab, then Get Data > From Online Services > From Dynamics 365 (online).

    • If you don't see the Get Data button, click New Query > From Other Sources > From Dynamics 365 (online).

  6. In the dialog box, with the Basic option selected, enter the Web API URL for your CDS for Apps connection, and click OK.

    • If you select the Advanced option, you can append certain additional parameters to the query to control what data is returned. For more information see: Query Data using the Web API

  7. Select Organization account.

    • If you aren't signed in using the Microsoft Work or School account you use to access CDS for Apps, click Sign in and enter the account username and password.

  8. Click Connect.

  9. Within the Navigator dialog, select the data you want to retrieve.

  10. If the data is good to be imported as is, then select the Load option, otherwise choose the Edit option to open the Power Query Editor.

    Note: The Power Query Editor gives you multiple options to modify the data returned. For instance, you might want to import fewer columns than your source data contains. In that case, go to the Home tab > Manage Columns > Choose Columns, select the columns you want to keep, then click OK. When you're ready, click Close & Load to return the modified data to Excel.

Note: Before you can connect to a Teradata database, you need the .NET Data Provider for Teradata on your computer.

  1. In the Power Query ribbon tab, select From Database > From Teradata Database.

    Power Query From Database options
  2. In the Teradata Database dialog box, in Server Name specify the Teradata Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the Teradata server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Save.

  1. In the Power Query ribbon tab, select From Other Sources > From Microsoft Exchange.

    Power Query Data Sources
  2. In the Access an Exchange Server dialog box, specify your Email Address and Password.

  3. Click Save.

  4. In the Microsoft Exchange Autodiscover Service dialog box, select Allow to allow the Exchange Service to trust your credentials.

Notes: 

  • Before you can connect to an SAP SQL Anywhere database, you need the  SAP SQL Anywhere driver installed on your computer. Select the driver that matches your Power Query installation (32-bit or 64-bit).

  1. In the POWER QUERY ribbon tab, select From Database > From SAP Sybase SQL Anywhere.

    Get External Data from a Database
  2. In the Sybase Database dialog box, specify the Sybase Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. By default, the Encrypt connection check box is selected so that Power Query connects to your database using a simple encrypted connection.

  6. Click Connect.

Microsoft Azure Blob Storage is a service for storing large amounts of unstructured data, like images, videos, audio, and documents, which can be accessed from anywhere in the world via HTTP or HTTPS. For more information about Azure Blob Storage service, see How to Use Blob Storage.

  1. In the Power Query ribbon tab, select From Azure > From Microsoft Azure Blob Storage.

    Power Query Import from Azure dialog
  2. In the Microsoft Azure Blob Storage dialog box, enter your Microsoft Azure storage account name or URL, and then click OK.

  3. If you are connecting to the Blob storage service for the first time, you will be prompted to enter and save the storage access key. In the Access Microsoft Azure Blob Storage dialog box, enter your storage access key in the Account Key box, and click Save.

    Note:  If you need to retrieve your storage access key, browse to the Microsoft Azure Portal, select your storage account, and then click on the Manage Access Key icon on the bottom of the page. Click on the copy icon to the right of the primary key, and then paste the value in the Account Key box.

  4. The Query Editor lists all the available containers in your Microsoft Azure Blob Storage. In the Navigator, select a container from where you want to import data, and then click Apply & Close.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.

    Excel  Ribbon Image

    The Data Connection Wizard is displayed. This wizard has three panes.

    • Connect to Database Server

    • Select Database and Table

    • Save Data Connection File and Finish

  2. In the Connect to Database Server pane, in the Server name box, type the name of the OLAP database server.

    Tip: If you know the name of the offline cube file that you want to connect to, you can type the complete file path, file name, and extension.

  3. Under Log on credentials, do one of the following, then click Next:

    • To use your current Windows user name and password, click Use Windows Authentication.

    • To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password boxes.

  4. In the Select the database that contains the data you want pane, select a database, then click Next.

    To connect to a specific cube in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.

  5. In the Save Data Connection File and Finish pane, in the File Name box, revise the default file name as needed (optional).

    Click Browse to change the default file location of My Data Sources, or check for existing file names.

  6. In the Description, Friendly Name, and Search Keywords boxes, type a description of the file, a friendly name, and common search words (all are optional).

  7. To ensure that the connection file is used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data.

    Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.

  8. Click Finish to close the Data Connection Wizard.

  9. In the Import Data dialog box, under Select how you want to view this data in your workbook, do one of the following:

    • To create just a PivotTable report, click PivotTable Report.

    • To create a PivotTable report and a PivotChart report, click PivotChart and PivotTable Report.

    • To store the selected connection in the workbook for later use, click Only Create Connection. This check box ensures that the connection is used by formulas that contain Cube functions that you create and that you don't want to create a PivotTable report.

  10. Under Where do you want to put the data, do one of the following:

    • To place the PivotTable report in an existing worksheet, select Existing worksheet, and then type the cell reference of the first cell in the range of cells where you want to locate the PivotTable report.

      You can also click Collapse Dialog Button image to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog Button image .

  11. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.

  12. To verify or change connection properties, click Properties, make the necessary changes in the Connection Properties dialog box, and then click OK.

  1. Click the Power Query tab on the Ribbon, then select Get External Data > From Other Sources > From ODBC.

  2. In the From ODBC dialog, if displayed, select your Data Source Name (DSN).

  3. Enter your connection string, then press OK.

  4. In the next dialog box, select from Default or Custom, Windows, or Database connection options, enter your credentials, then press Connect.

  5. In the Navigator pane, select the tables or queries that you want to connect to, then press Load or Edit.

You can use the Power Query add-in to connect to external data sources and perform advanced data analyses. The following sections provide steps for connecting to your data sources - web pages, text files, databases, online services, and Excel files, tables, and ranges.

Important: 

  • Make sure you have downloaded, installed, and activated the Power Query Add-In.

  • To activate the Power Query add-in, click File > Options > Add-Ins. In the Manage section at the bottom, choose the COM Add-ins option from the drop-down list, then click Go. Click the Power Query check box, then OK. The Power Query ribbon should appear automatically, but if it doesn't, close and restart Excel.

You use the Query Editor to create formulas to connect to JSON files. In the Power Query ribbon tab, click From Other Sources > Blank Query, then go to Advanced Editor and input your custom query string.

let

    Source = Json.Document(File.Contents("C:\Users\Name\Desktop\JSONTest.json")),
    #"Converted to Table" = Record.ToTable(Source)

in

    #"Converted to Table"

  1. Click the Power Query ribbon, then From Web.

  2. In the From Web dialog box, enter a web page URL, then OK.

    Power Query > From Web > Input URL dialog

    In this case, we're using: http://en.wikipedia.org/wiki/UEFA_European_Football_Championship.

    If the web page requires user credentials:

    • In the Access Web dialog box, click a credentials option, and provide authentication values.

    • Click Save.

  3. Click OK.

  4. Power Query will analyze the web page, and load the Navigator pane in Table View.

    If you know which table you want to connect to, then click it from the list. For this example, we chose the Results table.

    Power Query > From Web > Navigator Table View

    Otherwise, you can switch to the Web View and pick the appropriate table manually. In this case, we've selected the Results table.

    Power Query > From Web > Navigator > Web View
  5. Click Load, and Power Query will load the web data you selected into Excel.

  1. In the Power Query ribbon tab, select From Database > From Access Database.

    Get data From Database dialog

  2. In the Browse dialog box, browse for or type a file URL to import or link to a file.

  3. Follow the steps in the Navigator dialog to connect to the table or query of your choice.

  4. Click Load or Edit.

  1. In the Power Query ribbon tab, click From Database > From SQL Server Database.

    Power Query From Database options
  2. In the Microsoft SQL Database dialog box, specify the SQL Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

    Power Query SQL Server Database connection dialog
  4. Select OK.

  5. Select the authentication mode to connect to the SQL Server database.

    Power Query SQL Server connection login credentials
    1. Windows: This is the default selection. Select this if you want to connect using Windows authentication.

    2. Database: Select this if you want to connect using SQL Server authentication. After you select this, specify a user name and password to connect to your SQL Server instance.

  6. By default, the Encrypt connection check box is selected to signify that Power Query connects to your database using an encrypted connection. If you do not want to connect using an encrypted connection, clear this check box, and then click Connect.

    If a connection to your SQL Server is not established using an encrypted connection, Power Query prompts you to connect using an unencrypted connection. Click OK in the message to connect using an unencrypted connection.

Formula Example

You can also use the Query Editor to write formulas for Power Query.

= Sql.Databases(".")
= Sql.Database(".","Contoso")

  1. In the Power Query ribbon tab, click From File > From XML.

    Power Query From File dialog
  2. In the From XML Browse dialog box, browse for or type a file URL to import or link to a file.

  3. Click Open.

    After the connection succeeds, you will be able to use the Navigator pane to browse and preview the collections of items in the XML file in a tabular form.

You can also use the Query Editor to write formulas for Power Query. For example:

= Xml.Tables(File.Contents("C:\Downloads\XML Example.xml"))

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

  1. In the Power Query ribbon tab, click From Database > From MySQL Database.

    Power Query From Database options
  2. In the MySQL Database dialog box, in Server Name specify the MySQL Database Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the MySQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. Select any cell within your data range.

  2. In the Power Query ribbon tab, click From Table.

    Connect to an Excel Data Table

  3. If prompted, in the From Table dialog box, you can click the Range Selection button to select a specific range to use as a data source.

    From Table dialog

  4. If the range of data has column headers, you can check My table has headers. The range header cells are used to set the column names for the query.

  5. In the Query Editor, click Close & Load.

Note: If your data range has been defined as a named range, or is in an Excel table, then Power Query will automatically sense the entire range and load it into the Query Editor for you. Plain data will automatically be converted to a table when it is loaded into the Query Editor.

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

You can use the Query Editor to write formulas for Power Query.

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Query Editor Formula Sample

  1. In the Power Query ribbon tab, click From File > From Excel.

  2. In the Excel Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

    If your source workbook has named ranges, the name of the range will be available as a data set.

You can also use the Query Editor to write formulas for Power Query. For example:

= Excel.Workbook
 (File.Contents("C:\Example\Products and Orders.xlsx"))

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window in Excel 2013 appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

  1. In the Power Query ribbon tab, click From File > From CSV or From Text.

  2. In the Comma-Separated Values Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

Note: If you are importing data from a CSV file, Power Query will automatically detect column delimiters including column names and types. For example, if you imported the example CSV file below, Power Query automatically uses the first row as the column names and changes each column data type.

Example CSV file

Image of a CSV file

Power Query automatically changes each column data type:

  • Order ID changes to number

  • Order Date changes to date

  • Category remains text (the default column type)

  • Product Name remains text (the default column type)

  • Sales changes to number

In the Query Editor, Power Query automatically applies a FirstRowAsHeader step and ChangeType step. These automatic actions are equivalent to manually promoting a row and manually changing each column type.

After Power Query auto detects columns, you can also use the Query Editor to write formulas for Power Query. For example:

= Csv.Document(File.Contents("C:\Examples\Products Categories and Orders.csv"),null,",",null,1252)

= Table.PromoteHeaders(Source)

= Table.TransformColumnTypes(FirstRowAsHeader,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})

  1. In the Power Query ribbon tab, select From Other Sources > From SharePoint List.

    Power Query get data from Other Sources dialog
  2. In the Microsoft SharePoint Lists dialog box that appears, enter the URL for a SharePoint site.

    Note: When connecting to a SharePoint list, enter the site URL instead of the list URL. In the Access SharePoint dialog box, select the most general URL to authenticate against the site correctly. By default, the most general URL is selected.

  3. Select OK to continue.

  4. In the Access SharePoint dialog box that appears next, select a credentials option:

    1. Select Anonymous if the SharePoint Server does not require any credentials.

    2. Select Windows if the SharePoint Server requires your Windows credentials.

    3. Select Organizational account if the SharePoint Server requires organizational account credentials.

  5. Select Connect.

    Excel Power Query connect to a Sharepoint List Connect dialog

Note: Before you can connect to an Oracle database using Power Query, you need the Oracle client software v8.1.7 or greater on your computer. To install the Oracle client software, go to 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio (12.1.0.2.4) to install the 32-bit Oracle client, or to 64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64 to install the 64-bit Oracle client.

  1. In the Power Query ribbon tab, click From Database > From Oracle Database.

    Power Query From Database options
  2. In the Oracle Database dialog box, in Server Name specify the Oracle Server to connect to. If a SID is required, this can be specified in the form of “ServerName/SID”.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the Oracle server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

Note: Before you can connect to a PostgreSQL database in Power Query, you need the Ngpsql data provider for PostgreSQL installed on your computer. Select the driver that matches your Office version (32-bit or 64-bit). See: Which version of Office am I using? for more information. Also make sure you have the provider registered in the machine configuration that matches the most recent .NET version on your device.

  1. In the Power Query ribbon tab, select From Database > From PostgreSQL Database.

    Power Query From Database options
  2. In the PostgreSQL Database dialog box, specify the PostgreSQL Database Server you want to connect to in the Server Name section.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the PostgreSQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Select Connect.

Important: Make sure you have downloaded and installed the Power Query Add-In.

  1. In the Power Query ribbon tab, click From Other Sources > From Active Directory.

    Power Query get data from Other Sources dialog

  2. Enter your domain in the Active Directory dialog box.

  3. In the Active Directory Domain dialog box for your domain, click Use my current credentials, or Use alternate credentials. For Use alternate credenitals authentication, enter your Username and Password.

  4. Click Connect.

  5. After the connection succeeds, you can use the Navigator pane to browse all the domains available within your Active Directory, and drill down into Active Directory information including Users, Accounts, and Computers.

Note:  If this is the first time you've connected to Facebook, you will be asked to provide credentials. Sign in using your Facebook account, and allow access to the Power Query application. You can turn off future prompts by clicking the Don't warn me again for this connector option.

  1. In the Power Query ribbon tab, click From Other Sources > From Facebook.

  2. In the Facebook dialog box, connect to Facebook using “Me”, your Username, or Object ID.

    Note:  Your Facebook username is different from your login email.

  3. Select a category to connect to from the Connection drop-down list. For example, select Friends to give you access to all information available in your Facebook Friends category.

  4. Click OK.

  5. If necessary, click Sign in from the Access Facebook dialog, then enter your Facebook email or phone number, and password. You can check the option to remain logged in. Once signed in, click Connect.

  6. After the connection succeeds, you will be able to preview a table containing information about the selected category. For instance, if you select the Friends category, Power Query renders a table containing your Facebook friends by name.

  7. Click Load or Edit.

You can use the Query Editor to write formulas for Power Query.

= Facebook.Graph("https://graph.facebook.com/v2.8/me/friends")

Power Query Editor with Facebook formula

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

Warning: 

  • Before you can connect to an IBM DB2 database, you need the IBM DB2 Data Server Driver installed on your computer (minimum requirement is the IBM Data Server Driver Package (DS Driver)). Select the driver that matches your Power Query installation (32-bit or 64-bit).

  • There are known issues reported by IBM installing the IBM DB2 Data Server Driver on Windows 8. If you are using Windows 8 and want to connect to IBM DB2 using Power Query, you need to follow additional installation steps. Find more information about the IBM DB2 Data Server Driver on Windows 8..

  1. In the Power Query ribbon tab, click From Database > From IBM DB2 Database.

    Power Query From Database options
  2. In the IBM DB2 Database dialog box, in Server Name specify the IBM DB2 Database Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the IBM DB2 server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. In the Power Query ribbon tab, select From Other Source > From OData Feed.

    Power Query get data from Other Sources dialog
  2. In the OData Feed dialog box, enter the URL for an OData feed.

  3. Select OK.

  4. If the OData feed requires user credentials, in the Access an OData feed dialog box:

    1. Select Windows if the OData feed requires Windows Authentication.

    2. Select Basic if the OData feed requires your username and password.

    3. Select Marketplace key if the OData feed requires a Marketplace account key. You can select the Get your Marketplace Account Key to subscribe to Microsoft Azure marketplace OData feeds. You can also Sign up for Microsoft Azure Marketplace from the Access an OData Feed dialog box.

    4. Click Organizational account if the OData feed requires federated access credentials. For Windows Live ID, log into your account.

    5. Select Save.

Note:  Connect to an OData feed supports the JSON light data service format.

  1. Navigate to https://web.powerapps.com/

  2. Select the Environment you want to connect to.

  3. In the menu, select the Settings icon > Advanced customizations > Developer resources.

  4. Copy the Instance Web API value. 

    Notes: 

    • The url format will be something like https://<tenant>.crm.dynamics.com/api/data/v9.0.

    • The exact format of the URL you will use to connect depends on your region and the version of CDS for Apps you're using. For more information see: Web API URL and versions.

  5. Select the Data tab, then Get Data > From Online Services > From Dynamics 365 (online).

    • If you don't see the Get Data button, click New Query > From Other Sources > From Dynamics 365 (online).

  6. In the dialog box, with the Basic option selected, enter the Web API URL for your CDS for Apps connection, and click OK.

    • If you select the Advanced option, you can append certain additional parameters to the query to control what data is returned. For more information see: Query Data using the Web API

  7. Select Organization account.

    • If you aren't signed in using the Microsoft Work or School account you use to access CDS for Apps, click Sign in and enter the account username and password.

  8. Click Connect.

  9. Within the Navigator dialog, select the data you want to retrieve.

  10. If the data is good to be imported as is, then select the Load option, otherwise choose the Edit option to open the Power Query Editor.

    Note: The Power Query Editor gives you multiple options to modify the data returned. For instance, you might want to import fewer columns than your source data contains. In that case, go to the Home tab > Manage Columns > Choose Columns, select the columns you want to keep, then click OK. When you're ready, click Close & Load to return the modified data to Excel.

Note: Before you can connect to a Teradata database, you need the .NET Data Provider for Teradata on your computer.

  1. In the Power Query ribbon tab, select From Database > From Teradata Database.

    Power Query From Database options
  2. In the Teradata Database dialog box, in Server Name specify the Teradata Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the Teradata server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Save.

  1. In the Power Query ribbon tab, select From Other Sources > From Microsoft Exchange.

    Power Query Data Sources
  2. In the Access an Exchange Server dialog box, specify your Email Address and Password.

  3. Click Save.

  4. In the Microsoft Exchange Autodiscover Service dialog box, select Allow to allow the Exchange Service to trust your credentials.

Notes: 

  • Before you can connect to an SAP SQL Anywhere database, you need the  SAP SQL Anywhere driver installed on your computer. Select the driver that matches your Power Query installation (32-bit or 64-bit).

  1. In the POWER QUERY ribbon tab, select From Database > From SAP Sybase SQL Anywhere.

    Get External Data from a Database
  2. In the Sybase Database dialog box, specify the Sybase Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. By default, the Encrypt connection check box is selected so that Power Query connects to your database using a simple encrypted connection.

  6. Click Connect.

Microsoft Azure Blob Storage is a service for storing large amounts of unstructured data, like images, videos, audio, and documents, which can be accessed from anywhere in the world via HTTP or HTTPS. For more information about Azure Blob Storage service, see How to Use Blob Storage.

  1. In the Power Query ribbon tab, select From Azure > From Microsoft Azure Blob Storage.

    Power Query Import from Azure dialog
  2. In the Microsoft Azure Blob Storage dialog box, enter your Microsoft Azure storage account name or URL, and then click OK.

  3. If you are connecting to the Blob storage service for the first time, you will be prompted to enter and save the storage access key. In the Access Microsoft Azure Blob Storage dialog box, enter your storage access key in the Account Key box, and click Save.

    Note:  If you need to retrieve your storage access key, browse to the Microsoft Azure Portal, select your storage account, and then click on the Manage Access Key icon on the bottom of the page. Click on the copy icon to the right of the primary key, and then paste the value in the Account Key box.

  4. The Query Editor lists all the available containers in your Microsoft Azure Blob Storage. In the Navigator, select a container from where you want to import data, and then click Apply & Close.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.

    Excel  Ribbon Image

    The Data Connection Wizard is displayed. This wizard has three panes.

    • Connect to Database Server

    • Select Database and Table

    • Save Data Connection File and Finish

  2. In the Connect to Database Server pane, in the Server name box, type the name of the OLAP database server.

    Tip: If you know the name of the offline cube file that you want to connect to, you can type the complete file path, file name, and extension.

  3. Under Log on credentials, do one of the following, then click Next:

    • To use your current Windows user name and password, click Use Windows Authentication.

    • To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password boxes.

  4. In the Select the database that contains the data you want pane, select a database, then click Next.

    To connect to a specific cube in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.

  5. In the Save Data Connection File and Finish pane, in the File Name box, revise the default file name as needed (optional).

    Click Browse to change the default file location of My Data Sources, or check for existing file names.

  6. In the Description, Friendly Name, and Search Keywords boxes, type a description of the file, a friendly name, and common search words (all are optional).

  7. To ensure that the connection file is used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data.

    Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.

  8. Click Finish to close the Data Connection Wizard.

  9. In the Import Data dialog box, under Select how you want to view this data in your workbook, do one of the following:

    • To create just a PivotTable report, click PivotTable Report.

    • To create a PivotTable report and a PivotChart report, click PivotChart and PivotTable Report.

    • To store the selected connection in the workbook for later use, click Only Create Connection. This check box ensures that the connection is used by formulas that contain Cube functions that you create and that you don't want to create a PivotTable report.

  10. Under Where do you want to put the data, do one of the following:

    • To place the PivotTable report in an existing worksheet, select Existing worksheet, and then type the cell reference of the first cell in the range of cells where you want to locate the PivotTable report.

      You can also click Collapse Dialog Button image to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog Button image .

  11. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.

  12. To verify or change connection properties, click Properties, make the necessary changes in the Connection Properties dialog box, and then click OK.

  1. Click the Power Query tab on the Ribbon, then select Get External Data > From Other Sources > From ODBC.

  2. In the From ODBC dialog, if displayed, select your Data Source Name (DSN).

  3. Enter your connection string, then press OK.

  4. In the next dialog box, select from Default or Custom, Windows, or Database connection options, enter your credentials, then press Connect.

  5. In the Navigator pane, select the tables or queries that you want to connect to, then press Load or Edit.

Learn More

See Power Query 101 if you'd like to learn more about connecting to a data source, then shaping and transforming data with the Query Editor.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Power Query is known as Get & Transform in Excel 2016

Import data from external data sources

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×