Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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.
Hi @Anonymous,
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:
Regards,
Daniel He
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |