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

[TheSott94]

Hi all,

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

When I have expanded all the columns, 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.

Has anyone else come across this error message?

Thanks in advance,

Manuel

[TheSott94 · Accepted solution]

Hi, @v-yalanwu-msft 

I solved in another way that I 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 "[Table]" else if [#"ProblematicColumn"] is record then "[Record]" else if [#"ProblematicColumn"] is list then "[List]" 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 filter applied and then the error does not show up anymore.

Hopefully this will be helpful for someone in the future,

Manuel

[v-yalanwu-msft]

Hi, @TheSott94 ;
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

In addition, if you can please share a pbix file to test, it is hard to troubleshooting without any detail information and sample data.

https://forum.enterprisedna.co/t/ole-db-or-odbc-error-expression-error-we-cannot-convert-the-value-t...

https://community.powerbi.com/t5/Desktop/OLE-DB-or-ODBC-error-Expression-Error-We-cannot-convert-the...

https://community.powerbi.com/t5/Power-Query/OLE-DB-or-ODBC-error-Expression-Error-We-cannot-convert...

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.