Connecting Excel with Dynamics 365 through Power Query

(0) Share

Report

Posted on by vlcmstne

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

I have the same question (0)

All responses (9)

Answers (0)

PranavShroti 4,510 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
vlcmstne 15 on at

Like (0)

Report

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)

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

Was this reply helpful? Yes No
Suggested answer

PranavShroti 4,510 on at

Like (0)

Report

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

Was this reply helpful? Yes No
vlcmstne 15 on at

Like (0)

Report

RE: Connecting Excel with Dynamics 365 through Power Query

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

Was this reply helpful? Yes No
Suggested answer

PranavShroti 4,510 on at

Like (1)

Report

RE: Connecting Excel with Dynamics 365 through Power Query

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

Was this reply helpful? Yes No
vlcmstne 15 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

PranavShroti 4,510 on at

Like (1)

Report

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,

Was this reply helpful? Yes No
vlcmstne 15 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

PranavShroti 4,510 on at

Like (1)

Report

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

Was this reply helpful? Yes No