cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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

Document.status_Error.PNG

 

 

 

 

 

 

 

 

 

 

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

applyquerychangesError.PNG

 

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.

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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}})

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

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}})

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.