topic Re: Excel with Dynamics 365 connection - Cannot refresh in Power Query

Excel with Dynamics 365 connection - Cannot refresh

ntheam — Tue, 12 Mar 2024 04:32:52 GMT

Hello

Refresh no longer works through Excel Microsoft Dynamics 365 connections. I have exported a Dynamic Worksheet through Microsoft Dynamics 365 Advance Find feature. This used to work. I now get the error "This Web query returned no data...".

Upon inspection in Queries & Connections > Query_from_Microsoft_CRM > properties > definition tab > edit query; it appears that the "address" URL - in the "Edit Web Query" window - gets redirected to http://login.microsoftonline.com.au but the login window does not render correctly. It appears as through the "login" window is hidden. A grey square box - that I assume is the login window - appears 99% of the time but occassional I can see the "choose account" window rendered behind a black square; it is inaccessible. Screenshots attached. I have tried refreshing and copying the original address back into the address section but to no avail. I have tried all combinations of ticking and un-ticking the various options in “Web Query Options”, with no resolution.

After refresh:

Properties

Definition > edit query; notice that there is a grey square box where there should be a login window:

Occassionally, I will see this:

Re: Excel with Dynamics 365 connection - Cannot refresh

Anonymous — Wed, 13 Mar 2024 08:33:35 GMT

Hi ntheam ,

1. Try clearing your browser's cache and cookies to see if this resolves the issue.
2. Try update the browser and the Excel.
3. Try disabling the options here and see if that allows the login window to render correctly. Please note, this step involves security settings, so proceed with caution and consider re-enabling protected view after testing.

If after trying these steps, you're still facing the issue, it might be helpful to gather more information to further diagnose the problem:

1. Have there been any recent changes to your system or network configuration that might coincide with when the issue started?
2. Are you able to log in to Dynamics 365 through a web browser without any issues?
3. Is this issue occurring for other users as well, or is it isolated to your machine?

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group

Re: Excel with Dynamics 365 connection - Cannot refresh

ntheam — Wed, 13 Mar 2024 22:39:24 GMT

Thanks Anonymous. Yes I can login to Dynamics 365 through Edge without issues and this is running in the background. The few users that have attempted this do report the same issue. I can directly link to the API via ODataFeed however this is not a practical solution because it means that I have manually rebuild an advance find query from scatch. And there is just too much data. I've been on a sabatical for over a year so no doubt there may have been changes in the hosting environment.

Unfortunately browsers and other environment settings are managed Corporately, so these settings are greyed out for me. I have cleared cache and cookies etc however this does not resolve the issue. I have logged a ticket with Corporate IT and will let you know how this goes. I also suspect that the Pop-ups and redirects settings in the browser may also need to be updated.

Re: Excel with Dynamics 365 connection - Cannot refresh

Mehdi_Najjar — Thu, 21 Nov 2024 14:37:40 GMT

Hi,

I have the same problem, have you found a solution?

Regards

Re: Excel with Dynamics 365 connection - Cannot refresh

PwerQueryKees — Thu, 21 Nov 2024 17:33:31 GMT

I am using this to connect to MS365. Pretty awesome actually! But do NOT check the box to also load relationship field. This will get very slow for larger tables. (our tables can be miljons of rows)

CommonDataService.Database("yourenvironmentid.crm.dynamics.com")

Re: Excel with Dynamics 365 connection - Cannot refresh

AndyGadget — Thu, 29 May 2025 07:02:49 GMT

We are seeing exactly the same issue over multiple users.
We get the corrupted screen layout, as you need to full screen the query window to see anything.
All users can access D365 via a browser Customer Service/Sales with no issues and have access to all the data in the sheet.