Connect to Common Data Service for Apps (Power Query)

Use Excel's Get & Transform (Power Query) experience to connect to your Common Data Service for Apps (CDS for Apps) database. To begin, you need the URL to access the data.

To get the URL from PowerApps portal:

  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.

Connect Using Power Query

  1. 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).

  2. 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

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

  4. Click Connect.

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

  6. 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.

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
Get new features first
Join Office Insiders

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.

×