Within Power Query, you may want to pull data from a SharePoint List that is hosted on SharePoint Online. When loading from the site, you will see the lists populated in the Navigator pane. This is what it looks like for my current site that is set to English.
However, if I try to do the same thing with a Non-English site, German in this case, I will not get anything returned.
Unfortunately, there is a limitation, with Power Query currently, that only allows this to work with an English Site. Power Query will use the results from SharePoint to figure out how to categorize the data that comes back. The problem is that SharePoint localizes the property names coming back. So, something like ContentID will come back for English, but for German will be InhaltstypID.
Here’s a look at the German Test List that I created to illustrate what SharePoint is sending. I pulled this using Fiddler.
English:
<EntityType Name="GermanTestItem"> <Key> <PropertyRef Name="Id" /> </Key> <Property Name="ContentTypeID" Type="Edm.String" Nullable="true" /> <Property Name="Title" Type="Edm.String" Nullable="true" m:FC_TargetPath="SyndicationTitle" m:FC_ContentKind="text" m:FC_KeepInContent="true" /> <Property Name="Value" Type="Edm.String" Nullable="true" /> <Property Name="Id" Type="Edm.Int32" Nullable="false" /> <Property Name="ContentType" Type="Edm.String" Nullable="true" /> <Property Name="Modified" Type="Edm.DateTime" Nullable="true" m:FC_TargetPath="SyndicationUpdated" m:FC_ContentKind="text" m:FC_KeepInContent="true" /> <Property Name="Created" Type="Edm.DateTime" Nullable="true" /> <NavigationProperty Name="CreatedBy" Relationship="Microsoft.SharePoint.DataService.GermanTestItem_CreatedBy" FromRole="GermanTestItem" ToRole="CreatedBy" /> <Property Name="CreatedById" Type="Edm.Int32" Nullable="true" /> <NavigationProperty Name="ModifiedBy" Relationship="Microsoft.SharePoint.DataService.GermanTestItem_ModifiedBy" FromRole="GermanTestItem" ToRole="ModifiedBy" /> <Property Name="ModifiedById" Type="Edm.Int32" Nullable="true" /> <Property Name="Owshiddenversion" Type="Edm.Int32" Nullable="true" ConcurrencyMode="Fixed" /> <Property Name="Version" Type="Edm.String" Nullable="true" /> <NavigationProperty Name="Attachments" Relationship="Microsoft.SharePoint.DataService.GermanTestItem_Attachments" FromRole="GermanTestItem" ToRole="Attachments" /> <Property Name="Path" Type="Edm.String" Nullable="true" /></EntityType>
German:
<EntityType Name="GermanTestItem"> <Key> <PropertyRef Name="ID" /> </Key> <Property Name="InhaltstypID" Type="Edm.String" Nullable="true" /> <Property Name="Titel" Type="Edm.String" Nullable="true" m:FC_TargetPath="SyndicationTitle" m:FC_ContentKind="text" m:FC_KeepInContent="true" /> <Property Name="Value" Type="Edm.String" Nullable="true" /> <Property Name="ID" Type="Edm.Int32" Nullable="false" /> <Property Name="Inhaltstyp" Type="Edm.String" Nullable="true" /> <Property Name="Geändert" Type="Edm.DateTime" Nullable="true" m:FC_TargetPath="SyndicationUpdated" m:FC_ContentKind="text" m:FC_KeepInContent="true" /> <Property Name="Erstellt" Type="Edm.DateTime" Nullable="true" /> <NavigationProperty Name="ErstelltVon" Relationship="Microsoft.SharePoint.DataService.GermanTestItem_ErstelltVon" FromRole="GermanTestItem" ToRole="ErstelltVon" /> <Property Name="ErstelltVonId" Type="Edm.Int32" Nullable="true" /> <NavigationProperty Name="GeändertVon" Relationship="Microsoft.SharePoint.DataService.GermanTestItem_GeändertVon" FromRole="GermanTestItem" ToRole="GeändertVon" /> <Property Name="GeändertVonId" Type="Edm.Int32" Nullable="true" /> <Property Name="Owshiddenversion" Type="Edm.Int32" Nullable="true" ConcurrencyMode="Fixed" /> <Property Name="Version" Type="Edm.String" Nullable="true" /> <NavigationProperty Name="Anlagen" Relationship="Microsoft.SharePoint.DataService.GermanTestItem_Anlagen" FromRole="GermanTestItem" ToRole="Anlagen" /> <Property Name="Pfad" Type="Edm.String" Nullable="true" /></EntityType>
Unfortunately, there is not a timeline on when this limitation will be changed to allow for reading Non-English lists from SharePoint.
Adam W. Saxton | Microsoft SQL Server Escalation Serviceshttp://twitter.com/awsaxton