Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
itelligent-i
Helper I
Helper I

odata not loading

Has anyone come across an issue with PowerBI Designer whereby a OData feed (Dynamics) won't refresh.  It constantly gives me a 404 return message?  however if I open up a new pbix then it loads fine.. ?? Robot Frustrated

1 ACCEPTED SOLUTION

Finally got there (once again thanks to fiddler Man Tongue).  Used Fiddler Web Debugger to monitor, copy and manipulate and resubmit requests.  I can confirm having done this that my initial suspicions were correct.  If your column names, including a comma(,) in between each (eg. column1,column2,column3) exceed 2048 characters you will receive a 404 Not Found error in Power BI (or Power Query - it's the same in both Excel and Power BI Desktop).

 

Now this is not a Power BI issue.  The Dynamics CRM OData feed only allows for 2048 characters to be passed to the service.  Anything more is essentially a bad request (eg it doesn't exist!).  See the definition here https://msdn.microsoft.com/en-us/library/gg309461.aspx  I naviely thought the PBI Team had somehow negated the 2048 character limit, but it's for all HTTP requests which is obvious post testing and confirming Robot Embarassed

 

Be aware that when you use functions such as Keep Errors or Remove Errors (which are the 2 most important that I've found), the OData request includes all columns.  I think this is a potential bug in Power Query which I'll submit to the support team.  I would have thought only the columns left in your query would be included in the $select= filter for the OData request? it of course may not be this simple to resolve......

 

I have just tried this with the split function which also returns an error to so actually this is looking like a pretty fundamental issue to be honest...

View solution in original post

6 REPLIES 6
ashishrj
Power Participant
Power Participant

@itelligent-i I have came across the same issue when dealing with Dynamics Online OData feed when working with Power BI Designer Preview. But since Power BI Desktop tool is out in market, refresh issue have been resolved and now working smooth. If you have your project in older version of pbix file, then try installing new desktop tool and save as a newer version and later refresh. Hope this works!

Hi Guys - CRM Online OData feed...

 

and yep, we're using the latest PBI Desktop.  I'v been narrowing down the issue this morning and it appears to be something with certain columns, or a combination of columns in the OData string... I could do with a more verbose log file somewhere!

Is there a maximum number of columns an OData feed can return?  I'm not familiar with any limits but it appears to be the number of columns being returned, or even possible the number of characters per row??

 

Edit - or could it be the OData character string is limited to a maximum number of characters? in which case the column headings (as defined in the CRM System) would limit the number of columns that can be returned...

I have had issues with some CRM loads, particularly LeadSet list when trying to get all columns, it would bomb out right in the middle with a 404. Never did track it down specifically but not getting all of the colums (and specifically not expanding all of the columns) made the problem go away.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Finally got there (once again thanks to fiddler Man Tongue).  Used Fiddler Web Debugger to monitor, copy and manipulate and resubmit requests.  I can confirm having done this that my initial suspicions were correct.  If your column names, including a comma(,) in between each (eg. column1,column2,column3) exceed 2048 characters you will receive a 404 Not Found error in Power BI (or Power Query - it's the same in both Excel and Power BI Desktop).

 

Now this is not a Power BI issue.  The Dynamics CRM OData feed only allows for 2048 characters to be passed to the service.  Anything more is essentially a bad request (eg it doesn't exist!).  See the definition here https://msdn.microsoft.com/en-us/library/gg309461.aspx  I naviely thought the PBI Team had somehow negated the 2048 character limit, but it's for all HTTP requests which is obvious post testing and confirming Robot Embarassed

 

Be aware that when you use functions such as Keep Errors or Remove Errors (which are the 2 most important that I've found), the OData request includes all columns.  I think this is a potential bug in Power Query which I'll submit to the support team.  I would have thought only the columns left in your query would be included in the $select= filter for the OData request? it of course may not be this simple to resolve......

 

I have just tried this with the split function which also returns an error to so actually this is looking like a pretty fundamental issue to be honest...

Greg_Deckler
Super User
Super User

Is this on premises Dynamics and what flavor of Dynamics? It sounds like your data source is available locally but not allowed from the cloud/external would be my first guess. May have to open some firewall ports, etc.?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors