Forum Discussion
'Table' and 'List' while pulling data from Sharepoint Onilne
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).
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.
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!!!
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?