'Table' and 'List' while pulling data from Sharepoint Onilne

[Brandimore]

Hello,

All of the suggestions I've searched here aren't working. The issue is that I see LIST or TABLE for a few columns. I am pulling from a Sharepoint list online and I know the cause of the isuse.

Connection type is: Sharepoint List Online

Source columns that aren't loading:

- Pulling in names from AD: A column in which connects to AD and pulls in names which we can populate

Outcome in PBI Query:

Clicking the icon does nothing. Nothing populates. And yes, data is in the Sharepoint list

When I click on a cell that shows 'Table' I get an error saying it's not supported

[christinepayton]

To add on to what Daryl was mentioning, if you ARE using the 1.0 connector, if you go into the advanced editor and change the API version from 15 to 14, it will usually fix this issue. It causes the column names to all change, though, so you will have to go back and fix some of the query steps when you do this. I've run into the same issue you're mentioning many times... this fixes it for me every time. 

There are some cases where you have to use 1.0 over 2.0 (large lists), but try to use 2.0 where you're able. 

[Brandimore]

So I just tried a new connection and when I select 2.0, this is what I see under advanced. I get a drop down only. I can't type anything in

But I tried this 2.0 connector (had no idea the difference) and when I click on the little button for the 2 columns in question, I see something new:

If I click OK to expand the values, I see a bunch of numbers and then a new column is created which shows the list of names. 

So this solved the issue (between you and Daryl)

I appreciate your help!!!

[Daryl-Lynch-Bzy]

@Brandimore Implementation="2.0" does a better job at returning the current column name that appears in SharePoint, so it is possible that someone has re-named "PrimaryContact" to "IT Lead".   Implementation="2.0" also does a better job as finding and expanding nested table IMHO.  When you expand the table to those columns can you need the IT leads details?

[Daryl-Lynch-Bzy · Accepted solution]

Hi @Brandimore - can you please show the Power Query formula that you are using to connect to the SharePoint site.  I am hoping that you are using Implementation="2.0" rather than ApiVerison=15 or 14.  If you are using one approaches, you should consider switch to the other approach to see if Power Query can import the data.

Power Query SharePoint Online list connector - Power Query | Microsoft Learn

Other consideration, what is the data type in SharePoint of the column that you are import?  The "table" result in Power Query suggest some type of nested result.  It might be limitation for OData API that Power Query is using (hopefully the above fixes the issue).

[Brandimore]

I had selected 1.0 and I don't have the option to select any API version. See comment above. I get 2 options for viewer mode but I can see in PQ how you can check the API Version. This is what it shows for the 1.0 conneciton

= SharePoint.Tables("https://032gc.sharepoint.com/sites/XXXXXXX", [Implementation=null, ApiVersion=15])

This is what it shows for the 2.0 conneciton I just created 

= SharePoint.Tables("https://032gc.sharepoint.com/sites/XXXXXXXX", [Implementation="2.0", ViewMode="All"])

[christinepayton]

It's in the Advanced Editor button (the one where you see all the query code) in th query editor.