Trouble importing sharepoint list with site column

[sminonese]

Hello dear community,

Perhaps you can advise me on whether this is a bug or a feature.

I am trying to import a sharepoint (2010) list from a subsite. The list has some columns added as "existing site column", referring to site columns in the top site.

When I import this list in Power BI, the site columns are not imported. I would first think this is some limitation in Sharepoint, but there is one software that has no trouble with it: Microsoft Access. If I import the same Sharepoint list in Access, I get all columns, no complaints. Please Microsoft Power BI, make your Sharepoint integration work at least as well as Access 🙂

Or does anyone think this can be solved somehow?

[Anonymous]

Hi sminonese,

Based on my test (SharePoint 2010, Power BI Desktop version 2.38.4491.282), I created a SharePoint list and added some columns from ‘Add from existing site columns’ function:

 

When I connected to the SharePoint list, those added columns didn’t display in Navigator, but after loading the data, those columns displayed. So we are able to get those added columns in Power BI Desktop.

 

Can you provide some detail information about this? E.g. your test environment, the version of power bi desktop, detail screenshots.

Regards,

Xiaoxin Sheng

[sminonese]

Thanks for your effort Xiaoxin!

I have tested a bit more, and it seems it is a problem related to a custom field type in Sharepoint.

I can create a list in the subsite, and add site columns from the top site. There is no issue about importing this into Power BI. However, we apparently have some customization on our sharepoint site related to generating IDs for tasks. The vendor has added a custom field type called "Task ID serial number", which is read-only for all users.

Again, importing this list in Access also pulls in this custom field type without problems. Is there something in Power BI that requires that the field is of a predefined data type? Or is the 'actual' data type of the field not correctly communicated to Power BI?This custom field is obviously just a text string.

I'm sorry I cannot show you any screenshots. The fields with this custom data type just does not appear in import navigator, query editor or in the finished data set.