Expression.Error: We cannot convert the value " xxxx" to type Table.

[Josdash]

Hi everybody,

I am working with an XML file and when I expand the column description I get the following error: "Expression.Error: We cannot convert the value "
<![CDATA[
<para></p..." to type Table."

Is there a way to transform text values to table? I am new in Power BI and I don't know how to manage this situation.

This is the code that I have and the error is regarding the red step, I suppose that I need to add something before this line in order to fix the issue:

let
Source = Xml.Tables(File.Contents("xxxxxx")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"violations", type text}, {"oreqmViolations", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"environment", "cmdline", "inputfiles", "configuration", "violations", "oreqmViolations"}),
#"Expanded specdocument" = Table.ExpandTableColumn(#"Removed Columns", "specdocument", {"specobjects"}, {"specdocument.specobjects"}),
#"Expanded specdocument.specobjects" = Table.ExpandTableColumn(#"Expanded specdocument", "specdocument.specobjects", {"specobject", "Attribute:doctype", "Attribute:title", "Attribute:baseline"}, {"specdocument.specobjects.specobject", "specdocument.specobjects.Attribute:doctype", "specdocument.specobjects.Attribute:title", "specdocument.specobjects.Attribute:baseline"}),
#"Expanded specdocument.specobjects.specobject" = Table.ExpandTableColumn(#"Expanded specdocument.specobjects", "specdocument.specobjects.specobject", {"id", "status", "source", "sourcefile", "sourceline", "version", "description", "providescoverage", "violations", "oreqmViolations", "covstatus", "internalId"}, {"specdocument.specobjects.specobject.id", "specdocument.specobjects.specobject.status", "specdocument.specobjects.specobject.source", "specdocument.specobjects.specobject.sourcefile", "specdocument.specobjects.specobject.sourceline", "specdocument.specobjects.specobject.version", "specdocument.specobjects.specobject.description", "specdocument.specobjects.specobject.providescoverage", "specdocument.specobjects.specobject.violations", "specdocument.specobjects.specobject.oreqmViolations", "specdocument.specobjects.specobject.covstatus", "specdocument.specobjects.specobject.internalId"}),
#"Expanded specdocument.specobjects.specobject.providescoverage" = Table.ExpandTableColumn(#"Expanded specdocument.specobjects.specobject", "specdocument.specobjects.specobject.providescoverage", {"provcov"}, {"specdocument.specobjects.specobject.providescoverage.provcov"}),
#"Expanded specdocument.specobjects.specobject.providescoverage.provcov" = Table.ExpandTableColumn(#"Expanded specdocument.specobjects.specobject.providescoverage", "specdocument.specobjects.specobject.providescoverage.provcov", {"linksto", "dstversion", "linkstatus", "linkdestdoctype", "linkerror", "internalId"}, {"specdocument.specobjects.specobject.providescoverage.provcov.linksto", "specdocument.specobjects.specobject.providescoverage.provcov.dstversion", "specdocument.specobjects.specobject.providescoverage.provcov.linkstatus", "specdocument.specobjects.specobject.providescoverage.provcov.linkdestdoctype", "specdocument.specobjects.specobject.providescoverage.provcov.linkerror", "specdocument.specobjects.specobject.providescoverage.provcov.internalId"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded specdocument.specobjects.specobject.providescoverage.provcov", each [specdocument.specobjects.specobject.oreqmViolations], each if List.Contains( {null, ""}, [specdocument.specobjects.specobject.oreqmViolations] ) then Table.FromColumns( {{}}, {"oreqmViolations"}) else [specdocument.specobjects.specobject.oreqmViolations],Replacer.ReplaceValue,{"specdocument.specobjects.specobject.oreqmViolations"}),
#"Expanded specdocument.specobjects.specobject.oreqmViolations" = Table.ExpandTableColumn(#"Replaced Value", "specdocument.specobjects.specobject.oreqmViolations", {"Element:Text"}, {"specdocument.specobjects.specobject.oreqmViolations.Element:Text"}),
#"Expanded specdocument.specobjects.specobject.description" = Table.ExpandTableColumn(#"Expanded specdocument.specobjects.specobject.oreqmViolations", "specdocument.specobjects.specobject.description", {"Element:Text"}, {"specdocument.specobjects.specobject.description.Element:Text"})
in
#"Expanded specdocument.specobjects.specobject.description"

Thank you ver much in advance.

[Josdash · Accepted solution]

Hi everybody,

I could solve the issue by transforming the column "Description" containing both tables and texts before expanding it with the following code:

#"Lists from description" = Table.TransformColumns(#"Expanded specdocument.specobjects.specobject.oreqmViolations",{{"specdocument.specobjects.specobject.description", each if _ is table then Table.ToList(_) else {_}}}),
#"Expanded specdocument.specobjects.specobject.description" = Table.ExpandListColumn(#"Lists from description", "specdocument.specobjects.specobject.description")

I based on this link -> https://community.powerbi.com/t5/Desktop/Expand-Column-Containing-Some-Tables/m-p/409196 

Thank you anyway.

José

[ahsieh]

ooh, that's a great solution. I could use this as well.

[ahsieh]

I think there is a record that does not have any nesting values and it is show up as a value "xxx".