Power Query is a robust tool that can be used to connect to the online services (like, Dynamics 365, SharePoint Online, Azure SQL DB, etc.)
Below are the steps to connect to D365 CE:
- Open Excel
- Click Data > Get Data > From Online Services > From Dynamics 365 (online)
- Go to Advanced Settings in Dynamics 365 CE.
- Select Customizations > Developer Resources
- Fetch Web API URL from developer resources.
- Enter in the dialog box (this is a legacy connector)
- Click Organization Account and Sign in
- Once sign in is successful, click Connect.
- The Navigator will show all the tables that one can use to develop pivot tables, pivot charts etc.
- Select one or multiple tables that you want to use and transform and load the data into Excel.
Here are the steps to use the Dataverse connector.
Hope it helps!