BYU Student Author: @Jacob_Dutton
Reviewers: @Jimmy_Han, @James_Gerstner
Estimated Time to Solve: 15 Minutes
This is an intro challenge that is part of the Power BI Learning Path.
We provide the solution to this challenge using:
- Power BI
Need a program? Click here.
Overview
You’ve just landed the role of Financial Analyst at Peak Cycles, a bustling bike shop known for its quality and variety. Your first task is a vital one: dive into our shop’s data, clean it up, and get it ready in Power BI for analysis.
Your Mission:
Load It: Bring our sales, customers, and bikes data into Power BI.
Clean It: Tidy up the quirks in our data using Power Query—think names without spaces and inconsistent phone numbers.
Analyze It: Set the stage for revealing key insights that will drive Peak Cycles forward.
Your insights will guide our strategy, impacting everything from inventory to marketing. Ready to make data-driven decisions your superpower? Let’s get started!
Instructions
Loading Data into Power BI
- Open Power BI Desktop: Start a new report by opening the Power BI Desktop application.
- Get Data: Click on the “Get Data” option in the Home tab. Select “Excel Workbook”
- Select Files: Navigate to where you’ve saved the Excel file (download this from the challenge page).
- Select Data: Check the box for all 3 sheets of data.
- Load Data: Click “Transform Data”, which will open Power Query Editor.
Cleaning Data in Power Query: The only sheet data that needs to be cleaned is on “Customer Info”. Split FullName into FirstName and LastName. Remove any dashes from the PhoneNumber column.
- Open Power Query Editor: If not already in the Power Query Editor, you can access it by clicking “Transform Data” in the Home tab.
- Changing FullName to FirstName and LastName:
- Select the FullName column.
- Go to the “Transform” tab, click “Split Column” > “By Delimiter”.
- Choose “Space” and click “OK”.
- Select the FullName.1 column.
- Go to the “Transform” tab, click “Split Column” > “By Lowercase to Uppercase”.
- Now there are 3 name columns. Hold the “Control” key and Select FullName.1.2 and FullName.2.
- Click “Merge Columns”. Name the new column LastName.
- Right click FullName.1 and rename it to FirstName.
Standardizing Phone Numbers:
- Select the data type setting on the PhoneNumber column (this is the ABC123 to the left of the column title.
- Change data type to Text.
- Select the “Replace Values” option.
- Input “–“ as the value to find, and leave “Replace with” blank.
Apply Changes: After cleaning, go to the home tab and click “Close & Apply” to apply the transformations and return to the main Power BI window.
Final Steps: Now that your data is cleaned and loaded into Power BI, you are ready to start building visuals!
- KPI Card:
- If not already in on the report view, click the report view button in the left hand margin.
- Drag a Card visual onto the canvas (the card visual has a 123 on it).
- Drag “TotalSalesValue” from the data pane onto your card visual. The total should show “3.99 M”
Create a visual of your own: Select any visual from the visuals pane and add data to it. See if you can create something that would be useful in real life. Save your Power BI file as pbix.
Data Files
Suggestions and Hints
Make sure to select close and apply after doing your data cleaning in Power Query.
Solution
Challenge145_Solution.pbix
Solution Video: Challenge 145|POWERBI – Peaks Cycles
