cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
itelligent-i Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
itelligent-i Regular Visitor
Regular Visitor

Re: odata not loading

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
Super User
Super User

Re: odata not loading

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.?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ashishrj Senior Member
Senior Member

Re: odata not loading

@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!

itelligent-i Regular Visitor
Regular Visitor

Re: odata not loading

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!

itelligent-i Regular Visitor
Regular Visitor

Re: odata not loading

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...

Super User
Super User

Re: odata not loading

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.

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
itelligent-i Regular Visitor
Regular Visitor

Re: odata not loading

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 55 members 787 guests
Please welcome our newest community members: