cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SftStag Frequent Visitor
Frequent Visitor

Error when loading tables with foreign keys

I ma trying to connect Power BI to a localhost MySQL database. When a table in that database contains a foreign key I get the following errors when i want to load the data:

Expression.Error: The index is outside the bounds of the record.

Details:

Record=Record

Index=-1

 

and

 

Expression.Error: The column 'employeenumber' of the table wasn't found.
Details:
employeenumber

 

I have two tables, orders and employees. The orders table contains the columns: ID, ProductName, Price, Employeenumber

The employees table contains the columns: Employeenumber, firstName, lastName.

orders.employeenumber is a foreign key of employees.employeenumber.

The first error relates to the employees table, the second error relates to the orders table.

I think that if the first error gets solved the second error also gets solved automaticaly.

 

When i remove the foreign key from the talbe the data loads normaly.

 

How can i resolve these errors and still keep the foreign keys?

1 ACCEPTED SOLUTION

Accepted Solutions
SftStag Frequent Visitor
Frequent Visitor

Re: Error when loading tables with foreign keys

It took a while but I finally found the problem.

Apparently Power BI doesn't like it when you use capital letters in your column name.

So I change employeeNumber to employee_number and no more errors.

3 REPLIES 3
Community Support Team
Community Support Team

Re: Error when loading tables with foreign keys

Hi @SftStag,

Based on my research, the issue may due to when one of the selected table names ends with a string that matches a schema name in the database, you could refer to below link and try the mentioned workaround:

https://support.microsoft.com/en-sg/help/2769339/index-was-outside-the-bounds-of-the-array-error-whe...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SftStag Frequent Visitor
Frequent Visitor

Re: Error when loading tables with foreign keys

Hi @v-danhe-msft,

 

Thank you for the reply.

I'm having some issues applying the solution you mentioned.

For starters the link you provided is about excel 2013. I wanted to use this to get the exact same workaround proces. The problem is I only have excel 365 business which doesn't support mysql connections.

 

As mentioned it has to do with schema names and table names having the same string. But I have no idea what a schema name is and what a table name (I'm new to sql). I do know the difference between a schema and a table but I don't know how to see the distinct names between the two.

Schema MySql workbench.PNG

In the image above I have a screenshot of the navigator in MySql Workbench. At the top it says schemas. 

Does this mean that everything here is a schema name. Or does is everything under tables a tablename.

If the later is true then that means the problem isn't about table and schema names because none of them are similar.

 

SftStag Frequent Visitor
Frequent Visitor

Re: Error when loading tables with foreign keys

It took a while but I finally found the problem.

Apparently Power BI doesn't like it when you use capital letters in your column name.

So I change employeeNumber to employee_number and no more errors.