Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Cannot convert value to type table

Hi all,   I'm pulling xml data from a web URL using PBI desktop.   When I have expanded all the columns (due to their being nested tables), I get the following error message when trying to apply ...
  • ImkeF's avatar
    ImkeF
    9 years ago

    Yes, this happens if you have mixed data types in your column to expand.

     

    This query will return the same error:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NzJR0lY0M9QyMYJz8vOVUpVgefktz8vJKMnEoCqgpLE4tKUotA6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [period_start = _t, period_end = _t, schedule = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"period_start", type date}, {"period_end", type date}, {"schedule", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each List.Transform({Number.From([period_start])..Number.From([period_end])}, each Date.From(_))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ListOfNewRows", each if [schedule]="once" then [period_start] else if [schedule]="monthly" then List.Distinct(List.Transform([ListOfDates], each Date.StartOfMonth(_))) else List.Distinct(List.Transform([ListOfDates], each Date.StartOfQuarter(_)))),
    #"Expanded ListOfNewRows" = Table.ExpandListColumn(#"Added Custom1", "ListOfNewRows")
in
    #"Expanded ListOfNewRows"

     

    You have to unify the result of step "Added Custom1" by transforming the record [period_start] into a list like this: {[period_start]}

     

    Code now working:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NzJR0lY0M9QyMYJz8vOVUpVgefktz8vJKMnEoCqgpLE4tKUotA6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [period_start = _t, period_end = _t, schedule = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"period_start", type date}, {"period_end", type date}, {"schedule", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each List.Transform({Number.From([period_start])..Number.From([period_end])}, each Date.From(_))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ListOfNewRows", each if [schedule]="once" then {[period_start]} else if [schedule]="monthly" then List.Distinct(List.Transform([ListOfDates], each Date.StartOfMonth(_))) else List.Distinct(List.Transform([ListOfDates], each Date.StartOfQuarter(_)))),
    #"Expanded ListOfNewRows" = Table.ExpandListColumn(#"Added Custom1", "ListOfNewRows")
in
    #"Expanded ListOfNewRows"
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.