Cannot convert a value of type Table to type List - data export from a sharepoint

[Anonymous]

Hello,

I'm wondering if anyone could help with this error message I receive in power query when exporting data from a table in sharepoint to Power BI:

Expression.Error: We cannot convert a value of type Table to type List.

Details:

Value=[Table]

Type=[Type]

All other columns have exported fine, but the affected column appears like this: 

This is the advanced editor log:

let

Source = SharePoint.Tables("*URL*", [Implementation= "2.0", ViewMode = "Default"]),

Navigation = Source{[Id="*ID*"]}[Items],

TableSchema = Table.Schema(Navigation),

UserMultiTypeRows = Table.SelectRows(TableSchema, each [NativeTypeName] = "UserMulti"),

UserMultiFieldNames = {"id", "value", "title", "email", "sip", "picture", "jobTitle", "department"},

UserMultiTransforms = Table.AddColumn(UserMultiTypeRows, "UserMultiTransform", each(name) =>

if (name <> "") then @text.Combine(Table.Column(Table.FromList(name, Record.FieldValues, UserMultiFieldNames), "title"), ", ") else null, Function.Type),

UserMultiChanges = Table.ToRows(Table.SelectColumns(UserMultiTransforms, { "Name","UserMultiTransform"})),

ExpandedUserMulti = Table.TransformColumns(Navigation, UserMultiChanges, null, MissingField.UseNull),

UserTypeRows = Table.SelectRows(TableSchema, each [NativeTypeName] = "User"),

UserFieldNames = {"id", "title", "email", "sip", "picture", "jobTitle", "department"},

UserTransforms = Table.AddColumn(UserTypeRows, "UserTransform", each(name) =>

if (name <> "") then @text.Combine(Table.Column(Table.FromList(name, Record.FieldValues, UserFieldNames), "title"), ", ") else null, Function.Type),

UserChanges = Table.ToRows(Table.SelectColumns(UserTransforms, { "Name","UserTransform"})),

ExpandedUser = Table.TransformColumns(ExpandedUserMulti, UserChanges, null, MissingField.UseNull),

LookupTypeRows = Table.SelectRows(TableSchema, each [NativeTypeName] = "Lookup"),

LookupFieldNames = {"lookupId", "lookupValue", "isSecretFieldValue"},

LookupTransforms = Table.AddColumn(LookupTypeRows, "LookupTransform", each(name) =>

if (name <> "") then @text.Combine(Table.Column(Table.FromList(name, Record.FieldValues, LookupFieldNames), "lookupValue"), ", ") else null, Function.Type),

LookupChanges = Table.ToRows(Table.SelectColumns(LookupTransforms, { "Name","LookupTransform"})),

ExpandedLookup = Table.TransformColumns(ExpandedUser, LookupChanges, null, MissingField.UseNull),

TaxonomyFieldTypeTypeRows = Table.SelectRows(TableSchema, each [NativeTypeName] = "TaxonomyFieldType"),

TaxonomyFieldTypeTransforms = Table.AddColumn(TaxonomyFieldTypeTypeRows, "TaxonomyFieldTypeTransform", each (name) =>

if (name <> "") then @text.Combine(Table.Column(Table.FromRecords({ name }), "Label")) else null, Function.Type),

TaxonomyFieldTypeChanges = Table.ToRows(Table.SelectColumns(TaxonomyFieldTypeTransforms, { "Name","TaxonomyFieldTypeTransform"})),

ExpandedTaxonomyFieldType = Table.TransformColumns(ExpandedLookup, TaxonomyFieldTypeChanges, null, MissingField.UseNull),

LocationTypeRows = Table.SelectRows(TableSchema, each [NativeTypeName] = "Location"),

LocationTransforms = Table.AddColumn(LocationTypeRows, "LocationTransform", each (name) =>

if (name <> "") then @text.Combine(Table.Column(Table.FromRecords({ name }), "DisplayName")) else null, Function.Type),

LocationChanges = Table.ToRows(Table.SelectColumns(LocationTransforms, { "Name","LocationTransform"})),

ExpandedLocation = Table.TransformColumns(ExpandedTaxonomyFieldType, LocationChanges, null, MissingField.UseNull),

ThumbnailTypeRows = Table.SelectRows(TableSchema, each [NativeTypeName] = "Thumbnail"),

ThumbnailTransforms = Table.AddColumn(ThumbnailTypeRows, "ThumbnailTransform", each (name) =>

if (name <> "") then @text.Combine(Table.Column(Table.FromRecords({ name }), "fileName")) else null, Function.Type),

ThumbnailChanges = Table.ToRows(Table.SelectColumns(ThumbnailTransforms, { "Name","ThumbnailTransform"})),

ExpandedThumbnail = Table.TransformColumns(ExpandedLocation, ThumbnailChanges, null, MissingField.UseNull),

LookupMultiTypeRows = Table.SelectRows(TableSchema, each [NativeTypeName] = "LookupMulti"),

LookupMultiFieldNames = {"lookupId", "lookupValue", "isSecretFieldValue"},

LookupMultiTransforms = Table.AddColumn(LookupMultiTypeRows, "LookupMultiTransform", each(name) =>

if (name <> "") then @text.Combine(Table.Column(Table.FromList(name, Record.FieldValues, LookupMultiFieldNames), "lookupValue"), ", ") else null, Function.Type),

LookupMultiChanges = Table.ToRows(Table.SelectColumns(LookupMultiTransforms, { "Name","LookupMultiTransform"})),

ExpandedLookupMulti = Table.TransformColumns(ExpandedThumbnail, LookupMultiChanges, null, MissingField.UseNull),

TaxonomyFieldTypeMultiTypeRows = Table.SelectRows(TableSchema, each [NativeTypeName] = "TaxonomyFieldTypeMulti"),

