'OLE DB or ODBC error: [Expression.Error] The value "" cannot be converted to the Table..'

[asimpleaccountant]

Hi all,

I'm pulling xml data from a folder using Power Bi Desktop.

When I have expanded all the columns in PowerQuery, I get the following error message when trying to apply the query changes.

The changes could not be saved to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] The value "" cannot be converted to the Table..'.

I can't figure out what kind of value "" can be.

The folder is contained on a local network path, and contains various file extension: for expanding only the .xml files i actually filter only the ".xml" in the column extension.

I already tried to remove empty and error value in the column without any success, and can't really find any similar problem in other communities.


Has anyone else come across this error message?

Please, tell me if any additional information can help in solving this issue,

Thanks in advance,

 

[asimpleaccountant]

Hi all,

I solved this problem and I'll post here in the hope that it will be of help to someone.



First, I started to expand from the beginning of the query the columns and locate which column was causing the issue.



Second, i removed the passage where I expand the problematic column and i added a custom column which has this function:



Table.AddColumn(#"Problematic Column", "NameNewColumn", each if [#"ProblematicColumn"] is table then "

" else if [#"ProblematicColumn"] is record then "[Record]" else if [#"ProblematicColumn"] is list then "

• " else if [#"ProblematicColumn"] is function then "[Function]" else [#"ProblematicColumn"])

• 
  
  
  
  Third, i used filter in the "NameNewColum" trying to locate if there was any other values besides [Tabel] and I actually found that there was some rows without any kind of values (note that the column quality tools always reported 100% valid values). I proceeded to filter only the table values then.
  
  
  
  Fourth, i proceeded to expand the problematic column with the above filter applied and then the error does not show up anymore.
  
  
  
  Hopefully this will be helpful for someone in the future,
  
  
  
  E