cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

View solution in original post

3 REPLIES 3
v-danhe-msft Super Contributor
Super Contributor

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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 335 members 3,192 guests
Please welcome our newest community members: