Preparing Data For Power BI Report and Dashboard

Introduction

In this article, we will discuss about the steps that we need to flow to prepare an Interactive Reports and Dashboards using the Data in Power BI.

Import Data from Data Source

First thing is we have to load the data from the data sources. In our previous articles we have discussed about this, please feel free to refer those just in case if required. For this article we are going to use SQL Server as our Data source. Now lets connect to our SQL Server Data source by providing the Server name and Database name by selecting the Get Data from Ribbon tool and choose the SQL Server as the data source option and load the data.

Fig.1 Choose SQL Server as Data source and provide the server name and database name to connect with

Transforming Data

Sometime after loading the data into Power BI, the data might not be in correct form to prepare the report. But no need to worry about this, Power BI provides lots of options to transform those data.

Filtering by Column

The common transforming thing which we commonly does in all reporting is to eliminate some unused columns. We should always keep the columns those are really in need. We can eliminate the unused columns at any time. And also its really easy to add a new or deleted column back to the report.

Sample

From the imported data we want to remove few columns which are really no need in this report. To do this, we have to select Home from the Ribbon tool, and the select the Transform Data.

Fig.2 Choose Transform Data option from the Home Ribbon Tool

From the loaded data, lets remove the CreatedBy Column since we no longer required in this report.

Fig.3 Columns that are loaded from the Database table

Its very easy to remove the column from the report, just right click over the column name and we can see Remove from the option list and click Remove. Once clicked, the column will no longer present in the report.

Fig.4 Remove the Unused column from the Report
Filtering by Row

Another common transforming thing which we does is Row filtering, which is same reason as eliminating the columns. We essentially keeps row data those are really required one. And also its easy to add a new or deleted row back to the report again. The Power BI supports both simple and complex filtering which means, in a simple filter we can eliminate the data or else by applying more queries with AND and OR operators we can eliminate the data.

Sample

Now lets see how we can apply filter for the rows. If we click the dropdown from the column name, we can apply the filter.

Fig.5 Applying Filter for the CreatedDate Field

Now if we look at the above image, we can see all the Date’s are applied by the checkboxes as selected. Now we can uncheck all and check the required.

Fig.6 Applying the Filter for the CreatedDate Column

Only those data will be displayed. Thus we have to apply the row filters.

Fig. 7 After Applying the Filter
Fixing Metadata

Another common thing is, renaming the Column name or fixing the datatype of the column. This process is called Fixing Metadata.

Sample

Now lets rename the CreatedDate column name to Date. For that we have to double click the column to select and rename it.

Fig. 8 Select the column by double clicking the column name

After renaming the column heading looks like,

Fig.9 After renaming the column name

Conclusion

Here we discussed few methods to Prepare data for the Power BI Report. There are some more methods to follow, which we will see in our next article. I hope this article was useful for you all. Please share your feedback in the comment section.

Consider to read other articles

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: