Import data from external data sources (Power Query)

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

2. In the Query Editor, click Advanced Editor.

3. 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"
   

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

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

   

   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.

   

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

   

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.

   

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.

   

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.

   

4. Select OK.

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

   

   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.

   

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"))

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

   

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.

   

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.

   

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.

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

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

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"))

• Microsoft Access Database Engine 2010 Redistributable

• Access Database Engine 2010 Service Pack 1

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.

Example 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 Azure > From Microsoft Azure HDInsight.

   

2. In the Microsoft Azure HDInsight dialog box, enter an Account Name and click OK.

3. Next, enter your Account Key, and click Connect.

   Note:  If you need to retrieve your key, return to the Microsoft Azure Portal, select your storage account, and 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 paste the value into the wizard.

4. Select your cluster in the Navigator dialog, and then find and select a content file.

5. Click Load to load the selected table, or click Edit to perform additional data filters and transformations before loading it.

1. In the Power Query ribbon tab, select 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.

   

1. In the Power Query ribbon tab, click 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.

1. In the Power Query ribbon tab, select 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.

1. In the Power Query ribbon tab, click From Other Sources > From 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.

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")

1. In the Power Query ribbon tab, click 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. In the Power Query ribbon tab, select From Other Source > 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.

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.

1. In the Power Query ribbon tab, select 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. In the Power Query ribbon tab, select 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.

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

   

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.

   

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.

   

   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 to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog .

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.

1. Click the Data tab, then Get Data > From Other Sources > From OLEDB. If you don't see the Get Data button, follow the Data Connection Wizard instructions below.

2. In the From OLE DB dialog, enter your connection string, then press OK.

3. In the OLEDB Provider dialog, select from the Default or Custom, Windows, or Database connection options, enter the appropriate credentials, then click Connect.

4. In the Navigator dialog, select the Database and tables or queries you want to connect to, then press Load or Edit.

5. In the Power Query Editor, press Close & Load.

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

2. In the Query Editor, click Advanced Editor.

3. 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"
   

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

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

   

   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.

   

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

   

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.

   

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.

   

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.

   

4. Select OK.

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

   

   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.

   

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"))

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

   

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.

   

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.

   

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.

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

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

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"))

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.

Example 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 Azure > From Microsoft Azure HDInsight.

   

2. In the Microsoft Azure HDInsight dialog box, enter an Account Name and click OK.

3. Next, enter your Account Key, and click Connect.

   Note:  If you need to retrieve your key, return to the Microsoft Azure Portal, select your storage account, and 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 paste the value into the wizard.

4. Select your cluster in the Navigator dialog, and then find and select a content file.

5. Click Load to load the selected table, or click Edit to perform additional data filters and transformations before loading it.

1. In the Power Query ribbon tab, select 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.

   

1. In the Power Query ribbon tab, click 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.

1. In the Power Query ribbon tab, select 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.

1. In the Power Query ribbon tab, click From Other Sources > From 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.

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")

1. In the Power Query ribbon tab, click 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. In the Power Query ribbon tab, select From Other Source > 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.

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.

1. In the Power Query ribbon tab, select 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. In the Power Query ribbon tab, select 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.

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

   

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.

   

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.

   

   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 to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog .

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.

1. Click the Data tab, then Get Data > From Other Sources > From OLEDB. If you don't see the Get Data button, follow the Data Connection Wizard instructions below.

2. In the From OLE DB dialog, enter your connection string, then press OK.

3. In the OLEDB Provider dialog, select from the Default or Custom, Windows, or Database connection options, enter the appropriate credentials, then click Connect.

4. In the Navigator dialog, select the Database and tables or queries you want to connect to, then press Load or Edit.

5. In the Power Query Editor, press Close & Load.

You can use the Data Connection Wizard to connect to an Access database.

1. On the Data tab, in the Get External Data group, click From Access.

   

2. In the Select Data Source dialog box, browse to the Access database.

