Implementing Power BI - Part 1

Power BI

Power BI is a Microsoft product. It is used as a tool for developing and creating business analytics. It provides interactive visualizations for creating reports, charts, tables, and dashboard. Power BI is referred to as a self-service business intelligence tool where the end-users can create reports and dashboard by themselves. End-users don’t need to be more dependent on an IT person or developer to develop their reports, charts or dashboard. It is a cloud-based service. We can use Power BI on desktop, web, and even Android devices also, it provides natural language query for customizing and viewing of data. In Power BI, we can import customized visualization from store to fulfill user requirement.

It is easy to learn and implement as it provides drag and drop facility to develop the reports or dashboards. Power BI desktop is free for the users for development. It is quick to develop and use.

Data Sources in Power BI

Power BI provides facility to connect any data, anyway, anywhere, and all in one view. There is a large number of lists which can be used as a Power BI data source.

Different types of the categories used as a data source are: All, File, Database, Azure, Online Services, Other.

Below are the categorized lists of data sources.

1. All – This category includes all the data connection types from all categories.

2. File - It includes Excel, Text/CSV, XML, JSON, Folder, SharePoint Folder.

3. Database– This category includes SQL Server Database, Access Database, SQL Server Analysis Services Database, Oracle Database, IBM DB2 Database, SAP Business Warehouse server, etc.

4. Azure– This category consists of the following types of data connections -  Azure SQL Database, Azure SQL Data Warehouse, Azure Analysis Services database (Beta), Azure Blob Storage, Azure Table Storage.

5. Online Services – The data connections under Online Services are Power BI services, SharePoint online list, Salesforce Objects, Google Analytics, Facebook, Dynamics 365 (online), GitHub, etc.

6. Other – This category provides the following data connections: Web URL, R Script, ODBC, OLE DB and Blank Query.

Connecting Power BI to Data

Now, we will implement and connect the data source in Power BI desktop for creating and developing charts, reports, and dashboard.

For connecting a file



For connecting and working with Excel, click on Get Data - Get Data File Option - Excel - Connect.

Select the file path as below and click "Open".



After this, a navigator window will appear. Here, you can select Excel sheet from the Excel file where the data is in your sheet. When you check the Excel sheet, the data will be displayed in preview section.



After clicking on Load button, a dialog box will appear which shows that the data model is loading and the data loaded. It shows up in Power BI Visualization Data Section.

If you will click on Edit option, then the data which is previewed will open in Query Editor window where you can edit the data also. After editing the data, you can click on "Close". Here, you can add, edit or delete column names also.

Below is the screen of the query editor window. After modifying the data, you can click one of the three options - Close & Apply, Apply, and Close. The Close & Apply option commits the changes done and closes the query editor window. Apply option only commits the changes done but doesn’t close the query editor window whereas the Close option only closes the query editor window but doesn’t reflect or commit any changes done.



Applied Steps

This is the collection of steps done at the time of data modeling, the changes done at the time of data correction and modification. When we do anything like adding a column, editing a column name, deleting a column or whatever changes we make regarding the data modification, they are added as a step to reflect that particular task or changes. Applied steps give us the facility of undo. Deleting any steps, the changes will revert in previous steps. You can just right click and delete any step for reverting back to the previous stage of data.

Changing the name of table

The name of table can be modified in the following ways. You can either right-click on table name or double click on and click on Table name or click on ellipses which is near the table name or you can select and click F2 for renaming the table.



Creating Chart in Power BI from Data

For creating a report in Power BI, go to the Report section of Visualization. Here, you can select different types of table or chart for creating report and chart.

For selecting a chart, you can select and drag it to the Report section. You can also double click on chart for selecting a chart or table.



Creating Report like Table in Power BI

For creating a report from the table, I am going to repeat the same steps as mentioned above. First, import the data from Excel and select table from Power BI Visualization pane.

I have imported a new Excel sheet for reading data and creating report like table. After that, I have just dragged and dropped the column value to Values option of table as below.



We can export the data of table or report shown above by clicking on ellipse icon which gives export data option. We need to click on export data and select the file location and extension of the file.



Connecting Power BI to Folder

We can connect folder in Power BI to work with similar types of files. Consider a scenario of a company if a manager has assigned similar types task to two or more than two sub-ordinate employee. The task is to collect some information in excel sheet. The column is same. After completing the task of data collection by each employee in different excel sheet. Now manager want to combine all these excel sheet into one for analysis the data.

Now, for this one way is to copy all excel sheet one by one and paste it in a separate excel sheet.

But, this is very tedious job in case the number of files are more than 5 or records in each excel sheet are in thousands or lakh.

To overcome this problem and combining all these Excel sheets into one Excel sheet, Power BI has given a powerful technique.

Below are the steps to work with -

Click on Get Data - File - Folder - Connect.



Now, click on the Browse button to select a folder path and click on OK.



After clicking on OK, you will get the following screen. This screen shows the file information like Name, Extension, and Folder Path.

Click on "Combine & Edit" option as highlighted below.



After clicking on Combine & Edit option, now the following window appears. Click on "OK" here.



The following screen will appear.



In the above screen, you can edit the source name column by right clicking on that column. Delete the file name and click on "Close & Apply" option available in the left corner.



As the result of all the above steps, finally we get the data in Power BI visualization data pane as below. Now, you can develop your report or chart with this data.



I want to use this data to create a pie chart. For creating a Pie Chart, just go to Visualization Report section and double click on Pie chart of visualizations. To display data, drag and drop column in legend section and Values section after selecting the report.

Now, the Pie chart is ready.



Connecting and Cleaning of Web Data in Power BI

So far, we have learned how to work with data connections with files, such as Excel and folder. Now, we shall learn how to work with web URL in Power BI. As we know, internet is one of the powerful collections of data. We can get any type of data on the internet. So, we can implement that data for analysis in Power BI.

I am going to use Wikipedia for web data connections.

Type state-by-state population in India like below.



Click on the list below and copy the following link for working.

https://en.wikipedia.org/wiki/List_of_states_and_union_territories_of_India_by_population

Click on Get Data - Other - Web - Connect.



After that, the following window will appear. Paste the above copied link here and click OK.



Now, in below window, select the authentication. I have selected Anonymous.



In the next Navigator window, the list of table and preview of data will be displayed as in the below screen.



After clicking the Load option, you can get the data in Power BI Visualization data pane.



Now, you can see that the data shown here is not in cleaning format. So, we need to do some transformation for cleanup of data.

Summary

So far, we have learned the basics of Power BI with their definition and working in Power BI. We have learned about the different data connections and also, how we can work with that data connections. All the above example and screenshots are from a real example. I have also attached the sample files of Excel which are used in this explanation. We will learn about more data connections such as text files, JSON, and XML files in my next article. Hope you will learn and enjoy this article. Your appreciation, comments, and suggestions are welome.