0% found this document useful (0 votes)
6 views17 pagesData Analysis Lab 2
The document provides a detailed guide on using Microsoft Excel and Power Query to manipulate and analyze data, including creating PivotTables, transforming data types, and cleaning datasets. It includes step-by-step instructions for importing data from various sources, correcting data types, and generating summary statistics in both Excel and Power BI. Additionally, it emphasizes the importance of data validation and proper data modeling in Power BI for effective analysis.
Uploaded by
39trantuCopyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% found this document useful (0 votes)
6 views17 pagesData Analysis Lab 2
The document provides a detailed guide on using Microsoft Excel and Power Query to manipulate and analyze data, including creating PivotTables, transforming data types, and cleaning datasets. It includes step-by-step instructions for importing data from various sources, correcting data types, and generating summary statistics in both Excel and Power BI. Additionally, it emphasizes the importance of data validation and proper data modeling in Power BI for effective analysis.
Uploaded by
39trantuCopyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
Microsoft | Excel + Power Query
Ses by Month Cola abs
rand Total
‘et
ire rr)
Seon or)
rand Ta 1995 2199 tise
Microsoft Exce!
LAB 2-2M Example of PivotTable in Microsoft Excel for November and
December
Microsoft | Excel + Power Query
1. Create a new blank spreadshe
Excel
2. From the Data tab on the ribbon, click Get Data > From File > From
Workbook. Note: In older versions of Excel, click the New Query button,
3. Locate the Lab 2-2 Slainte [Link] file on your computer, and click
Import.
4, In the Navigator, check Select multiple items, then check the following tables to
import:
a Finished_Goods Products
b. Sales_Order
¢. Sales Order Lines
5. Click Edit or Transform Data to open Power Query Editor.
6. Click through the table queries and attributes and correct the following issues:
‘a, Finished_Goods Products: 1. Change the data type for Product_Sale_Price to Curreney (click the column
hneader, then click Transform > Data Type> Curreney. If prompted,
choose Replace current conversion step.
page 81
b, Sales_Order_Lines: Change the data type for Product_Sale Price to Curreney.
¢, Sales_Order: Change the data type for Invoice Order_Total and Shipping Cost
to Currency.
6 Take a screenshot (label it 2-2MA) of the Power Query Editor window with
your changes.
8. At this point, we are ready to connect the data to our Excel sheet, We will only
create a connection so we can pull it in for specific analyses. Click the Home tab
and choose the arrow below Close & Load > Close & Load To...
9. Choose Only Create Connection and Add this data to the Data Model and
click OK. The three queries will appear in a tab on the right side of your sheet.
10. Save your workbook as Lab 2-2 Slainte Modelalsx, and continue to Part 2. Microsoft | Excel + Power Query
1. Open the Lab 2-2 Slainte [Link] you created in Part 1
2. Click the Insert tab on the ribbon and choose PivotTable.
3. In the Create PivotTable window, click Use this workbook’s Data Model and
click OK. A PivotTable Fields pane appears on the right of your worksheet.
‘© Note: Ifat any point while working with your PivotTable, your PivotTable
Fields list disappears, you can make it reappear by ensuring that your active
cell is within the PivotTable itself, Ifthe Field List still doesn’t reappear,
‘navigate to the Analyze tab in the Ribbon, and select Field Li
4, Click the> next to each table to show the available fields. If you don’t see your
three tables, click the All option directly below the PivotTable Fi
pane tile
5. Drag Sales_Order-Sales_Order_Date to the Columns pane. Note: When you
add a date, Excel will automatically try to group the data by Year, Quarter, and so
on.
a Remove Sales_Order_Date (Quarter) from the Columns pane,
6. Drag Finished_Good_Products.Produet_Description to the Rows pane.
7. Drag Sales_Order_Lines.Sales_Order_Quantity_Sold to the Values pane.
Note: At this point, a waming will appear asking you to create relationships
8. Click Auto-Detect... to automatically create relationships in the data model.
Click Manage Relationships... to verify that the primary key-foreign key
pairs are correct: 1. Sales_Order_Lines (Product Code) = Fnished_Good_Products (Product_Code)
2, Ssles_Order_Lines (Seles_Order_1D) ~Sales_ Order (Sales_Order_IO)
b. 4 Take a sereenshot (label it 2-2MB) of your Manage Relationships window.
©. Click Close.
9. In the PivotTable, drill down to show the monthly data:
a, Click the + nest to 2020.
b, Ifyou see individual sales dates, right-click Jan and choose Expand/Collapse
> Collapse Entire Field.
10. Clean up your PivotTable, Rename labels and the ttle of the report to something
mare useful, like “Sales by Month”.
LL. € Take a screenshot (label it 2-2MC).
12. When you are finished answering the lab questions, you may close Excel. Save
your file as Lab 2-2 Slainte Pivotaxlst. Microsoft | Excel + Power Query
Microsoft Exest
LAB 2-3M_ Example of Cleaned Data in
icrosoft Excel Microsoft | Excel + Power Query
Open a new blank workbook in Excel
. In the Data ribbon, click Get Data > From File > From Workbook.
Locate the Lab 2-3 Lending Club Approve [Link] file on your computer
and click Import (this is a large file, soit may take a few minutes to load).
. Choese LoanStats3e and click Transform Data or Edit. Notice that all ofthe
column headers are incorrect
First we have to fix the column headers and remove unwanted data,
. In the Transform tab, click Use First Row as Headers to assign the correct,
column titles
Right-click the headers of any attribute that is not in the following list, and elick
Remove. Hint: Once you get to initial_list_status, click that column header then seroll to the right until you reach the end of the columns and Shift + Click the
Jast column (settlement_term). Then right-click and remove columns.
a loan_amnt
be term
int rate
d. grade
& emp_length
£. home_ownership
g. annual_ine
hi issue_d
i, loan_status
m dti
n, deling_ 2
©, earliest_er_line
P. open_ace
4q, revol_bal
revol_util
8, total_ace
7. €4 Take a sereenshot (label it 23MA) of your reduced columns,
Next, remove text values from numerical values and replace values so we can do
calculations and summarize the data, These extraneous text values include
months, <1, n/a, +, and years:
page 88
8, Select the term column.
a. In the Transform tab, click Replace Values.
1. Inthe Value to Find box, type “months” with a space as the first character
(do not include the quotation marks).
2. Leave the Replace With box blank. 3. Click OK.
9. Select the emp_length column.
a In the Transform tab, click Replace Values.
1. In the Value to Find box, type “years” with a space as the first character.
2. Leave the Replace With box blank.
3. Click OK.
b, In the Transform tab, click Replace Values.
1. In the Value to Find box, type “year” with a space as the first cheracter.
2. Leave the Replace With box blank,
3. Click OK.
¢. Inthe Transform tab, click Replace Values,
1. In the Value to Find box, type “<1” with a space between the two characters.
2. In the Replace With box, type “0”.
3. Click OK.
d. In the Transform tab, click Replace Values.
1. In the Value to Find box, type “n/a”
2. In the Replace With box, type “0”.
3. Click OK.
«In the Transform tab, click Extract > Text Before Delimiter.
1. In the Value to Find box, type “
2. Click OK.
f. In the Transform tab, click Extract > Text Before Delimiter.
1. In the Value to Find box, type “
2. Click OK.
(a single space).
g, Inthe Transform tab, click Data Type > Whole Number, 10. 6 Take a screenshot (label it 2-3MB) of your cleaned data file, showing the
term and emp_length columns.
11. Click the Home tab in the ribbon and then click Close & Load. It will take a
minute to clean the entire data file,
12. When you are finished answering the lab questions, you may close Excel. Save
your file as Lab 2-3 Lending Club [Link].
Microsoft | Power BI Desktop
: i anaes Met ce
Microsoft Excel
LAB 2-4M Example of Data Distributions in Microsoft Power Query Microsoft | Power BI Desktop
Lab Note: These instructons can also be performed in the Power Query Included with Excel 365.
- Open a new workbook in Power BI Desktop.
Click the Home tab in th
Navigate to your Lab 2-4 Lending Club [Link] file and click Open.
|. Check LoanStats3e and click Transform Data or Edit.
. Click View in the ribbon, then check Column Distribution. A small frequency
distribution graph will appear at the top of each column,
6. Click the loan_amt column,
boa and choose Get Data > Excel.
pre
we
7. In the View tab, check Column Profile. You now see summary stats and a
frequency distribution for the selected column.
8. Note: To show profile for the entire data set instead of the top 1,000 values, go to
the bottom ofthe Power Query Editor window and click the title Column
profiling based on top 1000 rows and change it to Column profiling based on
entire data set.
9. 16 Take a servenshot (label it 2-4MA) of the column stati
distribution,
sand value
10. Click the int_rate column and the annual_ine column, noting the count, min,
‘max, average, and standard deviation of each.
11. Click the drop-down next to the addr_state column and unc!
12. Check PA and click OK to filter the loans.
13. In Power BI, click Home > Close & Apply. Note: You can always return to
Power Query by clicking the Transform button in the Home tab.
14, To show summary statistics in Power BI, go to the visualizations pane and click
Multi-row card.
(Select All.
‘4, Drag loan_amnt to the Fields box. Click the drop-down menu next to
Joan_amnt and choose Sum.
b, Drag loan_amnt to the Fields box below the existing field. Click the drop-
down menu next to the new loan_amnt and choose Average, a, Drag loan_amnt to the Fields box. Click the drop-down menu next to
Joan_amnt end choose Sum.
, Drag loan_amnt to the Fields box below the existing field. Click the drop-
down menu next to the new loan_amnt and choose Average.
. Drag loan_amnt to the Fields box below the existing field, Click the drop-
down menu next to the new loan_amnt and choose Count,
. Drag loan_amnt to the Fields box below the existing field. Click the drop-
down menu next to the new loan_ammt and choose Max.
15. Add two new Multi-row cards showing the same values (Sum, Average, Count,
‘Max) for int_rate and annual_ine,
page 94
16, 4 Take a sercenshot (label it 2-4MB) of the column siatistics and value
distribution,
17, When you are finished answering the lab questions, you may close Power BI
Desktop. Save your file as Lab 2-4 Lending Club [Link]. Microsoft | Excel + Power Query
LAB 2-5M_ Example of Cleaned College Scorecard Data in Microsoft Excel Microsoft | Excel + Power Query
Open a new blank spreadsheet in Excel.
. From the Data tab in the ribbon, click Get Data > From File > From TextCSV.
Navigate to your Lab 2-5 College Scoreeard [Link] file end click Open,
|. Verify that the data loaded correctly into tables and rows and then click
‘Transform Data or Edit.
Click through each of the 30 columas and from the Transform tab in the ribbon,
click Data Type > Whole Number or Data Type > Decimal Number where
appropriate. If prompted, click Replace Current. Because the original text file
replaced empty values with “NULL”, Power Query erroneously detected many of
the columns as Text, Hint: Hold the Ctrl key and click to select multiple columns.
page 97
6. © Take a screenshot (label it 2-SMA) of your columns with the proper date
types.
7. From the Home tab, click Close & Load.
8. To ensure that you captured all of the data through the extraction from the tx file,
wwe need to validate them:
a. Inthe Queries & Connections pane, verify that there are 7,703 rows loaded.
b. Compare the attribute names (columr: headers) to the attributes listed in the data
dictionary (found in Appendix K of the textbook). There should be 30 columns
{the last column in Excel should be AD),
€. Click Column H for the SAT_AVG attribute. In the summary statisties at the
bottom of vour worksheet. the overall averase SAT score should be 1,059.07. ere
6.
7)
8,
9.
10,
- Open a new blank spreadsheet in Excel
From the Data tab in the ribbon, click Get Data > From File > From TextiCs
Navigate to your Lab 2-5 College Scorecard [Link] file and click Open.
‘Verify that the data loaded correctly into tables and rows and then click
‘Transform Data or Edit,
|. Click through each of the 30 columns and from the Transform tab in the ribbon,
click Data Type > Whole Number or Data Type > Decimal Number where
appropriate. If prompted, click Replace Current, Because the original text file
replaced empty values with “NULL”, Power Query erroneously detected many of
the columns as Text. Hint: Hold the Ctr! key and click to select multiple columns.
page 97
. 6 Take a screenshot (label it 2-SMA) of your columns with the proper data
types.
. From the Home tab, click Close & Load.
. To ensure that you captured all of the data through the extraction from the txt file,
we need to validate them:
4 In the Queries & Connections pane, verify that there are 7,703 rows loaded.
b, Compare the attribute names (column headers) to the attributes listed in the data
dictionary (found in Appendix K of the textbook). There should be 30 columns
(the last column in Excel should be AD).
¢. Click Column H for the SAT_AVG attribute. In the summary statisties at the
boitom of your worksheet, the overall average SAT score should be 1,059.07.
. © Take a screenshot (label it 2-SMB) of your data table in Excel,
). When you are finished answering the lab questions, you may close Excel, Save
your file as Lab 2-5 College Scorecard [Link]. Your data are now
ready for the test plan. This lab will continue in Lab 3-3, Microsoft | Power BI Desktop
Micro Excel
LAB 2-6M Example of Dillard’s Data Model in Microsoft Power BI Microsoft | Power BI Desktop
|. Open Power BI Desktop,
2. In he Home ribbon, click Get Data > SQL Server.
3. Enter the following and click OK (Keepin mind that SQL Serveris not just one
database, its a col
andthe specific datebase):
tion of databases, oi ie eriticl to indicate the server path
4. Server: [Link]
b, Database: WCOB_ Dillards
¢, Data Connectivity: Dire Query
4. Ifprompted to enter credentials, you cen keep the default to