Thursday, June 12, 2014

Use English as your #0365 (sub) site's default language or else...(use oData)

Hi there,
a quick post on something I discovered yesterday during the creation of a proof of concept a #0365 site that provides it's list data to an Excel Power Query & Power Pivot report. (Which will be published on a Power BI site, that will be described on it's own blog post :) )

I was having trouble getting the data from the O365 SharePoint list into Excel. After making the connection with Power Query:

The "navigator" which should show me all available lists on a given SharePoint URL was empty:
I tried different security settings, checking the data connections, removing all data connections and adding them again, blaming the latest Power BI update, googling, nothing worked. And the strange thing was that I'd seen it work in an earlier POC I did...
Eventually I tried another site URL (on the same O365 web application), that one worked !
Now I had to find out what the difference was between the working and none working sites. Soon I realized it was the regional and language settings. I made the regions the same, no difference. But when I created a new site and gave English as the default language upon creation, Power BI could instantly connect to it. 

I created a new site to test because changing the default language in O365 SharePoint is not as easy, I think I'll need Power Shell to do this but of course I need to focus on my original POC first :) 

I also found a post from Kasper de Jonge, a senior program manager at the Microsoft SQL Server BI division and a good friend; He said he'll take this issue up with the PQ team and just posted an update on this:
"Update: Yes it is a known issue which they are working on with SP, the workaround is to connect to the sharepoint API using OData, rather than using the From SharePoint option in PQ" 

Thanks Kasper !