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.
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.
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.
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.
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.
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.
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 let us 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 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
