In this blog, I am documenting a simple project on data analysis using Power BI.
Data Set: I have taken the dataset from http://data.un.org/
Here is the direct link http://data.un.org/_Docs/SYB/CSV/SYB62_285_201904_Research%20and%20Development%20Staff.csv
Data Import to Power BI:
- Let’s start with Get Data option under Home tab. As this is a csv file, select Text/CSV option from the drop down list
- Select the file named SYB62_285_201904_Research and Development Staff.csv (I had renamed the file based on my requirement)
- After selecting the file, data will be displayed in below format
Here we can select Load or Transform Data option. I have selected Transform Data option to open Power Query Editor.
Data Transformation with Power Query Editor:
In Power Query Editor, I am making some changes in terms of column renaming, data type modification, data value updating. We can explore some important features like Column Distribution, Column Quality etc. These were helping to prepare the data for visualization.
- Rename 2ndcolumn → Country Name
- Modify data type of field “Value” → Fixed Decimal Value
- Modify the values of Series field: Right click on Series column and displayed the properties. Select “Replace Values” and replace with desired value.
These views are self-explanatory. Using these we can find out the answers of following questions
- How is data distributed
- How much data is valid
- What is the error rate
- Missing values
- Distinct , Unique values information
Data is ready and click on Close&Apply button to save the data.
Power BI Editor
Now we are in main Power BI editor where model, data and report sections are present. For this analysis, there is only one table, so I have not used Model area and as well as there is no special requirement Data area.
- Need to convert the field type from measure to dimension. Select the required fields and below option will be available
- Under Column tools → Summarization -> Select “Don’t Summarize”.
Report / Page Creation
Let’s time to explore some visualization.
In the Overview Page, I am trying to capture reports on total personnel who had participated for research and development.
- Filled Map : Country wise Total R & D Personnel
- Line Chart : Total R & D Personnel by Year
- Two Cards: Total R & D Personnel and Total Country Participated
Now it’s time to create one page with next level of information. In By Category page, created some reports on three type of personnel categories (researchers, technicians and othering stuffs)
- Clustered Column Chart: Personnel Number by Category
- Stacked Bar Chart: Category wise Personnel by Country
- 100% Stacked Column Chart: Category wise Personnel by Year
Publish to Power BI service
To publish the report, go to File tab, select Publish and the My Workspace. Here are some
Now login to Power BI Service url ( https://app.powerbi.com/) and go to My Workspace.
Click on Research and Development Analysis report and displayed the below pages.
Have a look on in built navigation feature of Power BI.
Click on “Total Technicians” from the first report, the corresponding data is highlighted in other reports
Originally published at http://arpitatechcorner.wordpress.com on August 12, 2020.