Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Connecting Excel with ...
Microsoft Dynamics CRM (Archived)

Connecting Excel with Dynamics 365 through Power Query

(0) ShareShare
ReportReport
Posted on by vlcmstne 15

Afternoon,

I am having issues connection to my Dynamics data through Power Query,  I have tried to do this with the following method:

1. Create New Query from Dynamics 365 (online)

2. In the WEB API URL, enter the instance Web API found in the Customization Setting under Developer resources.

a. After pasting the data, an error Icon shows up to indicate the following:

"Invalid Arguments: one or more of the input field is in the incorrect format"

I am hoping someone can help with this issue, as I am stuck at this point.

Thank you

*This post is locked for comments

  • Suggested answer
    PranavShroti Profile Picture
    PranavShroti 4,510 on at
    RE: Connecting Excel with Dynamics 365 through Power Query

    Hi,

    Can you share what syntex are you following when your enter ur credentials...

    1. DOMAIN\UserName or just UserName

    2. Did you try using Admin credentials - just to rule out the posibliity of any permissions issues.

    3. Ensure you are using OrginizationData.scv service right (REST service)

    Additionally which option you are using... Login automatically or passing credentials? a

  • vlcmstne Profile Picture
    vlcmstne 15 on at
    RE: Connecting Excel with Dynamics 365 through Power Query

    that is very helpful, so if I use the Instance Web API address or the Discovery Web API I get an error of "We couldn't authenticate with the credentials provided. Please try again.", do I need certain permissions possibly to pull the Odata feed, I can log in and use dynamics just fine, but Authenticating with the windows type to the Odata Feed throws that error.

    I appreciate all your help on this.

  • Suggested answer
    PranavShroti Profile Picture
    PranavShroti 4,510 on at
    RE: Connecting Excel with Dynamics 365 through Power Query

    For internal (non-IFD) deployments enter the URL for the OData feed similar to server/.../vn.0 where server/crmorganization is the web app path for the Microsoft Dynamics 365 organization and vn.0 is the version of Microsoft Dynamics 365, such as v8.0 for Microsoft Dynamics 365. For example, contoso1/.../v8.0.

    When you are prompted to sign-in to access the OData feed, select Windows from the list of authentication types, and then enter your Dynamics 365 (on-premises) credentials.

    www.cobalt.net/.../excel-odata-feed-from-dynamics-crm-on-premise-or-ifd

    Hope this helps,

  • vlcmstne Profile Picture
    vlcmstne 15 on at
    RE: Connecting Excel with Dynamics 365 through Power Query

    That does explain why I was having issues, is there any information on how to properly create the connection with Power Query and On premises Dynamics, because when I try to use an Odata feed through power Query I get an authorization issue using windows Authorization methods.

  • Suggested answer
    PranavShroti Profile Picture
    PranavShroti 4,510 on at
    RE: Connecting Excel with Dynamics 365 through Power Query

    Yes, because your screenshot says Dynamics 365 (Online)...

  • vlcmstne Profile Picture
    vlcmstne 15 on at
    RE: Connecting Excel with Dynamics 365 through Power Query

    we are OnPrem, does that affect the connection ability for Power Query?

  • Suggested answer
    PranavShroti Profile Picture
    PranavShroti 4,510 on at
    RE: Connecting Excel with Dynamics 365 through Power Query

    Are you onPrem, for online users the URL should look like this pranavshroti.api.crm5.dynamics.com/.../v9.1

  • vlcmstne Profile Picture
    vlcmstne 15 on at
    RE: Connecting Excel with Dynamics 365 through Power Query

    Below are some screen shots of the issue I seem to be having,

    Here is where I am getting the API address from to enter in Excel Data line for Dynamics 365 (online)

    CRM-Link_5F00_Excel.PNG

    Using this address in Excel I get the error shown below:

    CRM-Link_5F00_Excel.PNG

  • PranavShroti Profile Picture
    PranavShroti 4,510 on at
    RE: Connecting Excel with Dynamics 365 through Power Query

    Hi... can you please share some screen shots so that we can get a better picture of what you are trying to do.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,489 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans