Cannot convert value to type table

Hi Anonymous,

I noticed that ImkeF has provided a detailed solution in this similar thread. Following is the summary for the solution. Hope it can help others who may also have this similar issue easily find the answer. :smileyhappy:

Solution form ImkeF:

 

This seems to be a bit tricky. You add a column that checks if the content of the current row of the column to expand is of type table. In that case you take the value as it is and if not, you transform the text-value to table. That way all fields will be in table format and can be expanded:

 

let

    Source = {1, #table({"a", "A"}, {{"b", "B"}})},
    #"In Tabelle konvertiert" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Step2 = Table.AddColumn(#"In Tabelle konvertiert", "Custom", each if Value.Is([Column1], type table) then [Column1] else #table({"ConvertedText"}, {{[Column1]}})),
    #"Erweiterte Custom" = Table.ExpandTableColumn(Step2, "Custom", Table.ColumnNames(Table.Combine(Step2[Custom])), Table.ColumnNames(Table.Combine(Step2[Custom])))
in
    #"Erweiterte Custom"

 

At what point in the advnaced editor do we need to insert the code?

 

 

---

Regards

Hello Experts,

I too am getting similar error:

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

I have following query in Advanced Editor:

let
Source = DocumentDB.Contents("https://<>/", "support","supportcollection",[Query = "SELECT * from resource r"] ),
#"Expanded Document" = Table.ExpandRecordColumn(Source, "Document", {"label", "id", "pk", "location", "type", "kind", "managedby", "friendlyName", "lastUpdate", "status", "failureRate", "averageDurationMS", "totalCount", "iconName", "totalFailures", "isTopLevel", "averageDuration", "count", "category", "cause", "Detection", "prevention", "eta", "effect", "failure", "function", "occurances", "priority", "recommendedActions", "resourceType"}, {"Document.label", "Document.id", "Document.pk", "Document.location", "Document.type", "Document.kind", "Document.managedby", "Document.friendlyName", "Document.lastUpdate", "Document.status", "Document.failureRate", "Document.averageDurationMS", "Document.totalCount", "Document.iconName", "Document.totalFailures", "Document.isTopLevel", "Document.averageDuration", "Document.count", "Document.category", "Document.cause", "Document.Detection", "Document.prevention", "Document.eta", "Document.effect", "Document.failure", "Document.function", "Document.occurances", "Document.priority", "Document.recommendedActions", "Document.resourceType"}),
#"Expanded Document.location" = Table.ExpandListColumn(#"Expanded Document", "Document.location"),
#"Expanded Document.location1" = Table.ExpandRecordColumn(#"Expanded Document.location", "Document.location", {"_value"}, {"Document.location._value"}),
#"Expanded Document.type" = Table.ExpandListColumn(#"Expanded Document.location1", "Document.type"),
#"Expanded Document.type1" = Table.ExpandRecordColumn(#"Expanded Document.type", "Document.type", {"_value"}, {"Document.type._value"})
in
#"Expanded Document.type1"

The column type seems to have mixed data type, some of the rows has string value some shows [list].

Since I am new to this type of branched query, I am not able to find how to put if condition that should identify if value are not type [list] then do not expand else expand and get the value.

Any help is appreciated.

Thank you.