3. In the Select Table dialog box, select the tables or queries you want to use, and click OK.

4. You can click Finish, or click Next to change details for the connection.

5. In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart.

6. Click the Properties button to set advanced properties for the connection, such as options for refreshing the connected data.

7. Optionally, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable report.

8. Click OK to finish.

Go to the Data tab > Get External Data > From Text. Then, in the Import Text File dialog box, double-click the text file that you want to import, and the Text Import Wizard dialog will open.

Step 1 of 3

Original data type    If items in the text file are separated by tabs, colons, semicolons, spaces, or other characters, select Delimited. If all of the items in each column are the same length, select Fixed width.

Start import at row    Type or select a row number to specify the first row of the data that you want to import.

File origin    Select the character set that is used in the text file. In most cases, you can leave this setting at its default. If you know that the text file was created by using a different character set than the character set that you are using on your computer, you should change this setting to match that character set. For example, if your computer is set to use character set 1251 (Cyrillic, Windows), but you know that the file was produced by using character set 1252 (Western European, Windows), you should set File Origin to 1252.

Preview of file    This box displays the text as it will appear when it is separated into columns on the worksheet.

Step 2 of 3 (Delimited data)

Delimiters    Select the character that separates values in your text file. If the character is not listed, select the Other check box, and then type the character in the box that contains the cursor. These options are not available if your data type is Fixed width.

Treat consecutive delimiters as one    Select this check box if your data contains a delimiter of more than one character between data fields or if your data contains multiple custom delimiters.

