Business Analysis can fall into any part of a business capability and it does so very well in Data Analytics.
Presented here is a project undertaken over the past week that encompasses the goal of building a dashboard using Power BI to understand the enrollment data over the past 3 years by district in the state of Massachusetts.
This was the expected outcome
The files for the project are available on github located here:
https://github.com/rlis123456/dataanalytics
Here are a couple of steps taken to build the dashboard:
Step 1: Import Data Files into power BI:
These are excel files that were downloaded from the Massachusetts Department of Education. I noticed the files were separate for each year.
Step 2: Data Transformation:
I cleaned the data from all three files as follows and automated the process of cleaning the data for all files>
- Stripped Headlines and left the headers as row 1
- Removed Totals at the botoom
- Removed note that Data for enrollment less than 6 students not included in spreadsheet
- Stripped any records that had *** based on the data file
Step 2: I modeled the data.
Since all the data labels in all three files were the same aka the column names were the same, I added a column for the year on each file.
I then created a separated file in Power BI to append the data from all three files. I also created a date file that consisted of the ORG_Code and the Year. Why did I do this? This was done so that I could distinguish between a granular table versus Fact Table.
A Fact table = Transactional or in this case the enrollment data
Granular Table = Date table
Step 3: Now comes the fun part! Build the Dashboard.
For this effort I decided to keep the Dashboard Simple. These were the requirements:
Buid a Dash board to include
A. Year 20,19 and 18 Data on enrollment by Grade and SPED Beyond Grade 12
B. Card – Total Enrollment in Massachusetts
C. Splicer – By School Districts and year
I spent a few days figuring out dashboard and the filters.
Are you interested in learning more about how I built the dashboard? Please comment below.
