MP9: Nolex

Data wrangling at Nolex Luxury Watches

Goal

Learning about data wrangling / data cleaning and practicing using Power BI.

Prep work

To do this assignment you need an intro to a powerful tool, widely used in business: PowerBI.

Watch: Power BI tutorial (12 min)
Watch: Power query tutorial (16 min)

Note: Microsoft constantly updates the tool. The version you are using may look a little different than the one presented in those videos. No worries!

Note:  Power BI is available only on Windows. If you have a Mac use a lab. If you are not Comm, go to the help desk in Shumway and get auserid and password. It might take time. Start now.

Context

In 2021 the luxury watchmaker Nolex Watches opened three new shops in three airports: New York LaGuardia, Paris DeGaulle and Rome Fiumicino. Michelle Leclock, Chief Data Officer at Nolex Watches and your currents boss, is in charge of reporting the performance of the stores to the C-suite.

“Holy Nolex! I can’t believe this!”
You seem to have developed a skill to walk into her office at the right (wrong?) moment. Ms. Leclock looks upset.

"Good morning Ms. Leclock, how are you today?"

“Come and see this!” says she as she gives you a printout. “I asked for a report on the sales at our airport shops and THIS is what they send me! A PDF! What were they thinking? What am I supposed to do with it, retype a thousand numbers on a spreadsheet? And look at that! Half of the sales in Dollars and half in Euros!… and typing errors!…so UNPROFESSIONAL!”

You sense what is coming.

“You know how to clean-up data, right? Please, see what you can do with this 'report' they produced. I will send you the file. Use a BI tool and build a dashboard looking like this sketch I made.”
“No problem, Boss. I am on it….”
You smile, thinking that this is becoming your catchphrase...

Task

The challenge in this assignment is to figure out on your own how to get stuff done in Power BI. Please do not ask me or the TA "How do I ....?"- type questions. Finding that out IS the assignment. It might require asking your GenAI, googling, or searching through the materials linked from the class deck. Clarification questions are always welcome. If you cannot figure out how to do something, leave it undone for partial credit.

Data wrangling (a.k.a. data cleaning, remediating, or munging) is the gathering and transforming raw data into formats ready for analysis. Data wrangling is a key phase in the preparation of a BI report, and often the most time consuming. You will discover that it requires attention to detail, patience, ingenuity, and perseverance. Give yourself plenty of time.

1. Start PowerBI and load the PDF. At this time, PowerBI only runs on Windows, so you might need to go to the RRH labs. Consider carefully what to load and be patient: it might take a while. You may skip the auto transformations.
2. 'Load' and append the data. It might take time.
3. Rename the resulting dataset "Airport Sales"
4. Check carefully the columns: use view > column quality, distribution, and profile. Figure out what these tools do.
5. Clean up all the problems you see. This step will require patience and persistence. If at any point you make a mistake, no worries! Just delete the last 'applied steps' and start over. This step is a bit like a puzzle. There are many different ways to achieve this objective, and different orders of actions. Do not follow the hints below blindly. Think about what you are doing!

a. remove top and bottom
b. eliminate the rows with the Totals
c. merge the columns with cities and fix the misspellings
d. merge the date parts
e. figure out what happened to the money columns.
f. duplicate the merged money column. Right click on the title of one of them. Change type using locale > fixed decimal number > Italy. This will solve the dots and commas problem. OK to have errors.
g. multiply the Euro column times 0.99 (euro/USD exchange rate at the time).
h. delete 'USD' from the other column (replace it with nothing). Change type >using locale > fixed decimal number > US. OK to have errors
i. replace errors with 'null' in both columns and merge them
j. remove the columns other than the ones that contain the cities, the dates, and the money
k. call the column 'City' and make sure that the type is Text (ABC), 'Dates' is Date. 'Sales' is fixed decimal.

6. Now you know why data wrangling is so time consuming! Home > Close and apply. You can go back to PowerQuery anytime you like or need.
7. Back in PowerBI, click the Table button on the left side. Find the table you have created and check it out.
8. And now the fun part! Look at the sketch from Mrs. Leclock. Click the report button on the left side of PowerBI's main screen and drop six visuals and one textbox. Download, save, and then insert the Paris store image.
9. Expand 'Airport Sales' on the right of the screen and pick and choose the data that you need to activate the visuals.
10. Play with the formatting controls to make it look like Ms. Leclock's sketch. Align the visuals. Add borders. Make all the columns dark green, etc. This is not busywork. You are learning the PowerBI controls as you are doing this.     
11. Lastly, save the PowerBI file as "MP9 YourLastname.pbix", drop it in your BOX folder and pat yourself in the back. Another job well done!

Criteria

The report should look as much as possible like Ms. Leclock's sketch, including the requested colors. The closes to the sketch, the more points.  Make it look professional: well-aligned items and borders, well-organized fonts, matching colors, matching formats, etc. All components of the reports must be active visuals.  All data must be accurate. No need to create a web or mobile version.