Share via

Power BI Tips for Working with MySQL

  • Article

Connecting to MySQL

Connections to MySQL from Power Query/Power BI Desktop tend to default to Windows credentials which often do not work with default installations of MySQL Server. For example, if you have a local MySQL server running and can connect to it and view data with Workbench but attempt the following procedure:

  1. 1. Start Power BI Desktop - Get Data - MySQL Database - Connect
  2. 2. In "MySQL Database" dialog enter "localhost" or name of local machine, enter name of database and press OK button.

You may see the following error in your query editor window:

"DataSource.Error: Object reference not set to an instance of an object."

You are most likely getting a "user not authorized error". To resolve this issue, go to File | Options and Settings | Data source settings and edit your data source and set the credentials to "Database credentials", not Windows credentials.

Getting Data from MySQL Views

Excel Power Query and Power BI Desktop query Navigator, as of the August 2015 update, do not support MySQL Views. To get data from MySQL views, use the "SQL statement (optional)" and enter a SQL query such as:

SELECT * FROM "database"."view" 

Note, you must use Get Data | MySQL Database and NOT use Recent Sources, as using Recent Sources will bring up the Navigator window and not allow you to enter SQL queries directly.

Detecting Relationships

If running into problems detecting relationship, you can uncheck the options to automatically try to detect the data sources and import relationships from data source. To do this, go to File | Options and settings | Options

Under CURRENT FILE | Data Load | Relationships, uncheck options.