Data wrangling at Nolex Luxury Watches
Individual graded pledged assignment MP9
Goal
Learning about data wrangling / data cleaning
Learning to use Power BI
Prep work
Watch: Power BI tutorial (27 min)
Watch: Power query tutorial (16 min)
Context
In 2021 the luxury watchmaker Nolex Watches opened three 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, and I would like from you a real BI tool, a dashboard looking like this sketch I made.”
“No problem, Boss. I am on it….” You smile, as you think that this is becoming your catchphrase...
Task
The challenge in this assignment is to figure out 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 googling it, or searching through the materials linked from the class deck. Copilot is also an excellent resource for help. However, 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. Again, patience!
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 'applied steps' and start over. Note: this 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 a-j 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. Merge them after replacing 'york' with 'null'
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 column 'Cities' and make sure that the type is Text (ABC), 'Dates' is Date. 'Sales' is fixed decimal.
6. Now you know why data wrangling is such a pain! 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, one textbox, and download (save as) 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: Format
visual > General > visual borders. Make all the columns dark green.
11. Save the PowerBI file as "MP9
YourLastname YourFirstName.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 the sketch, including the requested colors. All components of the reports must be active visuals. All data must be accurate. No need to create a web or mobile version.