After creating a dashboard in Excel, I proceeded to try my hand at building a dashboard in Power BI — the one renowned for its interactive data visualizations. In this project, I also conducted data cleaning before visualizing and creating a dashboard. Alright, Let’s get started!
Data Understanding
Okay, so the data is about a survey conducted by Alex (I found it in his Power BI project YouTube video). It’s a survey of data professionals.
Here’s an overview of the raw data (actually I tried to screenshot the table but there are too many columns — 28 columns — to capture):
The data contains 630 records.
So, based on the data above, I will present visualizations containing information about preferences, needs, and trends in the data professional industry.
Data Cleaning
Firstly, I conducted data cleaning to prepare the data for analysis. I did this in Power Query. The first thing in this cleaning process was to remove the columns Browser, OS, City, Country, and Referrer because these columns had no values.
[Get the “Other” value]
There are several columns with “Other (Please Specify)” values. What happened was, during the survey, several options were provided as answers, but respondents could write their own answers if they chose the “Other” option. I thought some of the other values could provide useful insights. So, I wanted to get the “other” value.
Okay, so these are the values from the column Q1 — Which Title Best Fits Your Current Role?
Five options are provided as seen in the image. But there are also many “other” entries. So, I think it’s insightful if we can know what's the other value so we shouldn’t be limited to just 5 roles. So, what I did was:
1. Created a new column containing the other roles by extracting text after the delimiter ‘:’. (For the original column, I extracted text before the delimiter ‘ (‘ leaving only the word “Others”).
2. For the new column, I removed any extra spaces with trim.
And here’s the longest part of my data cleaning. You know, people don’t fill in the “other” entries consistently, even though it’s the same role, they type them differently. And I kept trying to find the most efficient way to address this, to normalize these values.
I found that I couldn’t effectively use the Column From Examples or Conditional Column features. Moreover, using the Replace Values box for each value was too time-consuming.
3. So, I resorted to a more manual approach — I wrote the replacement values in M Language after identifying the existing values and what they should be replaced with.
I don’t know what it’s called. But I used that box to write the command? the query? (not sure). Click the “fx”, and you can write in that box. I really don’t know if I have done the most efficient way or not, but so far let’s just use that. (I think it can do better with Python or something else but for this one, I want everything to be done in Power BI).
4. Okay, next. I have clean data regarding the other roles. What I did next was to merge the original columns with the other role columns. So I have data for all roles. Previously, I had replaced the “Other” values with “”, yes, no value, so when merged, it wouldn’t corrupt the data I had cleaned up.
Nice!. The roles have been cleaned.
Next, I cleaned the columns Q4 — What Industry do you work in?, Q5 — Favorite Programming Language, and Q11 — Which Country do you live in?, with the exact same steps as the role column. So, I obtained the specified value for “other”.
[Just Keep “Other” for Value]
For Q8 — If you were to look for a new job today, what would be the most important thing to you? and Q13 — Ethnicity, I didn’t keep the specified value, I just made it all “Other”. Because I think the options provided already provide enough information.
[Change Salary Value to Numeric (Count the Average Salary)]
And the last, I did something with Q3 — Current Yearly Salary (in USD). Because the value is in the form of a range (string), so I think it’s better if it’s in numerical form. First, I used split column by digit to non-digit.
After removing the non-digit column, I have 2 digit columns, let’s call them Range A and Range B. And… I created a new column, namely Average Yearly Salary, containing the average of the values in Range A and Range B. Again, I don’t know if this is the best way or not (and actually, I followed Alex’s steps). Or should it be the median?
Okay, that’s all my data cleaning steps! Yay!
((I hope I cleaned it up properly))
Data Visualization
After completing the data cleaning process, it’s time to visualize the data for easy understanding and valuable insights! In this section, I will present data visualizations to analyze preferences, needs, and trends in the data professional industry.
1. Level of Happiness
From the entire Q6 column, we can determine the happiness level of respondents in their positions related to several factors: salary, work/life balance, coworkers, management, upward mobility, and learning new things. The happiness level ranges from 0 to 10.
I created a visualization in the form of a Gauge. From this visualization, we can easily see whether the average happiness level has reached the maximum value or still has room for improvement. The lowest average happiness level is associated with Salary, while the highest is related to coworkers.
2. Important Things for Job Seeker
From the Q8 — If you were to look for a new job today, what would be the most important thing to you? column, we can understand what important factors job seekers consider when looking for a new job.
I created a visualization in the form of a pie chart, which clearly shows the most important things for job seekers. 47.22% of respondents identified better salary as the most important factor in seeking a new job.
3. Respondent Job Title
This visualization shows the top 10 respondent job titles obtained from the Q1 column. I created the visualization in the form of a bar chart.
From this visualization, it’s evident that the majority of respondents are data analysts. We can also observe several other respondent job titles participating in the survey.
4. Average Yearly Salary by Job Title
This visualization helps understand the distribution of annual salaries among data professionals. The values are taken from the Average Yearly Salary column. I created the visualization in the form of a horizontal bar chart.
From this visualization, we can see the average annual salaries for each job title, providing insights into the typical salaries for different roles.
5. Distribution of Job Title Across Industries
Next, I explored the distribution of the Top 7 job titles in the Top 10 Industries. This visualization helps understand job profession needs across industries. I created the visualization in the form of a stacked bar chart.
From this visualization, we can see the distribution of jobs across various industries. However, it’s essential to note that this may be influenced by the fact that the majority of respondents are data analysts.
6. Favorite Programming Language
This visualization helps us understand trends in programming language preferences among data professionals. I created the visualization in the form of a pie chart.
From this visualization, we can identify the most popular programming languages among respondents, which can guide those looking to improve their programming skills. Python is the favorite programming language for 70.47% of respondents.
Dashboard (Report) Creation
After producing strong visualizations from the data, the next step is to create an interactive dashboard that summarizes key findings and allows users to explore the data more deeply.
Dashboard Layout
Before attempting to create the layout, I determined what would be included in my dashboard, which is:
- Respondent demographics (total number of respondents, total number of countries contributing to the survey, distribution map of respondent countries, and respondent education level information)
- Key findings or primary visualizations (6 visualizations from the previous section — data visualization)
- Slicers (dashboard interactivity to enhance the user experience and provide flexibility in exploring the data.)
Now, having decided what will be included in my dashboard, I started arranging the layout. I experimented with positioning or resizing existing components to get the right overview and layout. Here is my final layout:
- Section 1: It’s clear that this area is for the title, right?
- Section 2: I’ll place demographic information here.
- Section 3: Area for visualizations more related to personal insights and job satisfaction perceptions.
- Section 4: Area for visualizations more related to job-related trends.
- Section 5: Area for several slicers.
The slicers I will add to the dashboard are age range, quarter date taken survey, and job title dropdown.
Dashboard (Report) Looks✨
Ok, so this is the final look of my dashboard!
For the dashboard theme, I utilized a theme from Power BI with a few modifications. I also enabled shadows for all the main visualizations, which, in my opinion, makes the dashboard look more dynamic. And of course, it’s interactive, but the one above is just an image.
Conclusion
Yay! That was my first dashboard project in Power BI! It was an exhilarating journey from cleaning the raw data to crafting interactive visualizations and creating an insightful dashboard. I spent a lot of time on data cleaning, but it led me to explore power query — and even using M Language. In the data visualization and dashboard creation stages, I delved into Power BI’s visualization tools, experimented with filtering or presenting useful information, and tried to craft a well-organized layout. I’ve learned a great deal about Power BI from this project! I’ve learned so much along the way and am thrilled with the results! I will continue learning and exploring to create even more impactful and insightful dashboards!
And a big thanks to Alex The Analyst and Pavan Lalwani for accompanying me on this Power BI journey!