Text qualifier    Select the character that encloses values in your text file. When Excel encounters the text qualifier character, all of the text that follows that character and precedes the next occurrence of that character is imported as one value, even if the text contains a delimiter character. For example, if the delimiter is a comma (,) and the text qualifier is a quotation mark ("), "Dallas, Texas" is imported into one cell as Dallas, Texas. If no character or the apostrophe (') is specified as the text qualifier, "Dallas, Texas" is imported into two adjacent cells as "Dallas and Texas".

If the delimiter character occurs between text qualifiers, Excel omits the qualifiers in the imported value. If no delimiter character occurs between text qualifiers, Excel includes the qualifier character in the imported value. Hence, "Dallas Texas" (using the quotation mark text qualifier) is imported into one cell as "Dallas Texas".

Data preview    Review the text in this box to verify that the text will be separated into columns on the worksheet as you want it.

Step 2 of 3 (Fixed width data)

Data preview    Set field widths in this section. Click the preview window to set a column break, which is represented by a vertical line. Double-click a column break to remove it, or drag a column break to move it.

Step 3 of 3

Click the Advanced button to do one or more of the following:

• Specify the type of decimal and thousands separators that are used in the text file. When the data is imported into Excel, the separators will match those that are specified for your location in Regional and Language Options or Regional Settings (Windows Control Panel).

• Specify that one or more numeric values may contain a trailing minus sign.

Column data format    Click the data format of the column that is selected in the Data preview section. If you do not want to import the selected column, click Do not import column (skip).

After you select a data format option for the selected column, the column heading under Data preview displays the format. If you select Date, select a date format in the Date box.

Choose the data format that closely matches the preview data so that Excel can convert the imported data correctly. For example:

• To convert a column of all currency number characters to the Excel Currency format, select General.

• To convert a column of all number characters to the Excel Text format, select Text.

• To convert a column of all date characters, each date in the order of year, month, and day, to the Excel Date format, select Date, and then select the date type of YMD in the Date box.

Excel will import the column as General if the conversion could yield unintended results. For example:

• If the column contains a mix of formats, such as alphabetical and numeric characters, Excel converts the column to General.

• If, in a column of dates, each date is in the order of year, month, and date, and you select Date along with a date type of MDY, Excel converts the column to General format. A column that contains date characters must closely match an Excel built-in date or custom date formats.

If Excel does not convert a column to the format that you want, you can convert the data after you import it.

• Convert numbers stored as text to numbers

• Convert dates stored as text to dates

• TEXT function

• VALUE function

When you have selected the options you want, click Finish to open the Import Data dialog and choose where to place your data.

Import Data

Set these options to control how the data import process runs, including what data connection properties to use and what file and range to populate with the imported data.

• The options under Select how you want to view this data in your workbook are only available if you have a Data Model prepared and select the option to add this import to that model (see the third item in this list).

• Specify a target workbook:

  • If you choose Existing Worksheet, click a cell in the sheet to place the first cell of imported data, or click and drag to select a range.

  • Choose New Worksheet to import into a new worksheet (starting at cell A1)

• If you have a Data Model in place, click Add this data to the Data Model to include this import in the model. For more information, see Create a Data Model in Excel.

  Note that selecting this option unlocks the options under Select how you want to view this data in your workbook.

• Click Properties to set any External Data Range properties you want. For more information, see Manage external data ranges and their properties.

• Click OK when you're ready to finish importing your data.

The Get & Transform experience was not available in Excel 2007, so you can use an Office Data Connection (.odc) file to connect to a Microsoft SQL Server database from an Excel 2007 workbook. SQL Server is a full-featured, relational database program that is designed for enterprise-wide data solutions that require optimum performance, availability, scalability, and security.

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

   

   The Data Connection Wizard starts. This wizard has three pages.

   Page 1: Connect to Database Server    

2. In step 1, type the name of the SQL Server computer in the Server name box.

3. In step 2, under Log on credentials, do one of the following:

   • To use your current Microsoft 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.

     Security Note: 

     • Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: house1. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better.

     • It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

     Page 2: Select Database and Table    

4. Under Select the database that contains the data you want, select a database. Under Connect to a specific table, select a specific table or view.

   Alternatively, you can clear the Connect to a specific table check box, so that other users who use this connection file will be prompted for the list of tables and views.

   Page 3: Save Data Connection File and Finish    

5. Optionally, in the File Name box, revise the suggested file name. Click Browse to change the default file location (My Data Sources).

6. Optionally, type a description of the file, a friendly name, and common search words in the Description, Friendly Name, and Search Keywords boxes.

7. To ensure that the connection file is always used when the data is updated, click the Always attempt to use this file to refresh this data check box. This check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.

8. To specify how the external data source of a PivotTable report is accessed if the workbook is saved to Excel Services and is opened by using Excel Services, click Authentication Settings, and then select one of the following options to log on to the data source:

   • Windows Authentication     Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can affect performance when many users are connected to the server.

   • SSO     Select this option to use Single Sign On (SSO), and then enter the appropriate identification string in the SSO ID box. A site administrator can configure a Windows SharePoint Services site to use a Single Sign On database in which a user name and password can be stored. This method can be the most efficient when many users are connected to the server.

   • None     Select this option to save the user name and password in the connection file.

     Security Note: Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.

     Note: The authentication setting is used only by Excel Services, and not by Excel.

9. Click OK.

10. Click Finish to close the Data Connection Wizard.

    The Import Data dialog box is displayed.

11. Under Select how you want to view this data in your workbook, do one of the following:

    • To create an Excel table, click Table (this is the default).

    • To create a PivotTable report, click PivotTable Report.

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

      Note: The Only Create Connection option is available only for an OLAP database.

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

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

      Alternatively, click Collapse Dialog to temporarily collapse the dialog box, select the beginning cell on the worksheet, and then click Expand Dialog .

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

13. Optionally, you can change the connection properties (and also change the connection file) by clicking Properties, making your changes in the Connection Properties dialog box, and then clicking OK.

    For more information, see Connection properties.

For Excel 2007, you can use Microsoft Query to connect to ODBC data sources.

Data Connection Wizard

1. On the Data tab, in the Get External Data group, click From Other Sources, and then click 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).

   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 to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog .

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.