HAS ANYONE DONE THESE EXERCISES? Just answering the questions in bold.

Lab Overview

SCENARIO

VanArsdel is a company that manufactures and sells sporting goods. The company has offices in the United States (US) and several other countries. Its sales comprise of US sales and International sales. VanArsdels sales come from its owned manufactured products, as well as other manufacturers products.

VanArsdel's US office stores the sales data on an Access database. VanArsdel International sales transactions are available as comma separated (CSV) files. They could be generated daily, either manually by someone, or automatically by an automated process. They are available in a dedicated folder. These CSV files have the same column structure as the sales table for the US sales that comes from the SQL Database.

You want to perform analysis on VanArsdel's worldwide sales data for the year 2000 to 2015. You need to bring all these data into Power BI Desktop before you can perform any analysis. Finally, you want to compare VanArsdel's country sales with the country population. You need to import the country population data from a less structured Excel report to Power BI.

LAB OVERVIEW

This lab comprises of three exercises:

In the first exercise, you will import data to Power BI Desktop from an Access database file.

In the second exercise, you will import data from CSV files which resides in a file folder. You will append this new data to the corresponding existing data that comes from the Access Database.

In the third exercise, you will import data to Power BI Desktop from an Excel file that is less structured.

Before starting this lab, you should review the Power BI Desktop Data Transformations module in this course. Then, if you have not already done so, follow the instructions in the Set up the Lab Environment section of this course to set up the lab environment.

WHAT YOU'LL NEED

A computer with the latest version of Power BI Desktop installed on it.

A copy of the Access Database containing VanArsdel's US sales data.

4 CSV files, containing VanArsdels international sales data:

CA Sales.csv

FR Sales.csv

DE Sales.csv

MX Sales.csv

An Excel file containing country population data. NOTE: If you are having issues with the direct link, head over to the github repository and download from there. https://github.com/MicrosoftLearning/Analyzing-Visualizing-Data-PowerBI

CONNECTING TO AN ACCESS DATABASE FOR THE FIRST TIME

If you are connecting to an Access database for the first time, you might need to install the Access redistributable package.

If you have a 32 bit machine, you need to install the 32 bit Power BI Desktop and the 32 bit redistributable of Access.

If you have a 32 bit Office installed (regardless of your machine), you need to install the 32 bit Power BI Desktop and the 32 bit redistributable of Access.

Otherwise, you can install the 64 bit Power BI Desktop and the 64 bit redistributable of Access.

Follow the link provided by Power BI Desktop when trying to connect to the Access database.

Exercise 1: Import Data from Access Database

VanArsdel's US office stores the sales data on an Access database. You will need to perform analysis on that data, but before you can do so, you need to import the data to Power BI Desktop and perform some transformations.

IMPORTANT! Before you start, if your locale settings is not English (United States), you might want to change this, since the data you will import is based on this locale. This is covered in "Changing Locale".

Download the VanArsdel's Access database. Extract the file to "C:\DAT207x". You can use other folders, but if you are going to use the starter file provided at each lab instead of your own file, then it is recommended to use the absolute path given above in order to successfully perform the schedule refresh in Lab 4. NOTE: If you are having issues with the direct link, head over to the github repository and download from there. https://github.com/MicrosoftLearning/Analyzing-Visualizing-Data-PowerBI

Start with a blank Power BI Desktop file.

Connect to the Access database by using Get Data and select the Access database file.

Select the following tables to be imported: bi_date, bi_geo, bi_manufacturer, bi_product, and bi_salesFact.

Edit the query before loading to the data model.

Filter the rows on the bi_salesFact query to include dates from January 1st, 2000. (Hint: Apply a Date filter in the Date column to import dates after December 31st, 1999. Before you can apply a date filter, you need to change the Date column's Data Type to Date).

Filter the rows on the bi_date query to include dates from January 1st, 2000. (Hint: Apply a Date filter in theDate column to import dates after December 31st, 1999. Before you can apply a date filter, you need to change the Date column's Data Type to Date).

Rename the queries as follows:

bi_date: Date

bi_geo: Locations

bi_manufacturer: Manufacturers

bi_product: Products

bi_salesFact: Sales

Load the data into Power BI Desktop. This might take a few minutes.

Explore the imported data in the Data View.

Lab Questions

5 points possible (graded)

Answer the following question by reviewing the imported data in Power BI Desktop. (Hint: Use the Data view).

How many rows were imported from the Date query?

How many rows were imported from the Locations query?

How many rows were imported from the Manufacturer query?

How many rows were imported from the Products query?

How many rows were imported from the Sales table?

Exercise 2: Import Data from a Folder Containing CSV Files

Continue with your Power BI file from the previous exercise. You want to create a Query for the International sales and append the Query to the Query from US Sales.

Download the zip file containing VanArsdel's international sales data. Extract the file to "C:\DAT207x". You can use other folders, but if you are going to use the starter file provided at each lab instead of your own file, then it is recommended to use the absolute path given above in order to successfully perform the schedule refresh in Lab 4. You should see 4 CSV files in the "C:\DAT207x\International" folder.

Import the data from the file folder by using the Get Data. To do this, click the Get Data / More option, select Folder and click Connect. Select the folder where you saved the 4 CSV files containing VanArsdels international sales data.

Perform the following steps:

Name the query International Sales.

Select to combine (combined binaries) the content of those 4 files.

Filter the rows that come from the header of the CSV files. (Hint: One way to do this is to filter out the Country column from records containing Country).

Filter the rows that are after December 31st, 1999.

Edit the Sales Query from the US Sales.

Append the International Sales Query to the Sales query from the US Sales.

In the Sales query, add a custom column named Country Name which takes the value of the Countrycolumn when it is not null and the value of "USA" when the Country column is null.

Remove the Country column.

Load the data into Power BI Desktop. This might take a few minutes.

Explore the imported data in the Data View.

Hide the International Sales table from report view.

Lab Questions

4 points possible (graded)

Answer the following question by reviewing the imported data in Power BI Desktop. (Hint: Use the Data view).

How many rows were imported from the International Sales query?

How many rows were imported from the Sales query?

Exercise 3: Import a Less Structured Data from an Excel File

Continue with your Power BI file from the previous exercise. You want to import an Excel report containing population data for the countries that VanArsdel operates.

Download the zip file containing the Country Population data. (Data is a subset of the "World Data Bank's Population, total" dataset). Extract the file to "C:\DAT207x". You can use other folders, but if you are going to use the starter file provided at each lab instead of your own file, then it is recommended to use the absolute path given above in order to successfully perform the schedule refresh in Lab 4.

Import the data from the file folder by using the Get Data. To do this, click the Get Data / Excel option. Select the Excel file containing the Country Population data.

Edit the query and perform the following steps:

Name the Query Country Population.

Remove the first four rows of the table.

Promote the row that represents the title of the column to the table headers.

Transform the yearly columns to rows and name the resulting columns appropriately. (Hint: You might find the Unpivot Columns feature useful).

Rename the resulting columns to Year and Population respectively.

Filter out the year 1999.

Set the Data Type of both the Year and Population columns to Whole Number.

Load the data into Power BI Desktop.

Explore the imported data in the Data View.

Lab Questions

Answer the following question by reviewing the imported data in Power BI Desktop. (Hint: Use the Data view).

How many rows were imported from the Country Population query?

How many distinct values of the Year column were imported from the Country Population query?