Cannot convert value to type table

[Anonymous]

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 the query changes.

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

The value XXX is a customers surname.

Has anyone else come across this error message?

Thanks

Mike

[Anonymous]

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.

[ImkeF · Accepted solution]

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"

[randosui]

Hello,

I have the same problem, trying to get data from web xml and get error that can't convert value "-1151,43" to type table. Coudnt figure out what's the reason there....

Gr,

Rando.

[ImkeF]

Please check out this post: https://community.powerbi.com/t5/Desktop/Expand-column-where-not-all-records-are-tables/td-p/79060

If your api returns different formats, then you have to convert to one before moving on.

[DebbieE]

I have the exact same issue happening with this line 

    #"Expanded helpdesk-ticket.custom_field" = Table.ExpandTableColumn(#"Removed Columns", "helpdesk-ticket.custom_field", {"product_id_177959", "cf_sla_priority_177959"}, {"helpdesk-ticket.custom_field.product_id_177959", "helpdesk-ticket.custom_field.cf_sla_priority_177959"}),

Im still unclear as to what I need to do looking at all the above information

My error says We cannot convert the value "Infrastructure" to  type table

[PeterT]

I am facing a similar issue. I'm pulling data from a complex XML-file. While trying to convert this nested XML data into relational tables through expanding columns I get the same error "OLE DB or ODBC error: [Expression.Error] We cannot convert the value XXX to type Table.."

I have tried changing the data type of the column in the Query Editor, but this does not have any effect. The column in question is "Classifications.ItClassification.Code" and has integer and null values in it.

This is my code

let
 Lähde = Xml.Tables(File.Contents("export.xml")),
 Table0 = Lähde{0}[Table],
 #"Muutettu tyyppi" = Table.TransformColumnTypes(Table0,{{"CBPID", Int64.Type}, {"Costs", type text}, {"CBPID", Int64.Type}, {"CurrencyID", Int64.Type}, {"DateCreated", type datetime}, {"DeID", Int64.Type}, {"DUID", Int64.Type}, {"EventDate", type datetime}, {"ID", Int64.Type}, {"IsMOR", type logical}, {"IsRestricted", type logical}, {"Number", type text}, {"OwnerPersonID", Int64.Type}, {"OwnerPersonName", type text}, {"PriorityID", Int64.Type}, {"ReportTypeID", Int64.Type}, {"ReportTypeName", type text}, {"StatusID", Int64.Type}, {"StatusName", type text}, {"Title", type text}, {"TotalCost", type text}, {"TotalTime", Int64.Type}}),
 #"Laajennettu Classifications" = Table.ExpandTableColumn(#"Muutettu tyyppi", "Classifications", {"ItClassification", "Element:Text"}, {"Classifications.ItClassification", "Classifications.Element:Text"}),
 #"Laajennettu Classifications.ItClassification" = Table.ExpandTableColumn(#"Laajennettu Classifications", "Classifications.ItClassification", {"ClassificationPath", "Code", "CBPID", "DateCreated", "Description", "FindingDescription", "FindingID", "FriendlyName", "ID", "ListName", "RaisedFromFinding"}, {"Classifications.ItClassification.ClassificationPath", "Classifications.ItClassification.Code", "Classifications.ItClassification.CBPID", "Classifications.ItClassification.DateCreated", "Classifications.ItClassification.Description", "Classifications.ItClassification.FindingDescription", "Classifications.ItClassification.FindingID", "Classifications.ItClassification.FriendlyName", "Classifications.ItClassification.ID", "Classifications.ItClassification.ListName", "Classifications.ItClassification.RaisedFromFinding"}),
 #"Laajennettu Classifications.ItClassification.Code" = Table.ExpandTableColumn(#"Laajennettu Classifications.ItClassification", "Classifications.ItClassification.Code", {"Element:Text", "http://www.w3.org/2001/XMLSchema-instance"}, {"Classifications.ItClassification.Code.Element:Text", "Classifications.ItClassification.Code.http://www.w3.org/2001/XMLSchema-instance"})
in
 #"Laajennettu Classifications.ItClassification.Code"

Any ideas appreciated!

Pete

[ImkeF]

Hi Peter,

having problems understanding what you mean here:

If the column has integers and nulls in it, there would be no need to perform a "Table.ExpandTableColumn"-command like in the last step.

Could you please post a picture of how the content of the column looks like at step: #"Laajennettu Classifications.ItClassification" ?

Thx

[PeterT]

After the  #"Laajennettu Classifications.ItClassification" step, this is how the data looks like in the Classification.ItClassifications.Code column

[ImkeF]

Hi Pete,

if you see the arrows, expansion should be possible. The example from this post looked like so:

Where in the last column the arrows were missing. Because the other items in the columns have been lists, we converted the single item into a list.

So if you want to apply this method to your case, you have to convert the value into a table instead.

[PeterT]

Thank you Imke for your prompt responses,

As I am new to Power BI, it took me a while to figure out how to achieve converting to tables. I actually managed to do this with the help of this post by you. I'll continue the discussion there, as this step is now resolved and I have another issues I am trying to resolve, which I think is more related to that thread.

Much appreciated,

Pete

[v-ljerr-msft]

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. 

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