Analyzing and Visualizing Data with Excel

Question 1

Which version of Excel was the Excel Data Model FIRST introduced??

Answer 1

2010

Question 2

In the Diagram view of the Excel data model, what does a line between two tables represent?

Answer 2

Table relationship

Question 3

When you create a calculated column on a table in the Excel data model, which two sources of data can the calculated column be based on?

Answer 3

You can create a calculated column in the Excel data model based on data in the current table or on data in another table that is related to the current table.

Question 4

Which two type of relationships are supported in the Excel data model?

Answer 4

• One-to-one
• One-to-many

Question 5

WHAT IS DAX?

Answer 5

Data Analysis Expressions (DAX) is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.

Question 6

In which two locations can you use Data Analysis Expression (DAX)?

Answer 6

You can use DAX in calculated columns and measures

Question 7

What are measures in Excel?

Answer 7

Measures in Excel is a calculation created for the purpose of measuring an outcome or result relative to other factors.

Question 8

Which statement describes an implicit measure?

Answer 8

An implicit measure is one that Excel generates for you when you add fields to the Values area of a pivot table.

Question 9

Which statement describes an explicit measure?

Answer 9

An explicit measure is a measure that you manually create.

Question 10

What does the RELATED function (DAX) return?

Answer 10

The RELATED function returns a single value that is related to the current row from another table.

Question 11

What are two ways of loading data into Excel by using queries in Excel 2016?

Answer 11

You can load data to Excel table and/or to Excel data model.

Question 12

Where are data pre-processing steps recorded in the Query Editor?

Answer 12

The pre-processing steps are recorded in the list of Applied Steps section in the Query Editor.

Question 13

Using the Replace Values step, how many distinct values can be replaced at a time?

Answer 13

You can replace one distinct value at a time using the Replace Values step.

Question 14

Which are two purposes of using a calendar table in a data model?

Answer 14

• To filter by year, month, or week.
• To perform advanced calculations such as year-over-year comparison.

Question 15

Which are two ways to create a calendar table in Excel 2016 that will be used with the data model?

Answer 15

• Create a calendar table in the worksheet and add it to the Excel data model.
• Use the New Date Table function from the Design tab of the Power Pivot for Excel window.

Question 16

You are importing data from four CSV files that are located in the same folder. All four of the CSV files have the same file structure. You are using the “From folder” option of the query.

What is the initial step you need to do in the Query Editor before loading the data into the Excel data model?

Answer 16

If all four CSV files have headers, the first step you typically do is to remove the headers from the other three CSV files.

Question 17

You have four CSV files that are located in the same folder. All four of the CSV files have the same file structure.

What is the minimum number of queries you need to create to import three of the CSV files?

Answer 17

You can import multiple CSV files that are located in the same folder and have the same file structure in a single query.

Question 18

What does DAX stand for?

Answer 18

DAX stands for Data Analysis Expressions.

Question 19

Consider the following DAX statement:

CALCULATE([TotalRevenue],Manufacturer[Manufacturer]=”VanArsdel”)

What does this statement return?

Answer 19

The statement returns the value of TotalRevenue for the VanArsdel manufacturer.

Question 20

What does the SAMEPERIODLASTYEAR function return?

Answer 20

The SAMEPERIODLASTYEAR function returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.

Question 21

The SAMEPERIODLASTYEAR function takes in one parameter. Which argument can be used in that parameter?

Answer 21

The SAMEPERIODLASTYEAR function takes in one argument, which can be any of the following:

A reference to a date/time column, a table expression that returns a single column of date/time values, or a Boolean expression that defines a single-column table of date/time values.

Question 22

When to Use the CUBE Functions?

Answer 22

• When dashboard-style layouts are required (flexibility to move around cells).
• To reproduce reports that must look and behave similarly to legacy reports.
• To overcome limitations of pivot tables and pivot charts.
• To integrate data from multiple data models into a single report.

Question 23

When NOT to Use the CUBE Functions?

Answer 23

When pivot tables and pivot charts could deliver the same result.

Question 24

What are two reasons for uploading Excel reports to Power BI?

Answer 24

• To gain access to a more sophisticated visualization element.
• To easily share the report between users.

Explanation:

Power BI gives you access to more visualization element than the ones in Excel. Tree-map visualization is one example. In addition, by uploading your reports to Power BI, you can easily share the dashboard you created with other users.

Question 25

What are the two ways of using Excel with Power BI?

Answer 25

You can either import your Excel data to Power BI or connect, manage and view the Excel workbook in Power BI.

Question 26

What are the components of a Power BI dashboard?

Answer 26

Power BI dashboard is made of tiles.