Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need to extract the lookupValue from a column in sharepoint list

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:

Captura de pantalla 2020-10-28 211842.png

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

Captura de pantalla 2020-10-28 212229.png

Values are displayed:

Captura de pantalla 2020-10-28 212337.png

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:


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"})
#"Expanded Cliente1"

Super User IV
Super User IV
Not applicable

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.


Any other idea? Thanks in advance

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors