Weekend with Microsoft Power BI — How to Import data and perform data transformation.

In our previous series we learnt about the capabilities, components, main features and in all the interface of PowerBI.

This weekend’s tutorial we’ll be learning data importation and transformation.

To start with, we need to import the data from our data source. Like earlier said in the previous tutorial Power BI allows you connect to diverse data sources.

In this practical, we have chosen to use “ Country Population by Year dataset”. Our next step, Click on Get Data on the Home tab at the very top of the Power BI window.

When you’ve clicked on Get Data, you’ll be prompted to choose which data source you want to import from and file format to use. We will connect to local file of Excel “xlsx format”.

Navigate to the folder where the data is located on your system, choose it then click on “Open”.

When this is done, you’ll get a preview of your data in the table form. Now most times the raw data you get is not clean “messy data” and peradventure it is, you still need to double check.

Now before you load in your data, you need to ensure the quality of the data is top notch in other words “clean” hence the need to modify the data in “ Power Query Editor” by clicking Transform Data.

The Power Query Editor is dedicated to enable and display data transformations, another great feature of power query is that it keeps all applied steps for each transformation you carry out such that when you refresh or load in new data it applies the already stored steps, it basically saves you the stress of having to retype the steps all over again. GREAT RIGHT!

Analyzing information requires structured and accessible data for best results. Data transformation enables organizations to alter the structure and format of raw data as needed.

Data Transformation involves performing various methods of data cleaning which include but not limited to; destructive(deleting duplicate fields and unwanted records), constructive(adding, copying and replicating data) and structural(renaming of fields, changing data type, moving and combining columns in a database).

Country population by year data loaded in power query editor

Having a look at the data, There are minor problems here — the structure of the data isn’t in the right format, there are null values, the column headers are not in the right position, the years are meant to be in one single column to show for each country.

We want to transform all these problems so here we go!

DATA TRANSFORMATION STEPS

1. Understand the context of the dataset by knowing what insight you need to generate and which columns are relevant to your analysis.

2. Delete the first 3 rows as they contain irrelevant data “noise”.

3. After deleting the noise, Use the first rows as headers to ensure proper column names.

4. Ctrl select all the years asides the Country Name, right click then select on unpivot columns or click where the arrow on the transform tab is pointed, select unpivot columns to transpose from columns to rows so we can have all the years in a single column assigned to different countries.

5. Click the Check the data type on the home tab or right click each of the column headers to ensure each column is in the right data type format.

NB —

Looking at the small icons on the column headers you can easily detect the data type and decide to change it to suit your need.

6. Close and Apply to load into your PowerBi data view for analysis.

Now your data is ready to be worked with, you’ll be able to view it in the data view section to perform your analysis and view it on the field panel for visualization

Conclusion

In this tutorial, we’ve learnt how to import data from a data source, the importance of data transformation and how to perform some data transformation techniques on our dataset.

Effective Storytelling with data is an essential step in the data analytics pipeline

In our next tutorial we would be diving into an Introduction to Data Visualization with Power BI.

NB —

Get started with using Power BI by reading up the last tutorial series https://medium.com/@debbyalenkhe/weekend-with-microsoft-power-bi-a-beginners-guide-from-data-to-actionable-insights-5e8a8243024d

See you in the next read!