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

[nikolanenadovic]

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??

[v-jingzhang]

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.

[nikolanenadovic]

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

[v-jingzhang]

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]

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

[nikolanenadovic]

Hi Nate, 

I removed links to my tenant, and wrote REMOVED 🙂