Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

OLE DB or ODBC error: [Expression.Error] We cannot convert the value "" to type Table..



I get an error in PowerBI query: 


OLE DB or ODBC error: [Expression.Error] We cannot convert the value "" to type Table..

This is the query: 


Source = SharePoint.Tables("REMOVED", [Implementation="2.0", ViewMode="All"]),
#"REMOVED" = Source{[Id="REMOVED"]}[Items],
#"Removed Columns" = Table.RemoveColumns(#"REMOVED",{"Button", "Client case code", "Research status", "Delivery schedule", "Title", "Compliance Asset Id", "Full Name", "ID", "Content Type", "Modified By", "Version", "Attachments", "Edit", "Type", "Item Child Count", "Folder Child Count", "Label setting", "Retention label", "Retention label Applied", "Label applied by", "Item is a Record", "App Created By", "App Modified By", "Modified"}),
#"Expanded Created By CM" = Table.ExpandListColumn(#"Removed Columns", "Created By CM"),
#"Expanded Created By CM1" = Table.ExpandRecordColumn(#"Expanded Created By CM", "Created By CM", {"title"}, {"Created By CM.title"}),
#"Expanded Client division" = Table.ExpandListColumn(#"Expanded Created By CM1", "Client division"),
#"Expanded Client division1" = Table.ExpandRecordColumn(#"Expanded Client division", "Client division", {"lookupValue"}, {"Client division.lookupValue"}),
#"Expanded Requester" = Table.ExpandListColumn(#"Expanded Client division1", "Requester"),
#"Expanded Requester1" = Table.ExpandRecordColumn(#"Expanded Requester", "Requester", {"lookupValue"}, {"Requester.lookupValue"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Requester1",{{"Subjects", type text}}),
#"Expanded Scope" = Table.ExpandListColumn(#"Changed Type", "Scope"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Scope",{{"Subjects", type text}})
#"Changed Type1"


Can anyone help??

Community Support
Community Support

Hi @nikolanenadovic 


There are multiple transformation steps in your query. If you select steps upward from the last one in Applied Steps pane, which step do you see this error start to occur? Does the first Source step get the correct result?


BTW, what data do you hope to get from Sharepoint? Is it possible to achieve your goal by using other Sharepoint connectors like Sharepoint folder connector or Sharepoint list connector?

Power Query SharePoint Folder connector | Microsoft Docs

Power Query SharePoint Online List connector | Microsoft Docs


Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.



This error happens after any Expanded lookup value. 

I use the sharepoint online list connector v2.0. It's really good in performance and gets me the data I need.



Hi @nikolanenadovic 


I see the first Expanded lookup value is in the step #"Expanded Client division1", you can remove this step and other steps below it, then expand the column from its previous step to select the columns to expand into the table. To avoid missing any steps, you can create a blank query first, paste these M codes into the new query and transform table from #"Expanded Client division" again. These steps are not complicated and are able to be operated from the User Interface in Power Query Editor.


Community Support Team _ Jing

Super User
Super User

The SharePoint.Tables function takes a url as the first parameter:

SharePoint.Tables(url as text, optional options as nullable record) as table

If for some reason you have your url saved as a text variable named REMOVED,  then you can just typed REMOVED, since it's already a text value.


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Hi Nate, 


I removed links to my tenant, and wrote REMOVED 🙂 


Helpful resources

November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors