Using Odata in excel

Dynamics 365 finance and operations offer a variety of options for exporting data for reporting purposes, including a data management framework, an office add-in, and an Odata service. In this blog, we will look at how to use Excel's power query option for reporting purposes.

• Copy the base URL from your browser for your environment. See link for instructions on obtaining the base URL. (https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/odata#addressing)
• Start with a blank spreadsheet. From the Data tab, select "Get data." Navigate to "From Other sources > Odata Feed" (like screenshot below)

• In the following screen, paste the URL copied in step 1 with the "/data" end. It should resemble "https://ABC.operation.dynamics.com/data."

• The next dialogue box will prompt you to enter authentication information for the environment. Sign in using your organizational account. Use the same account that you used to access the environment. Then press the connect button.
• Once authentication is confirmed, the next screen will display a list of data entities available in the application. To export data to Excel, select the data entity and click load. If you want to change the format of the data in power query, you can also click transform data. Refer below URL for more details on using power query

(https://learn.microsoft.com/en-us/power-query/power-query-ui#the-power-query-editor-user-experience )