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

[Anonymous]

Hello Experts,

I am getting data from cosmos db into powerBI. I see some columns, e.g. Document.status having mixed type of data as shown below:

Some rows show List, some has string values

I am trying to expand the column but I get following error:

Following is the query in advanced editor:

let
Source = DocumentDB.Contents("https://<>.documents.azure.com:443/", "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.kind" = Table.ExpandListColumn(#"Expanded Document.location1", "Document.kind"),
#"Expanded Document.kind1" = Table.ExpandRecordColumn(#"Expanded Document.kind", "Document.kind", {"_value"}, {"Document.kind._value"}),
#"Expanded Document.friendlyName" = Table.ExpandListColumn(#"Expanded Document.kind1", "Document.friendlyName"),
#"Expanded Document.friendlyName1" = Table.ExpandRecordColumn(#"Expanded Document.friendlyName", "Document.friendlyName", {"_value"}, {"Document.friendlyName._value"}),
#"Expanded Document.status" = Table.ExpandListColumn(#"Expanded Document.friendlyName1", "Document.status"),
#"Expanded Document.status1" = Table.ExpandRecordColumn(#"Expanded Document.status", "Document.status", {"_value"}, {"Document.status._value"})
in
#"Expanded Document.status1"

I am not sure how to write if condition in expand record query. Any help is appreciated. Thank you.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

You can use try/otherwise.

In this case, this should work:

= Table.TransformColumns(#"last step",
{{"column", each try Combiner.CombineTextByDelimiter(";")(List.Transform( _ , each [value]))
otherwise null, type text}})

[pwatkinson]

Hi v-frfei-msft. Would it be possible for you to include the complete code and proper syntax please? I a have a similar issue, but being a newbee to Power BI I am finding the code a little hard to follow. Thanks

[v-frfei-msft]

Hi @Anonymous ,

Has your issue been solved? If so kindly mark my answer as a solution to close the case. Thanks in advance. Any other question, feel free to let me know please.