Power BI/query text column is shown as a table

Question

I am importing data into Power BI desktop from an XML file. If the field value is empty, e.g., <Address></Address> then PBI assigns that column type "Table" and it is not possible to change the type into text. I am getting an error:

Expression.Error: We cannot convert a value of type Table to type Text. Details: Value=Table Type=Type

It looks like if the first encountered value is empty, then all following rows of that column in the same "page" (data is being read in chunks a.k.a "pages" by the data connector) fetch are assigned type "Table", though not always. For some columns, it even not possible to change the column data type (they have "table" icon)

Power Bi image

Any ideas on how to change the column type to text?

Edit1: I have noticed that clicking "Table" actually shows there is a text value in that column. Maybe there is some invisible setting for Power BI to overcome this blank string value problem? Edit2: it looks like I have the same problem as described here https://community.powerbi.com/t5/Desktop/XML-Import-Table-Type-in-Column/td-p/97512. There is no solution to this simple problem? I found this https://community.powerbi.com/t5/Issues/Power-Query-should-not-wrap-simple-xml-elements-in-a-table/idi-p/125525. It looks it is "by design" and no solution exists. It's a pitty for wasting time...

I have found someone's solution:

shared ConvertTableField=(aTable as table, fieldName as text)=> let //alternative: https://community.powerbi.com/t5/Desktop/Expand-value-from-table/td-p/214838 //#"Expanded g_v" = Table.TransformColumns(#"Expanded ts_info", {{"g_v", each if _ is table then Table.FirstValue(_, "") else _}}) #"Lists from table" = Table.TransformColumns(aTable,{{fieldName, each if _ is table then Table.ToList(_) else {_}}}), #"Expanded List" = Table.ExpandListColumn(#"Lists from table", fieldName) in #"Expanded List";

I haven't still figured out how to run this function for all the columns of the table. M language is the most difficult of the programming languages I know more or less...

Tweaker Tweaker · Accepted Answer · 2019-06-11T12:52:52

I am not sure about the XML usage, but as far as table type to text conversion goes - this worked for me. Maybe it's an overkill.

let Source = (Table1 as table) =>

let

Section = Record.ToTable(#sections[Section1]),

Compare = Table.SelectRows(Section, each [Value] = Table1){0},

GetName = Record.Field(Compare,"Name")

in GetName 

in Source

Power BI/query text column is shown as a table

1 Answers