Forum Discussion
Cannot convert value to type table
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"
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
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
After the #"Laajennettu Classifications.ItClassification" step, this is how the data looks like in the Classification.ItClassifications.Code column
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.
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