Building a Power BI dashboard for my workplace

Data Visualization, Power BI, Excel

Project Summary:

As I became more familiar with Power BI, I thought it would be good to practice on a real dataset from a real business to experience what data analysts see and work on themselves in their everyday lives.

I chose to work on our inventory data because it is the most frequently updated and utilised data source in my workplace. Each day, many parcels would arrive at our warehouse and operatives would scan and record the tracking numbers, sender’s name and quantity. Another reason is that this could potentially answer some of our business problems. We usually lose track of how much stock our warehouse held, trends of incoming goods being received, and the lack of response to old stock.

The dataset consists of 16 excel files, each corresponding to one of our agents. These files are kept up to date to reflect daily goods received accurately. The sheets are regularly sent to our agents to validate the status of shipments.

I first loaded all the excel files into power BI and examined how data are stored. It turns out the formats of the files is not kept to a single standard, and there are many cases of null values, duplicates and error values due to a lack of data validation. I then performed data cleaning and wrangling in the power query editor, eventually removed useless columns, corrected data types of some columns, replaced and fixed error values.

Finally, I built two dashboards using measures and calculated columns in DAX and visualization tools. The first dashboard is about recent goods received, including numbers of parcels/pallets received and their overall trend. The other dashboard identifies old stock so that our operatives can remind the corresponding agent/customer about the situation.

As the dataset and dashboards contain sensitive BI information, I cannot publicly share the Power BI report. However, here is a galley of the screenshots of the project, including dashboards and how the dataset looks like pre-cleaning, with all sensitive data blurred for your consumption.

1
First dashboard
1
Time period filter in action
1
Additional insights from customer filter
1
Second dashboard shows aged stock
1
Example of messy data 1: many sheets cramped into a single file
1
Example of messy data 2: Long data in one sheet but bad validation
Select Your Color
Theme Option