Power BI and SharePoint – problem with new query

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.

The feed's metadata document appears to be invalid.

“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&#8221;. 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.”:

ListData.svc with broken metadata

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!

Listdata.svc with working metadata

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.

Problem solved.

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

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s