TaxonomyFieldTypeMultiFieldNames = {"Label", "TermID"},

TaxonomyFieldTypeMultiTransforms = Table.AddColumn(TaxonomyFieldTypeMultiTypeRows, "TaxonomyFieldTypeMultiTransform", each(name) =>

if (name <> "") then @text.Combine(Table.Column(Table.FromList(name, Record.FieldValues, TaxonomyFieldTypeMultiFieldNames), "Label"), ", ") else null, Function.Type),

TaxonomyFieldTypeMultiChanges = Table.ToRows(Table.SelectColumns(TaxonomyFieldTypeMultiTransforms, { "Name","TaxonomyFieldTypeMultiTransform"})),

ExpandedTaxonomyFieldTypeMulti = Table.TransformColumns(ExpandedLookupMulti, TaxonomyFieldTypeMultiChanges, null, MissingField.UseNull),

MultiChoiceTypeRows = Table.SelectRows(TableSchema, each [NativeTypeName] = "MultiChoice"),

MultiChoiceTransforms = Table.AddColumn(MultiChoiceTypeRows, "MultiChoiceTransform", each(name) =>

if (name <> "") then @text.Combine(name, ", ") else null, Function.Type),

MultiChoiceChanges = Table.ToRows(Table.SelectColumns(MultiChoiceTransforms, { "Name","MultiChoiceTransform"})),

ExpandedMultiChoice = Table.TransformColumns(ExpandedTaxonomyFieldTypeMulti, MultiChoiceChanges, null, MissingField.UseNull),

#"Added Custom" = Table.AddColumn(ExpandedMultiChoice, "Due Date Updated", each List.Max({[Due Date], [Revised date]})),

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Current Date", each DateTime.Date(DateTime.LocalNow ())),

#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Current Date", type date}, {"Due Date Updated", type date}}),

#"Added Custom2" = Table.AddColumn(#"Changed Type", "PastDue", each if [Due Date Updated] <= [Current Date] then "Yes" else "No"),

#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Owner", type text}})

in

#"Changed Type1"

Please let me know how I can resolve if possible, thank you!

[amustafa]

it's kinda difficult to troubleshoot it all at once. Go back one step at a time and see which transformation step starting to give you the error and start to fix it from there on.

[amustafa]

The error you're encountering, "Expression.Error: We cannot convert a value of type Table to type List," usually occurs when there's a mismatch between the expected data types in a function. In your code, this error is likely happening in the `Table.TransformColumns` function calls, where you are attempting to use the `Table.ToRows` output (which is a list of lists) as a transformation specification in `Table.TransformColumns`. `Table.TransformColumns` expects a list of column transformation specifications, typically provided as a list of tuples.

To correct this error, we need to adjust the way you're creating the `UserMultiChanges`, `UserChanges`, `LookupChanges`, etc., so that they're in the format expected by `Table.TransformColumns`. Each of these variables should be a list of tuples, where each tuple consists of the column name to be transformed and the transformation function to apply.

Here's how you can modify your code:

1. Change the `Table.ToRows` to `Table.ToRecords` in the transformations for `UserMultiChanges`, `UserChanges`, `LookupChanges`, and so on. This change is necessary because `Table.TransformColumns` expects a list of records (tuples) specifying the column name and the transformation function.

2. Make sure that the transformation function is correctly defined. Currently, it seems like you are trying to pass column names to the transformation functions which may not be the correct approach depending on your data structure and what you're trying to achieve.

I'll modify the first few transformations as an example:

UserMultiTransforms = Table.AddColumn(UserMultiTypeRows, "UserMultiTransform", each (name) =>
if (name <> "") then @text.Combine(Table.Column(Table.FromList(name, Record.FieldValues, UserMultiFieldNames), "title"), ", ") else null, Function.Type),

UserMultiChanges = Table.ToRecords(Table.SelectColumns(UserMultiTransforms, { "Name","UserMultiTransform"})),

ExpandedUserMulti = Table.TransformColumns(Navigation, UserMultiChanges),

UserTypeRows = Table.SelectRows(TableSchema, each [NativeTypeName] = "User"),

UserFieldNames = {"id", "title", "email", "sip", "picture", "jobTitle", "department"},

UserTransforms = Table.AddColumn(UserTypeRows, "UserTransform", each (name) =>
if (name <> "") then @text.Combine(Table.Column(Table.FromList(name, Record.FieldValues, UserFieldNames), "title"), ", ") else null, Function.Type),

UserChanges = Table.ToRecords(Table.SelectColumns(UserTransforms, { "Name","UserTransform"})),

ExpandedUser = Table.TransformColumns(ExpandedUserMulti, UserChanges),

Apply similar changes to the other transformations in your code. Remember, the key is to provide `Table.TransformColumns` with a list of tuples where each tuple contains a column name and a corresponding transformation function.

[Anonymous]

Hello,

Thank you for your response. I'm not very well read in power query so trying to understand what you mean by 'Make sure that the transformation function is correctly defined'. This code is what Power BI did automatically for me when I exported from the sharepoint table.

When I replace Table.toRows to Table.toRecords for all my transformations, I instead get this error: 

What does this mean?

[Neo_Mando]

I've exactly the same problem and still not found a solution, do you ?

[SimpleHuMan]

Hi @Neo_Mando, I hope you found the solution to the problem.

My solution was to change UserMultiTransform script. Changes marked in bold below. I hope this helps. 

UserMultiTransform = Table.AddColumn(UserMultiTypeRows, "UserMultiTransform", each(name) =>
if (name <> null) then @Text.Combine(Table.Column(Table.FromList(Table.ToRecords(name), Record.FieldValues, UserFieldNames), "title"), ", ") else null, Function.Type)