Introduction to Power BI — The Beginning of Data for many things Intelligence II

So, we are going to continue the journey of expressing data as the vital thing when making decisions. In the world of Business Intelligence, it is very important to understand your data — this will help you make informed decisions regarding the right approach to structure your dashboard so that decision makers will get the most out of it.

Like every tool, Power BI comes with it’s features to work with data. It has some cool features that allows you to do great data modelling before you create visualizations. Behind the scene Power BI makes use of Power Query and Power Pivot. These tools were introduced into Excel when Microsoft made the move to be relevant in the Big Data world, since one of their major data product, Excel, have a wide adoption for data manipulation and transformation, these tools help expand Excel to handle querying much larger data and data from different sources. Power Query gives Excel the capacity to transform millions more rows of data easily while Power Pivot allows Excel to get large data from multiple sources and work with it conveniently. These two tools have also been embedded into Power BI as it’s back-end data collection and data transformation machine.

1. Sample way of Connecting to a Database with Power BI

So, in practice, you will need to connect to a Database and get the relevant tables that will be required to build a dashboard. This I will do with a sample Access Database. You can get the data here.

Connecting to the database is pretty simple, Under Get Data pick Access Database and select the folder containing your DB.

Connect to Access Databse

Note: This may require that you have Access Database Engine Connector, you can download it here

Connect to the folder containing your database

After connecting to the database, you will be able to see the tables that are present in the Database.

Figure contains the list of tables in your Database. You can select each to view before you either Load or Edit.

You can now go ahead and select the tables you want to work with and either load directly into Power BI or edit the tables with Power Query, my approach usually is to edit the tables where I can then do some data transformations before sending to Power BI.

The tables in the database as represented with Power Query

This is a typical scenario of how you will interact with Databases in practice. Some other points to note:

a. Once you finish working on your data and you click close and apply Power BI automatically detects relationship between your tables, this is very great for visualization. More on Relationship later in next tutorial.

b. At the right end of the power query pane above is Applied Steps, this allows you to go back on some edit you do on your data and you want to take back.

2. Some Simple Data Manipulation:

This section makes use of the data used in Tutorial I, this is because it allows us to do some very basic things that form the big idea behind using Power BI for Business Intelligence and Visualizations with a very basic data.

We will load the csv containing Mexico sales only.

This is what you should have after loading Mexico sales data

We can extract out the date, month and year from the date column into separate Columns — Our approach will be :

• Click on the Date Column to ensure it is selected
• Right click on it to make a copy by selecting Duplicate Column, this is important so that splitting the column will not affect our initial column.
• Under Transform , Click on the drop-down in Split Column , then split column By delimiter.

Copy of Data Column and Split Icon with it’s drop-down

Note : — Ensure you observe what you can split your column with, in our case it’s a character ‘/’ that separates our data into Month, Date and Year. Also, the basic delimiters may not contain your separator, after you have checked your column well to know what separates it, you can create a custom delimiter.

Split column by a custom delimiter ‘/’

SN: You can choose where the values in your column splits at, splitting left-most delimiter will only split at the very first delimiter it encounters from the left and same goes for the right-most delimiter button. However in our case, we are splitting at every occurrence of the delimiter.

• Click OK, and your data will be separated into 3 Columns

• Rename the columns — right click each column and select Rename, in our case we rename the columns to Month, Day and Year.
• Load your transformed Data with Close and Apply

This is the view of your data from the ‘Data’ icon in Power BI

Another transformation we can do is to categorize our months with real month name

Approach:

• From Home bar, select Edit Query, this takes you back to the Power Query Pane.
• Select Add Column, and then Conditional Column. Conditional columns allows you to add a new column based on the condition from another column i.e. say I have a month column denoted with numbers 1–12, I could create another column based on that column by replacing each month number with the actual Month name, January, February…December.

Selecting Conditional Columns

• Conditional Column allows you to add if-else condition rules.

• Rename the column and make Visualizations

A typical visualization on the newly created column will look like this;

Typical visualization from the newly created condition column

Note that the month was sorted alphabetically rather than by the actual month sort, we can change this by sorting Month Name based on another column and in this case Month which contains the number representation for each month.

Approach:

• Select Data icon in Power BI
• Select the column you want to sort from the Field in our case Month Name.
• Select Modelling tab and select Sort by Column then pick Month

• Return and check Visualization

A final thing we will like to do with the Mexico sales data is to know how to create new table from an existing table in Power BI.

Approach:

• Select New Table from Modelling tab
• In the small space where we have “Table =”, you will create an expression of what you want the new table to have as it’s data. The space is not only used in creating table, it’s used for many calculation in Power BI. The language used for creating this calculation is called DAX — Data Expression Language. It is a very powerful language that allows you to do Data Transformation on the fly.
• You can change the table name to your choice before you create the expression by simply erasing “Table” and adding your own name. In our case we will create a table with the unique Months and their total Revenue.
  DAX code SUMMARIZE(‘MX Sales’, ‘MX Sales’[Month Name], “Total Revenue”, SUM(‘MX Sales’[Revenue])).
  What the code does back-end is to create a summary of your initial table, based on a grouping (by Month Name) and a calculation ( a Sum) over your groups. Finally it renames the aggregated column as “Total Revenue”.

This is a very basic approach to transforming data with Power BI, One vital thing that is often useful again is creating Measures. This and many more we will look at in coming tutorials.

You can go ahead and create some transformations using the Access DB, make visualizations and let’s see some of them in the comment section.