Preparing Data For Power BI Report And Dashboard

Introduction

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

Import Data from Data Source

The first thing is we have to load the data from the data sources. In our previous articles we have discussed this, please feel free to refer to those just in case if required.

For this article, we are going to use SQL Server as our Data source. Now let's connect to our SQL Server Data source by providing the Server name and Database name by selecting the Get Data from Ribbon tool and choosing the SQL Server as the data source option and loading the data.

Preparing Data For Power BI Report and Dashboard
Fig.1 Choose SQL Server as Data source and provide the server name and database name to connect with

Transforming Data

Sometimes after loading the data into Power BI, the data might not be incorrect 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 do in all reporting is to eliminate some unused columns. We should always keep the columns that are really in need. We can eliminate the unused columns at any time. And also it's really easy to add a new or deleted column back to the report.

Sample

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

Preparing Data For Power BI Report and Dashboard
Fig.2 Choose Transform Data option from the Home Ribbon Tool

From the loaded data, let's remove the CreatedBy Column since we no longer required it in this report.

Preparing Data For Power BI Report and Dashboard
Fig.3 Columns that are loaded from the database table

It is 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 be present in the report.

Preparing Data For Power BI Report and Dashboard
Fig.4 Remove the Unused column from the Report

Filtering by Row

Another common transforming thing that we do is Row filtering, which is the same reason as eliminating the columns. We essentially keep row data that are really required. And also it is 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 let us see how we can apply filters for the rows. If we click the dropdown from the column name, we can apply the filter.

Preparing Data For Power BI Report and Dashboard
Fig.5 Applying Filter for the CreatedDate Field

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

Preparing Data For Power BI Report and Dashboard
Fig.6 Applying the Filter for the CreatedDate Column

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

Preparing Data For Power BI Report and Dashboard
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 let us rename the CreatedDate column name to Date. For that, we have to double-click the column to select and rename it.

Preparing Data For Power BI Report and Dashboard
Fig. 8 Select the column by double-clicking the column name

After renaming the column heading looks like,

Preparing Data For Power BI Report and Dashboard
Fig.9 After renaming the column name

Conclusion

Here we discussed a 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 reading other articles