cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nikolanenadovic
Frequent Visitor

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

Hi, 

 

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: 

 

let
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}})
in
#"Changed Type1"

 

Can anyone help??

5 REPLIES 5
v-jingzhang
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

 

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

Hi, 

 

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.

 

Best

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.

 

Regards,
Community Support Team _ Jing

watkinnc
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.

--Nate


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

Announcements
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