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

[TomD90]

Hi, relatively beginner when it comes to Power Query and Power BI but trying to learn!

I have a script which is trying to pull data from a Sharepoint List into a Power BI Report. The script is:

let
Source = SharePoint.Tables("Removed the web link for this forum", [Implementation= "2.0", ViewMode = "Default"]),
Navigation = Source{[Id="d1c32555-6e60-4076-9fcc-b89ad5c2c1e0"]}[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)
in ExpandedMultiChoice

However, I am getting the error:

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

Specifically on People columns, and looks to be occuring at the ExpandedUserMulti step. 

Have tried to figure it out myself but no luck. Please help! Thank you.

[TomD90]

This is what I've currently got:

ExpandedUserMulti = Table.TransformColumns(UserMultiChanges, { {"Dependency Owner (Accountable)", each null} }),

Still giving the same error though:

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

Thanks again,

[BA_Pete]

Ok. Difficult to say what the issue is here without being able to see what your UserMultiChanges step looks like before this transformation.

Are you able to post a small example of your data as it appears at the Source step here please? This will allow me to apply your whole current query to the sample data and see what's going on.

Make sure to remove any sensitive data first, then copy and paste the sample into the Enter Data section in Power Query (max 30,000 CELLS). You can then copy the code from Advanced Editor for that query and paste it into a code window here ( </> button).

Pete

[TomD90 · Accepted solution]

Thanks Pete. Appear to have solved the issue. It was due to the data in People columns being pulled in as nested tables. Have added a couple of steps to expand the nested tables into seperate cokumns and it seems to have done the trick, can now see the data as needed in the report. 
Thanks again for looking into this one.

[TomD90]

Thanks Pete, this is really helpful for my understanding. 

I have now updated the Expanded User Multi step as follows:

ExpandedUserMulti = Table.TransformColumns(UserMultiChanges, { {"Dependency Owner (Accountable)", each null} }, MissingField.UseNull),

From what you said I'm assuming I'm missing an operation in there? (Have just focused on one of the columns for now for sake of simplicity).

I am now getting this error, the opposite of before:

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

Thanks again for your help!

[BA_Pete]

Try taking out the ", MissingField.UseNull" argument at the end.

Pete

[BA_Pete]

Hi @TomD90 ,

It doesn't look as though the step you've referenced is structured correctly.

Per MS Learn:

Table.TransformColumns(
    table as table,
    transformOperations as list,
    optional defaultTransformation as nullable function,
    optional missingField as nullable number
) as table

The first argument should reference the previous step name that represents the table you want to perform that transformation on - you're referencing the Navigation step which I can't imagine is what you're intending.

The second argument should be a list of lists that each contain the column and function that you want the Table.TransformColumns function to perform on each value - you're referencing the previous step name.

From what I can see, it looks like you're trying to null out all values in a column. I would suggest your step should look more along these lines:

= Table.TransformColumns(
    UserMultiChanges,
    { {"ColumnName", each null} },
    MissingField.UseNull
)

Pete