$2)$ Connect to the MavenMarket$\_$Customers csv file

$$ Name the table "Customers", and make sure that headers have been promoted

$$ Confirm that data types are accurate $($Note: "customer$\_$id$"$ should be whole

numbers, and both "customer$\_$acct$\_$num" and "customer$\_$postal$\_$code" should

be text$)$

$$ Add a new column named "full$\_$name" to merge the the "first$\_$name" and

"last$\_$name" columns, separated by a space

$$ Create a new column named "birth$\_$year" to extract the year from the

"birthdate" column, and format as text

$$ Create a conditional column named "has$\_$children" which equals $"$N$"$ if

"total$\_$children" $=0,$ otherwise $"$Y$"$

$3)$ Connect to the MavenMarket$\_$Products csv file

$$ Name the table "Products" and make sure that headers have been promoted

$$ Confirm that data types are accurate $($Note: "product$\_$id$"$ should be whole

numbers, "product$\_$sku" should be text$),$ "product$\_$retail$\_$price" and

"product$\_$cost$"$ should be decimal numbers$)$

$$ Use the statistics tools to return the number of distinct product brands, followed

by distinct product names

o Spot check: You should see $111$ brands and $1,560$ product names

$$ Add a calculated column named "discount$\_$price", equal to $90\%$ of the original

retail price

o Format as a fixed decimal number, and then use the rounding tool to

round to $2$ digits

$$ Select "product$\_$brand" and use the Group By option to calculate the average

retail price by brand, and name the new column "Avg Retail Price"

o Spot check: You should see an average retail price of $$2\mathrm{.}18$ for

Washington products, and $$2\mathrm{.}21$ for Green Ribbon

$$ Delete the last applied step to return the table to its pre$-$grouped state

$$ Replace "null" values with zeros in both the "recyclable" and "low$-$fat"

columns

$4)$ Connect to the MavenMarket$\_$Stores csv file

$$ Name the table "Stores" and make sure that headers have been promoted

$$ Confirm that data types are accurate $($Note: "store$\_$id$"$ and "region$\_$id$"$ should

be whole numbers$)$

$$ Add a calculated column named "full$\_$address", by merging "store$\_$city",

"store$\_$state", and "store$\_$country", separated by a comma and space $($hint: use

a custom separator$)$

$$ Add a calculated column named "area$\_$code", by extracting the characters

before the dash $("-")$ in the "store$\_$phone" field

$5)$ Connect to the MavenMarket$\_$Regions csv file

$$ Name the table "Regions" and make sure that headers have been promoted

$$ Confirm that data types are accurate $($Note: "region$\_$id$"$ should be whole

numbers$)$

$6)$ Connect to the MavenMarket$\_$Calendar csv file

$$ Name the table "Calendar" and make sure that headers have been promoted

$$ Use the date tools in the query editor to add the following columns:

o Start of Week $($starting Sunday

o Name of Day

o Start of Month

o Name of Month

o Quarter of Year

o Year

$7)$ Connect to the MavenMarket$\_$Returns csv file

$$ Name the table "Return$\_$Data" and make sure that headers have been

promoted

$$ Confirm that data types are accurate $($all ID columns and quantity should be

whole numbers$)$

$8)$ Add a new folder on your desktop $($or in your documents$)$ named "MavenMarket

Transactions", containing both

the MavenMarket$\_$Transactions$\_1997$ and MavenMarket$\_$Transactions$\_1998$ csv

files

$$ Connect to the folder path, and choose "Trnasform" $($vs$.$ Combine and

Transform$)$

$$ Click the "Content" column header $($double arrow icon$)$ to combine the files,

then remove the "Source.Name" column

$$ Name the table "Transaction$\_$Data", and confirm that headers have been

promoted

$$ Confirm that data types are accurate $($all ID columns and quantity should be

whole numbers$)$

o Spot check: You should see data from $1/1/1997$ through $12/30/1998$ in

the "transaction$\_$date" column

$9)$ With the exception of the two data tables, disable "Include in Report Refresh",

then Close & Apply

$$ Confirm that all $7$ tables are now accessible within

both the RELATIONSHIPS view and the DATA view

$10)$ Save your $.$pbix file $($i$.$e$.$ "MavenMarket$\_$Report"$)$

PART $2$

Using the report you created in Part $1,$ complete the following steps:

$1)$ In the RELATIONSHIPS view, arrange your tables with the lookup tables above the data

tables

$$ Connect Transaction$\_$Data to Customers, Products, and Stores using

valid primary$/$foreign keys

$$ Connect Transaction$\_$Data to Calendar using both date fields, with an

inactive "stock$\_$date" relationship

$$ Connect Return$\_$Data to Products, Calendar, and Stores using valid primary$/$foreign

keys

$$ Connect Stores to Regions as a "snowflake" schema

$2)$ Confirm the following:

$$ All relationships follow one$-$to$-$many cardinality, with primary keys $(1)$ on the lookup

side and foreign keys $(*)$ on the data side

$$ Filters are all one$-$way $($no two$-$way filters$)$

$$ Filter context flows "downstream" from lookup tables to data tables

$$ Data tables are connected via shared lookup tables $($not directly to each other$)$

$3)$ Hide all foreign keys in both data tables from Report View, as well as "region$\_$id$"$ from

the Stores table

$4)$ In the DATA view, complete the following:

$$ Update all date