Need to extract the lookupValue from a column in sharepoint list

[Anonymous]

Hi All,

I'm extracting data from a sharedpoint list, from the Power Query Editor I see the values of the column as "List":

Then I use the "Expand to New Rows" option and I get the "Record" in the column values:

Then I click again into the expand option and I want to select only the option of "lookupValue" from the list

Values are displayed:

But after the changes are applied, I get the error message and the values:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] We cannot convert the value "" to type Table.. The current operation was cancelled because another operation in the transaction failed. '.

Any idea of how to solve this? Thanks in advance.

This is the code generated:

let
Source = SharePoint.Tables("https://xxxxxxxxxx/icpmo/", [Implementation="2.0", ViewMode="All"]),
#"bb14239c-c848-465d-80ad-52b29b8c3e96" = Source{[Id="bb14239c-c848-465d-80ad-52b29b8c3e96"]}[Items],
#"Expanded Cliente" = Table.ExpandListColumn(#"bb14239c-c848-465d-80ad-52b29b8c3e96", "Cliente"),
#"Expanded Cliente1" = Table.ExpandRecordColumn(#"Expanded Cliente", "Cliente", {"lookupValue"}, {"Cliente.lookupValue"})
in
#"Expanded Cliente1"

[amitchandak]

@Anonymous , Check it the data type of text.

also check

https://community.powerbi.com/t5/Desktop/OLE-DB-or-ODBC-error-Expression-Error-We-cannot-convert-the/td-p/678899

https://social.technet.microsoft.com/Forums/Lync/en-US/e292765f-b2f3-4a02-8ac9-e3687134c5d6/ole-db-or-odbc-error-expressionerror-we-cannot-convert-the-value-quotquot-to-type-list?forum=powerquery

[Anonymous]

Thanks @amitchandak 

I already tried to replace values and change the data type, but is was not successful.

Code to change data type and replace value after expanding:

#"Expanded Cliente1" = Table.ExpandRecordColumn(#"Expanded Cliente", "Cliente", {"lookupValue"}, {"Cliente.lookupValue"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Cliente1",{{"Cliente.lookupValue", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","","NA",Replacer.ReplaceValue,{"Cliente.lookupValue"})

Error message:

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

I tried also to change the data type before the expanding

#"Changed Type" = Table.TransformColumnTypes(#"bb14239c-c848-465d-80ad-52b29b8c3e96",{{"Cliente", type text}}),
#"Expanded Cliente" = Table.ExpandListColumn(#"Changed Type", "Cliente"),
#"Expanded Cliente1" = Table.ExpandRecordColumn(#"Expanded Cliente", "Cliente", {"lookupValue"}, {"Cliente.lookupValue"})

I got the following:

Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]

Any other idea? Thanks in advance