How to Create a Power BI report using MySQL data

Introduction

In this article, we will learn to create a Power BI report using MySQL data. The article will teach us to use the MySQL Workbench (something like the SSMS, but for MySQL). We will do the following in this article.

First, we will connect and create a database in MySQL.
Secondly, we will create a schema (database).
Thirdly we will create and import data into a MySQL table.
Also, we will connect to Power BI and load data from MySQL.
Finally, we will create a Power BI report using MySQL data.

Requirements

First, you need to install MySQL on your Windows Machine. This tutorial is oriented to Windows environments make sure to install the MySQL Workbench which is included in the installer.
Finally, you need to have Power BI Desktop installed.

Create a database in MySQL

First, open the MySQL Workbench and press the + icon to open a new connection.

add mysql connection

Secondly, write a connection name of your preference, write the root password, and press OK. The root password is set during the MySQL installation.

connection port username used

Thirdly, the software will ask you for the root password. Write a password and press OK.

mysql root password

Finally, you will be connected to MySQL using Workbench

workbench mysql

Create a schema (database) in MySQL

Now, create a new MySQL schema. First, go to the Schema tab.

Workbench navigator

Secondly, right-click and select the Create Schema option

Create schema mysql

Finally, write a name for the schema. In this example our schema name is games. Press the Apply button. In MySQL the schemas and the databases are synonymous.

schema name

Create and import data into a MySQL table

To create a table, we will import data from the players.csv file. Download the file in your machine. First, right-click the Tables in the games schema and select the Table Data Import Wizard option.

Table Data Import Wizard

Secondly, press the Browse button and select the players.csv file, and press Next.

select file to import

Thirdly, select the Create new table option. Optionally, you can select the Drop table if exists if you want to delete an existing table with the same name.

Create new table

In the configure import settings, you can set the encoding and the data type (field type). In this example, we will not modify these properties. Press Next. For more information about data types, refer to this link.

Configure Import Settings

In addition, in the import data settings, press Next.

Import data

Next, in the Import Results windows press Finish.

Import results

Finally, to verify that the data was imported successfully, right-click on the player's table just created in Tables and select Select Rows – Limit 1000.