Analyzing and Visualizing Data with Excel Flashcards

You may prefer our related Brainscape-certified flashcards:
1
Q

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

A

2010

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

Table relationship

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A
  • One-to-one
  • One-to-many
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

WHAT IS DAX?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

You can use DAX in calculated columns and measures

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What are measures in Excel?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Which statement describes an implicit measure?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which statement describes an explicit measure?

A

An explicit measure is a measure that you manually create.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What does the RELATED function (DAX) return?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A
  • To filter by year, month, or week.
  • To perform advanced calculations such as year-over-year comparison.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A
  • 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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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?

A

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

17
Q

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?

A

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

18
Q

What does DAX stand for?

A

DAX stands for Data Analysis Expressions.

19
Q

Consider the following DAX statement:

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

What does this statement return?

A

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

20
Q

What does the SAMEPERIODLASTYEAR function return?

A

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.

21
Q

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

A

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.

22
Q

When to Use the CUBE Functions?

A
  • 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.
23
Q

When NOT to Use the CUBE Functions?

A

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

24
Q

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

A
  • 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.

25
Q

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

A

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

26
Q

What are the components of a Power BI dashboard?

A

Power BI dashboard is made of tiles.

27
Q
A