OLE DB or ODBC error: [Expression.Error] We cannot convert the value "" to type Table

Power Query
1

Good afternoon,
I am not able to get through this error.

The error message is:

image
image831×395 34.2 KB

My understanding is that in the column that I expanding there is a value “” and the column type can not handle it.

The column that I need and is creating the issue is Links, and from there I need to expand the table and bring the colunm Attribute:Organization_ID

image
image691×559 75.6 KB

Attached is pbix with this table only.

Any help is more than welcome!
Dna_Projects.pbix (247.1 KB)

Thanks in advance
Ezquiel

2

Hi @ezenunez,

When you examine the values in your LINKS.LINK column you’ll find empty text strings, “”
Since a string isn’t of type table, that’s what is causing the error. Two ways around it 1) filter out those strings in the LINKS.LINK column or 2) replace the strings in that column with an empty table. Something like:
Table.FromColumns( {{}, {}}, {"Attribute:CONTACT_ID", "Attribute:ORGANISATION_ID"})

In this chapter you’ll find common errors in PQ and how to deal with them.

I hope this is helpful

3

Thank you Melissa,
I am pleased to know I am closer to solving this issue. I was trying different things for days!

Option 1
I was not able to filter those “empty String”. I try to replace it with nulls but did not work either.
Option 2
Still bring the error, I wondering if the location and the syntax are correct

image
image2808×543 91.5 KB

I am attaching my XML file in case you are able to have a go.

Projects.xml (1.4 MB)

Thanks a lot for your help.

Warm regards,
Ezequiel

4

Hi @ezenunez,

I’m not experiencing the reported issue when expanding the nested tables from the provided xml However I have implemented a replace values step for your reference.

Here’s your sample file (just add your file location to the FileLocation parameter list and select it)
Expand nested table.pbix (12.1 KB)

I hope this is helpful

5

Thank you for checking this our @Melissa :slight_smile:

Hello @ezenunez, just following up if the response above help you solve your inquiry?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

6

Hi @ezenunez, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.