Power Query Editor - Format Column

Question

I m trying to change column's data type but i m receiving the below error:

Expression.Error: We cannot convert a value of type Table to type Text

What i have noticed is that in the specific column is something strange. In some rows there are data and in others the word "Table".

Any help will be greatly appreaciated.

Source: .xaf file

UPDATE: Following @horseyride suggestion, due to the fact that Table are empty, I have used the below formula to convert Table to empty string.

= if [REf] is table then "" else [REf]

Answer 1

You need to convert the [table] cell to text to make the column all text

If the table has a single cell value in it, you can grab in my example below using add column .. custom column ... with formula

= if [REf] is table then [REf][c]{0}  else  [REf]

Alternately, convert all the text to tables with

= if [REf] is table then [REf]  else  #table({"REf"},{{[REf]}})

then expand that column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each if [REf] is table then [REf]  else  #table({"REf"},{{[REf]}})),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"REf"}),
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(Table.Column(#"Removed Columns", "Custom"), each if _ is table then Table.ColumnNames(_) else {}))),
#"Expanded" = Table.ExpandTableColumn(#"Removed Columns", "Custom",ColumnsToExpand ,ColumnsToExpand )
in Expanded