While creating queries with Power BI Desktop and also from within Excel, one thing had me stumped: When trying to query a particular SharePoint 2010 site, I would get an error.
“OData: The feed’s metadata document appears to be invalid.”
Other SharePoint sites in the same site collection offered their lists just fine. I started a Fiddler trace to see what was going on, and it showed that there was a problem with the ListData service metadata file that has the URL “http://<SitePath>/_vti_bin/listdata.svc/$metadata”. I plugged that URL into the browser and here, too, instead of presenting the XML of the metadata, I saw this error “An error occurred while processing this request.”:
It took a lot of searching and head scratching until I finally worked it out. I came across a blog post on Technet: why would listdata.svc return an error that explains how invalid characters in a column name can cause problems for the list service. I’m religiously avoiding special characters when naming columns, but I thought I’d take a look through the site’s lists. I found a list with a name that started with a number. While numbers are not exactly special characters, there are places where they can cause problems. Excel’s range names cannot start with a number, either, for example.
I renamed the list to start with a letter and refreshed the metadata tab. Lo and behold!
The SharePoint internal name has not changed, but the XML contains the renamed version. It appears that a list name starting with a number breaks the metadata view of a list in SharePoint 2010.
After closing Excel / Power BI Desktop to clear each query cache, the query into that SharePoint site worked fine from both tools.
PS: I tried the same in SharePoint 365 and could not reproduce the problem there. The metadata page opens fine, but the name of the list that starts with a number appears like “C_123MyList”.